C# Insert into SQL Table with List as parameter

Question

With sincere help from experts in this wonderful forum, I have been able to parsed my xml returned by a SharePoint list to get the desired list items into C# Lists.

XDocument xdoc = XDocument.Parse(activeItemData.InnerXml);
XNamespace z = "#RowsetSchema";

List<int> listID = (from row in xdoc.Descendants(z + "row") 
select (int)row.Attribute("ows_ID")).ToList();

List<string> listTitle = (from row in xdoc.Descendants(z + "row") 
select (string)row.Attribute("ows_LinkTitle")).ToList();

I have created a SQL table and I want to insert values in my table using Lists listID and listTitle as parameters

System.Data.SqlClient.SqlConnection sqlConnection1 =
            new System.Data.SqlClient.SqlConnection(MyconnectionString);

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "INSERT TechOpsProjectTracker (ID, [Project Name]) VALUES (@ID, @ProjectName)";

//What I want to do:
            //1. @ID should get the values from the List listID: List<int> listID = (from row in xdoc.Descendants(z + "row") select (int)row.Attribute("ows_ID")).ToList();
            //2. @ProjectName should get the values from the List listTitle: List<string> listTitle = (from row in xdoc.Descendants(z + "row") select (string)row.Attribute("ows_LinkTitle")).ToList();
            //3. so each new record inserted in the table has ID and its corresponding Project Name. Something like this
            /* ID    Project Name
               360   GEI Survey data to Sharepoint
               378   Create back end and environment to support User demographic reporting


             */

There might be some other possibly easier ways to accomplish my job. Please let me know. TIA.

score 2 · Answer 1 · edited May 23 '17 at 10:09

2

When it comes to inserting a list as a parameter, you might try table-valued parameters. It's similar to doing a bulk insert or using a temp table to do a SELECT INTO.

http://msdn.microsoft.com/en-us/library/bb675163.aspx

How to pass table value parameters to stored procedure from .net code

edited May 23 '17 at 10:09

Community

1
1

answered May 25 '12 at 16:23

Jacob

21
3

score 2 · Accepted Answer · answered May 25 '12 at 17:14

Setup a user defined type similar to.

CREATE TYPE [dbo].[tableOf_Ints] AS TABLE(
    [ID] [int] NULL
)
GO

Then you can can use it like this.

public static SqlCommand CreateCommand(List<int> ints)
{
    var dt = new DataTable();
    dt.Columns.Add("ID",typeof(Int32));
    for (int i = 0; i < ints.Count; i++)
    {
        dt.Rows.Add(ints[i]);
    }

    SqlCommand cmd = new SqlCommand("SomeStoredProc");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandTimeout = 120;
    var param1 = cmd.Parameters.AddWithValue("@SomeParam", dt);
    param1.SqlDbType = SqlDbType.Structured;
    param1.TypeName = "dbo.tableOf_Ints";

    return cmd;
}

Assuming you have a stored proc like this.

CREATE PROCEDURE [dbo].[SomeStoredProc]  
    @SomeParam TableOf_Ints READONLY
AS
BEGIN
END

Thanks Austin. I have some Qs before I try this out. What are we trying to accomplish with Stored proc? Where is the ISNERT command to insert my listID to my table? Is my stored proc suppose to have it? TIA. — Nemo, May 25 '12 at 18:10

score 0 · Answer 3 · answered May 25 '12 at 16:24

0

If you are using Sql Server 2008, you can pass in tables to your stored procedure and do a set based update/insert. This removes a lot of the complexity you are adding.

http://msdn.microsoft.com/en-us/library/bb510489.aspx and the Ado.Net Link

answered May 25 '12 at 16:24

Seth Flowers

8,990
2
29
42

Thanks. The site definitely helped me. :) – Nemo May 25 '12 at 19:24

sihirbazzz · Answer 4 · 2012-05-25T23:56:13.787

cmd.CommandText = "INSERT TechOpsProjectTracker (ID, [Project Name]) VALUES (@ID, @ProjectName)";    
Dictionary<int, string> dt = new Dictionary();
    for (int i=0; i<ListId.Count; i++)
    {
    dt.Add(ListID[i], ListTitle[i]);
    }

    foreach (KeyValuePair<int, string> pair in dt)
    {
       SqlParameter a = cmd.Parameters.Add("@ID", SqlDbType.Int);

       // or write what is the type of your column in your table after SqlDbType. 

       a.Value = pair.key;

       SqlParameter b = cmd.Parameters.Add("@Project", SqlDbType.Varchar, 50);

        // 50 is what you gave max length to your db column//

        b.Value = pair.value;

        int v = cmd.ExecuteNonQuery();

        if ( v == 1 )
        {
           MessageBox.Show("Successfully Done !");
        }

        else
        {
           MessageBox.Show("Oops ! I can' t insert Successfully")
        }
    }

Or directly you can pass ListID[0] to your query text by AddinParameter func or AddwithValue function

cmd.CommandText = "INSERT TechOpsProjectTracker (ID, [Project Name]) VALUES (@ID, @ProjectName)";

 for (int i=0; i<ListId.Count; i++)
    {
       cmd.Parameters.AddWithValue("@ID", ListID[i]);
       cmd.Parameters.AddWithValue("@Project", ListTitle[i]);
       int result = cmd.ExecuteNonQuery();

       // if you want you can add here the messagebox as the same in first code example
    }

hi Sihirbazzz, could you please point to how to pass the *pair.key* element to your @ID with AddinParameter function or AddwithValue. And how can I directly pass listID[0] tp my query text by using AddinParameter func or AddwithValue function? TIA. — Nemo, May 25 '12 at 18:41
@Nemo As a rememberance to you..for a better performance and security.. # Use stored Procedures # Control the values for unwanted inserts where you take (i.e. textboxes in your application) # Use datareader when you retrieve data from your db..And if you can, use datareader to retrieve data into Generic List (List i.e. List ) with this you will gain as a performance of upto % 40-50 — sihirbazzz, May 26 '12 at 00:18

C# Insert into SQL Table with List as parameter

4 Answers4

Linked