Populate a Sheet from SQL server query (C#)

Erik Beltran

Hi Everyone,

 

I need some help with a fairly simple task we are trying to accomplish -but i am having some hard time getting it to work - We are reading from a SQL server and some of the result needs to be populated into Smartsheet.

As i am using Dataset - and trying to update each value on the variable "ProjectName" into a column named "Project Name" while looping each record in the Dataset. As a result i am populating the entire column instead of each cell on the column.

 

Has anyone done something similar that can give me a hand shedding some light?

Thanks in advance

 

Desired:

 

 

Actual Reult

 

 

 

// create a connection object

            SqlConnection conn = new SqlConnection("Server=127.0.0.1;Database=Smartsheet;Trusted_Connection=True");

            conn.Open();

            // create a command object

            SqlDataAdapter da = new SqlDataAdapter("select * from RYGTest", conn);

            DataSet ds = new DataSet();

            da.Fill(ds);

            foreach (DataTable table in ds.Tables)

            {

                foreach (DataRow dr in table.Rows)

                {

                    var ProjectID = dr["ProjectID"].ToString();

                    var ProjectName = dr["ProjectName"].ToString();

                    var Status = dr["Status"].ToString();

                    if (dr != null)

                    {

                        foreach (Row row in sheet.Rows)

                        {

                            List<Row> rowsToUpdate = new List<Row>();

                            Row rowToUpdate = evaluateRowAndBuildUpdates(row);

                            if (rowToUpdate != null)

                                rowsToUpdate.Add(rowToUpdate);

                            ss.SheetResources.RowResources.UpdateRows(sheet.Id.Value, rowsToUpdate);

                        }

                        Row evaluateRowAndBuildUpdates(Row sourceRow)

                        {

                            Row rowToUpdate = null;

                            var cellToUpdate = new Cell

                            {

                                ColumnId = columnMap["Project Name"],

                                Value = ProjectName

                            };

                            var cellsToUpdate = new List<Cell>();

                            cellsToUpdate.Add(cellToUpdate);

                            rowToUpdate = new Row

                            {

                                Id = sourceRow.Id,

                                Cells = cellsToUpdate

                            };

                            return rowToUpdate;

                        }

                    }

                }

          

 

Shaine Greenwood

Hi Erik—

I'm not positive on this as I'm not as familiar with C#, but this may be an issue with the scope of where this chunk is:

var ProjectID = dr["ProjectID"].ToString();

var ProjectName = dr["ProjectName"].ToString();

var Status = dr["Status"].ToString();

It looks like these are being set at the table level of the foreach, not at the row/cell iteration level.

Smartsheet may be automatically incrementing the numeric values of your ProjectID (just as you would find if you drag-filled numbers in the application).

You might try moving the variable creation to be specifically within the scope of your foreach loop below, so the variables are set in each stage of that loop instead of the one above.

If that doesn't do the trick, contact our Support team directly to see if they can help troubleshoot this further.

Erik Beltran

Shaine,



Thank you for your input - i was able to get this to work by using Row.UpdateRowBuilder and update at row/cell level as you suggested.



Also another problem i had was related to parse the data types from SQL to C# but this was a great reference (https://stackoverflow.com/questions/425389/c-sharp-equivalent-of-sql-server-datatypes) 

I found additional challenges, including how to handle Null records from database.

If anyone is interested to check the code or if any questions, i will be glad to help.

The code is not that long and is fairly simple.

 

Thanks, ^EB