question

Protecteric avatar image
1 Vote"
Protecteric asked ErlandSommarskog commented

Show datatable c# in SQL server as a result

I have a datatable with data in my C# script and I copy this data to a table in SQL server, now I want to pass this to a result window in sql server.

Example.

I have a assembly in SQL server and when I call this assembly with a stored procedure I have a parameter that the user can choose for copy the C# datatable from my DLL to a table in SQL server, like this:


         //Insert the datatable with a SqlBulkCopy
         using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cnn))
         {
             bulkCopy.DestinationTableName = temporalTable+TableNameToWrite;
             try
             {
                 bulkCopy.WriteToServer(tempTable, DataRowState.Added);
                    
             }
             catch (Exception)
             {
                 Mail mail = new Mail();
                 mail.SendMail();
             }
         }

But now I want to show the results of the table in the result pane of sql server when I run this stored procedure. I thought at TVP(Table-Valued Parameters), but I cant alter them after I make them so this is not a option for me.

Does anybody have some tips for me to show a datatable in sql server without copying the data to the server?

Best regards,
Eric.


sql-server-generaldotnet-csharpsql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I think there is some confusion here, Eric. SQL Server does not have a result pane, it is a headless service. If you wan to return data back to the client that executed the above assembly then you'll need to add that into the C# assembly.

I also want to take this time to call out that putting potentially large swaths of cpu and logic like this in an assembly loaded into SQL Server is a bad idea for various reasons.

0 Votes 0 ·
Protecteric avatar image Protecteric SeanGallardy-MSFT ·

I dont think there is a confusion, but that does'nt matter.

If you run a select * from whatevertable then you get a result right?

I want to run a stored procedure that execute my DLL, but I want to see the result from the DLL like the whatevertable example above in management studio.

We can discuss a lot about assemblies, but another time please.

Do you have a solution or tip to get me on my way?

0 Votes 0 ·

Much as I stated (about the confusion), you are not giving back output - at all - or obtaining any data from SQL Server. You'll need to add in the data to return or otherwise change your code much as Erland has stated and I alluded. I wish you luck.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered Protecteric commented

Does anybody have some tips for me to show a datatable in sql server without copying the data to the server?

You want to return the data from SQL Server, without first getting the data into SQL Server?

Then again, if this is an assembly in SQL Server, I guess the data is in SQL Server, at least in memory.

You could make a table-valued function out of your assembly and return the data from the DataTable.

If this in a CLR Stored Procedure, you could use the method Microsoft.SqlServer.Server.SqlPipe.Send. It does not accept a DataTable, but it accepts an SqlDataReader. There are some more methods in this class you can investigate.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Erland, this works fine for me!

0 Votes 0 ·
Protecteric avatar image
0 Votes"
Protecteric answered ErlandSommarskog commented
 void SendDataTableOverPipe(DataTable dataTable)
                 {
                      string columnTest = root.results[0].series[0].columns[0];
                     // Build our record schema
                     List<SqlMetaData> outputColumns = new List<SqlMetaData>(tempTable.Columns.Count);
                     foreach (DataColumn col in tempTable.Columns)
                     {
                         //SqlDbType tpe = (SqlDbType)Enum.Parse(typeof(SqlDbType), col.ToString(), true);
                         SqlMetaData OutputColumn = new SqlMetaData(col.ColumnName, System.Data.SqlDbType.NVarChar, col.MaxLength);
    
                         outputColumns.Add(OutputColumn);
                     }
    
    
                     // Build our SqlDataRecord and start the results
                     SqlDataRecord record = new SqlDataRecord(outputColumns.ToArray());
                     SqlContext.Pipe.SendResultsStart(record);
    
                     // Now send all the rows
                     foreach (DataRow row in tempTable.Rows)
                     {
                         for (int col = 0; col < tempTable.Columns.Count; col++)
                         {
                             record.SetValue(col, row.ItemArray[col]);
                         }
                         SqlContext.Pipe.SendResultsRow(record);
                     }
    
                     // And complete the results
                     SqlContext.Pipe.SendResultsEnd();
                 }
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Great to see that I was able to point you in the right direction!

1 Vote 1 ·