Adding multiple parameterized variables to a database in c#

Question

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).

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
– Adrian Salazar, Commented Aug 12, 2015 at 12:39

Zohar Peled · Accepted Answer · 2018-10-30 06:16:00Z

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();
    }
}

edited Oct 30, 2018 at 6:16

answered Aug 12, 2015 at 12:40

Zohar Peled

82.9k10 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adrian Salazar Over a year ago

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... :(

Tjasun · Accepted Answer · 2015-08-12 12:37:45Z

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.

answered Aug 12, 2015 at 12:37

Tjasun

1762 silver badges10 bronze badges

6 Comments

Thijser Over a year ago

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?

Zohar Peled Over a year ago

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 Over a year ago

@AdrianSalazar that's a perfectly valid tsql syntax. Try it yourself.

Zohar Peled Over a year ago

@AdrianSalazar everybody is allowed to make mistakes. so are you :-).

Zohar Peled Over a year ago

@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).

|

Collectives™ on Stack Overflow

Adding multiple parameterized variables to a database in c#

2 Answers 2

1 Comment

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related