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.