Using C#, how can I insert data from a SQL Server to Google Sheets?

Question

Using some code I found online, I am able to insert static data into a Google Sheet. Ultimately, I'd like to import data from a SQL stored procedure into a Google Sheet on-demand.

The static code below works just fine:

        {
             // Specifying Column Range for reading...
            var range = $"{sheet}!A:E";
            var valueRange = new ValueRange();

            // Data for another Student...
            var oblist = new List<object>() { "John", "Doe", "john@test.com", "5553332121" };
            valueRange.Values = new List<IList<object>> { oblist };

            // Append the above record...
            var appendRequest = service.Spreadsheets.Values.Append(valueRange, SpreadsheetId, range);
            appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
            var appendReponse = appendRequest.Execute();
        }

I have the following code I use to create a data table with my stored procedure result set:
(Note that the # of columns is dynamic as this is a pivot)

            string lConnString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ToString();
            string procedure = "usp_get_menupivot";

            DataTable table = new DataTable();

            using (SqlConnection conn = new SqlConnection(lConnString))
            {
                conn.Open();
                using (SqlCommand command = new SqlCommand(procedure, conn))
                {
                    using (var da = new SqlDataAdapter(command))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        da.Fill(table);
                    }
                }
            }

            string data = string.Empty;
            StringBuilder sb = new StringBuilder();

            if (null != table && null != table.Rows)
            {
                foreach (DataRow dataRow in table.Rows)
                {
                    foreach (var item in dataRow.ItemArray)
                    {
                        sb.Append(item);
                        sb.Append(',');
                    }
                    sb.AppendLine();
                }

                data = sb.ToString();
            }

How can I combine these two scripts to allow me to insert the SQL data to the Google Sheet?

Return the SQL Server data to the valueRange, `valueRange.Values = StoredProcResultSet();` — Jeremy Thompson, Dec 14 '22 at 23:17
@JeremyThompson The number of columns is dynamic, so the range needs to be addressed. How do you recommend that be done? — warrenk, Dec 15 '22 at 19:13
` $"{sheet}!A:E"` can easily be calculated by using Number To Column folrmula https://stackoverflow.com/a/182924/495455 — Jeremy Thompson, Dec 16 '22 at 00:12

score 0 · Answer 1 · answered Dec 16 '22 at 22:50

Here is the full solution including the creation and load of the DataTable to the conversion to an IList with column headers.

Something to note is that the "range" values don't have any impact.

    static void AddRows()
    {

        // Get data from REPORT SQL Server
        string lConnString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ToString();
        string procedure = "usp_get_menupivot";

        DataTable table = new DataTable();

        using (SqlConnection conn = new SqlConnection(lConnString))
        {
            conn.Open();
            using (SqlCommand command = new SqlCommand(procedure, conn))
            {
                using (var da = new SqlDataAdapter(command))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    da.Fill(table);

                }
            }
        }

        // Create a new List object to hold the rows
        List<IList<object>> list = new List<IList<object>>();
        IList<object> columnNames = new List<object>();

        foreach (DataColumn column in table.Columns)
        {
            columnNames.Add(column.ColumnName);
        }

        list.Add(columnNames);

        // Iterate through the rows in the DataTable
        foreach (DataRow row in table.Rows)
        {
            // Add the row to the List as an IList<object>
            list.Add(row.ItemArray);
        }

        // Specifying Column Range for reading...
        var range = $"{Sheet}!A:Z";
        var valueRange = new ValueRange();

        // Data for menu item(s)    
        valueRange.Values = list;

        // Append the above record...
        var appendRequest = service.Spreadsheets.Values.Append(valueRange, SpreadsheetId, range);
        appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
        var appendReponse = appendRequest.Execute();
    }

Using C#, how can I insert data from a SQL Server to Google Sheets?

1 Answers1