2

I'm looking to do something simulair toward here: How do I insert multiple rows WITHOUT repeating the "INSERT INTO dbo.Blah" part of the statement? except that in addition towards doing this in one query (faster then several dozen) I also want to do this parameterized as the input comes from the web.

Currently I have

foreach(string data in Scraper){
            SqlConnection conn = new SqlConnection(WebConfigurationManager.AppSettings["ConnectionInfo"].ToString());
            string query = "INSERT INTO DATABASE('web',@data)";
            SqlCommand sqlCommand= new SqlCommand(query, conn);
            sqlCommand.Parameters.AddWithValue("@data", data);
            Command.executeNonQuery(); 
            conn.close();
}

Which is a bit slugish (note the real example has a lot more colums but that would make things more confusing).

Thijser
  • 2,625
  • 1
  • 36
  • 71
  • I have a crazy solution but you're not gonna like it... Let's say it pays off if you really have a massive amount of items to be inserted at once... – Adrian Salazar Aug 12 '15 at 12:39

2 Answers2

7

Since you are using c# and sql server 2008, you can use a table valued parameter to insert multiple rows to your database. Here is a short description on how to do this:

First, you need to create a user defined table type:

CREATE TYPE MyTableType AS TABLE
(
    Col1 int,
    Col2 varchar(20) 
)
GO

Then, you need to create a stored procedure that will accept this table type as a parameter

CREATE PROCEDURE MyProcedure
(
    @MyTable dbo.MyTableType READONLY -- NOTE: table valued parameters must be Readonly!
)
AS

INSERT INTO MyTable (Col1, Col2)
SELECT Col1, Col2 
FROM @MyTable

GO

Finally, execute this stored procedure from your c# code:

DataTable dt = new DataTable();
dt.Columns.Add("Col1", typeof(int));
dt.Columns.Add("Col2", typeof(string));

// Fill your data table here

using (var con = new SqlConnection("ConnectionString"))
{
    using(var cmd = new SqlCommand("MyProcedure", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@MyTable", SqlDbType.Structured).Value = dt;
        con.Open();
        cmd.ExecuteNonQuery();
    }
}
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Wow, that's something elegant You got my up-vote for that... And it has been been there this whole time and I didn't knew... :( – Adrian Salazar Aug 12 '15 at 12:45
2

You can make use of the SQL syntax:

INSERT INTO YOUR_TABLE (dataColumn) VALUES (data1),(data2),(data3)

So loop over your rows you wanna insert and append ",(datax)" to your query and also add the corresponding parameter. Perhaps it helps.

Tjasun
  • 176
  • 2
  • 10
  • Problem is that that would insert one column at a time right? But I have more then just 1 column or is there a way to fix that too? – Thijser Aug 12 '15 at 12:40
  • That's not the problem, the problem is that you will have to build the values list dynamically and will have to also build the parameters list dynamically or risk sql injection. – Zohar Peled Aug 12 '15 at 12:43
  • 1
    @AdrianSalazar that's a perfectly valid tsql syntax. Try it yourself. – Zohar Peled Aug 12 '15 at 12:45
  • @AdrianSalazar everybody is allowed to make mistakes. so are you :-). – Zohar Peled Aug 12 '15 at 12:52
  • @Thijser in the link there is a loop that executes the query with a single parameter every time. However, It's not impossible to create the parameters list in a loop, and in fact, I think it might be even a good solution if you don't have permissions to create types or procedures on your sql server). – Zohar Peled Aug 12 '15 at 12:57
  • That's also an interesting solution as I'm now requesting said rights. – Thijser Aug 12 '15 at 12:59