Insert Large dictionary in MS-SQL Server using Stored Procedure

Question

I followed this article to insert values into SQL server. But I have a large dictionary (with key value pair 'Column Name and Column Values') around 100 items, I want to insert into SQL server.

Is there any way I can directly insert into DB with this Dictionary?

It is unclear to me whether you need to do this from your application or if you just need to get this data into SQL and could do it from a command prompt/management studio. Also, you have tagged this as both mysql and sql-server, but listed MS-SQL (which corresponds to the article) in the title. Please clarify which database you are using. — pstrjds, Dec 24 '16 at 08:31
Hi @pstrjds I have a wpf application. I want to do it using C# code. I'm using Microsoft SQL Server database. — Kishor Bikram Oli, Dec 24 '16 at 15:43
Hi @DenishParvadia, Its not data import. I am searching for C# code. — Kishor Bikram Oli, Dec 24 '16 at 15:44

score 1 · Answer 1 · edited May 23 '17 at 12:17

1

Here you have a similar question with an answer showing an example on how to pass a dictionary to a stored procedure. But being the dictionary 100 items size, doesn't seem to me "large". Maybe you would be ok calling 100 times a simpler stored procedure that just inserts 1 item at a time.

Or not having any stored procedure at all and inserting directly from C# code either using SqlCommands with "INSERT" statements as command or using SqlBulkCopy.

edited May 23 '17 at 12:17

Community

1
1

answered Dec 24 '16 at 08:43

Jorge Y.

1,123
1
9
16

Hi @Jorge Y. Thank you for your answer. From the similar question you mentioned, is it necessary to convert Dictionary to DataTable? Can't we use just Dictionary without converting to DataTable? – Kishor Bikram Oli Dec 24 '16 at 15:46
Hi @Jorge Y. I cannot use simple SqlCommands with Insert statements. have to use Stored Procedure insert. – Kishor Bikram Oli Dec 24 '16 at 15:49
1

I'm afraid that you have to convert the dictionary to something else to pass it to stored procedure [another example using datatable this time for arrays](http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure). In that link there are other ways, like using XML, but to me the DataTable object looks like the simpler approach. And again, if it is not a requirement to insert all the dictionary in one procedure call, I would do 1 call per dictionary element. – Jorge Y. Dec 25 '16 at 02:39

score 0 · Accepted Answer · answered Dec 25 '16 at 05:35

This one is the easy way to do it. myDictionary has the key value pair.

SqlConnection sqlConn = new SqlConnection("[Your Connection String]");
SqlCommand sqlcomm = new SqlCommand("[Your SP name]", sqlConn);
sqlcomm.CommandType = CommandType.StoredProcedure;
foreach (KeyValuePair<string, object> itm in myDictionary)
{
    sqlcomm.Parameters.AddWithValue(itm.Key, itm.Value);
}
sqlcomm.CommandText = spName;

sqlConn.Open();
sqlcomm.ExecuteNonQuery();

Insert Large dictionary in MS-SQL Server using Stored Procedure

2 Answers2