3

I have a C# SQLCLR Stored Procedure in which first data is filled in some dictionaries and is computed and then the output is stored in some other dictionaries.

This computation is done very fast because of the usage of Dictionaries and completely justifies my need of using CLR Stored Proc instead of a normal SQL Stored Proc.

However, I have to save the data in certain tables in SQL from these output Dictionaries, and this part takes so much time and fails my need of the whole SQLCLR proc to be faster.

I have to iterate on each key of every output dictionary and then have to create Insert Query and then have to run ExecuteNonQuery in the following way: enter image description here

So how can I improve my this approach so that it does not take time in inserting the data. I can't use SqlBulkCopy as it does not accept the in-process Context Connection (i.e. "Context Connection = true;") as the connectionString. So is there in other faster approach available? Thanks in advance.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
Hemant Sisodia
  • 488
  • 6
  • 23
  • 1
    "I can't use SQlBulkCopy as it does not accept connectionString as current context." - huh? what do you mean by that? Are you aware of this [constructor](https://msdn.microsoft.com/library/8x2hdfta.aspx)? I absolutely don't get what you mean by "connectionString as context". – Corak Oct 13 '17 at 12:19
  • @Corak I meant that I am setting connection of my CRL proc as "conn = new SqlConnection("context connection=true");". Now, I cant use this statement "using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn.ConnectionString))". – Hemant Sisodia Oct 13 '17 at 13:06
  • How about using this [constructor](https://msdn.microsoft.com/library/txy68e2d.aspx) then? `using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn)) { /*...*/}` – Corak Oct 13 '17 at 13:09
  • @Corak No, I am still getting this error by using your second solution. Error reading from The requested operation is not available on the context connection.. Stack Trace at System.Data.SqlClient.SqlBulkCopy.CreateOrValidateConnection(String method) – Hemant Sisodia Oct 13 '17 at 13:18
  • 1
    @Corak It is a physical limitation of `SqlBulkCopy` that the destination connection string cannot use the in-process connection (i.e. the Context Connection). I have updated the text of the question to reflect the correct terminology to hopefully reduce confusion (and in a moment will add the link to the supporting documentation). – Solomon Rutzky Oct 13 '17 at 13:28

2 Answers2

5

You have a few options:

  1. Create a User-Defined Table Type (UDTT) and a T-SQL stored procedure that accepts that UDTT as a parameter (i.e. a Table-Valued Parameter (TVP)). Since you already have the data in a collection, absolutely do NOT create a separate DataTable to transfer that data into as that is just a waste of time, memory, and CPU. You can stream the data in its native collection directly into the TVP by creating a method that returns IEnumerable<SqlDataRecord>, uses yield return;, and is used as the value for the SqlParameter that represents the UDTT. I have two examples of this here on SO:

  2. If there is only 1 table to populate (hence 1 result set), then you can output the collection as a result set from the stored procedure and use it as follows:

    INSERT INTO schema.table (column_list)
  EXEC schema.SQLCLR_stored_procedure @param1, ....;

  3. If there is only 1 table to populate (hence 1 result set), and if you aren't doing anything over the Context Connection that violates the rules of scalar functions, then you can try changing the SQLCLR stored procedure into a TVF, return IEnumerable, and iterate over the return via yield return; to stream it out. I wrote an article years ago with an example of this on SQL Server Central: CLR Table-Valued Function Example with Full Streaming (STVF / TVF)

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
3

Use SqlBulkCopy Class or you can create your own User-Defined Table Type in sql, in c# - generate your data into a DataTable, instead of some collection, which will match your user-defined type, then pass your DataTable as SqlParameter and execute. Or you can add your Collection directly into table value parameter, as shown Here (the useDataTable == false part)

Of course, for this you should use sql stored procedure. The calling part for sql user defined table type will look something like this:

cmd = new SqlCommand{ Connection = conn, ...
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_YourStoredProcedureName";
cmd.Parameters.Add(new SqlParameter("@UserDefinedTypeParameterName", yourDataTable));
cmd.ExecuteNonQuery();
SᴇM
  • 7,024
  • 3
  • 24
  • 41
  • This table type solution looks promising. I will check that and will let you know. Thanks mate! – Hemant Sisodia Oct 13 '17 at 13:07
  • ok, you're welcome, fell free to ask if something will go wrong. – SᴇM Oct 13 '17 at 13:07
  • @SeM-ՍեՄ -1 due to first option being exactly the thing the O.P. said that they couldn't use, and the second option being in the right direction (i.e. using a TVP) but implementing it in a very inefficient manner that is needlessly wasting time, memory, and CPU. The collection can be streamed as-is into the TVP without needing to duplicate it as a`DataSet`. Please see the links in my answer for example code on how to accomplish this. – Solomon Rutzky Oct 13 '17 at 13:25
  • @SolomonRutzky I saw your answer and I'm not telling him to write already initialized collection into `DataTable`, I said instead of write it into some collection, use `DataTable`. (_"create a DataTable with your data"_ not _"from your data"_). – SᴇM Oct 13 '17 at 13:27
  • Of course, if he already have a collection, he can stream data into TVP without doing one more unnecessary step. – SᴇM Oct 13 '17 at 13:30
  • @HemantSisodia and SeM: The O.P. stated that they already have data in dictionaries (i.e. collections), and even posted the code showing that. So even if you meant "generate the data, not already in a collection, into a DataTable" then I don't think anyone reading this answer would interpret in that way. That is why I took "_create a DataTable with your data_" to mean copy your collection into a DataTable, which unfortunately, is a very common thing for people to do, at least based on most examples I have come across. If you can re-word it to be clearer then I will remove the -1. – Solomon Rutzky Oct 13 '17 at 13:35
  • @HemantSisodia and SeM: I have removed the downvote (thanks for making that edit), but just to confirm my concern and what I said about most of the example code found here (and likely elsewhere), the bottom two, non-deleted, answers on the page that you just linked to in the update to this answer (the answers from Shahzad Qureshi and Martea) do exactly what I was talking about: needlessly copy an existing collection into a `DataTable`. Yeesh! – Solomon Rutzky Oct 13 '17 at 13:50