Adding multiple parameterized variables to a database in c#

by

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

More Related Contents:

  1. SQL WHERE clause matching values with trailing spaces
  2. Taking lot of time to read from SQL Server using c#
  3. SQL Server Char/VarChar DateTime Error [closed]
  4. Delete from two tables? [closed]
  5. I want to insert a record in DB and then need to return a row
  6. How to get last inserted id?
  7. What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]
  8. How do I clean SqlDependency from SQL Server memory?
  9. Insert entire DataTable into database at once instead of row by row?
  10. Getting return value from stored procedure in C#
  11. SQL error: Incorrect syntax near the keyword ‘User’
  12. What represents a double in sql server?
  13. Passing List to SQL Stored Procedure
  14. How to retrieve data from a SQL Server database in C#?
  15. SqlParameter does not allows Table name – other options without sql injection attack?
  16. Any way to SQLBulkCopy “insert or update if exists”?
  17. SQLException : String or binary data would be truncated
  18. Multi threading C# application with SQL Server database calls
  19. What’s the fastest way to bulk insert a lot of data in SQL Server (C# client)
  20. ‘datetime2’ error when using entity framework in VS 2010 .net 4.0
  21. ExecuteNonQuery returning -1 when using sql COUNT despite the query string
  22. Calling SQL Defined function in C#
  23. Add WHERE clauses to SQL dynamically / programmatically
  24. How to pass string array in SQL parameter to IN clause in SQL
  25. When is it better to write “ad hoc sql” vs stored procedures [duplicate]
  26. Query validation using C#
  27. Use SQL Server time datatype in C#.NET application?
  28. Generating sql code programmatically
  29. How to give ADO.NET Parameters
  30. Is SqlConnection.Close() need inside a Using statement?

Leave a Comment