Int rowsAffected = ExecStoredProcWithTVP(Connection, "usp_DoSomethingWithTableTypedParameter", "udt_UserId", dt)
#OCTOPARSE PASS PARAMETERS CODE#
Then you can write DAL functions which use this utility function with actual names of stored procedures to build on the example in your question, here is what the code would look like: public int usp_DoSomethingWithTableTypedParameter(List userIdList) Int rowsAffected = cmd.ExecuteNonQuery() // or could execute reader and pass a Func to perform action on the datareader SqlCommand cmd = new SqlCommand(storedProcedureName, conn) Ĭmd.CommandType = CommandType.StoredProcedure
Using (SqlConnection conn = new SqlConnection(connection.ConnectionString)) net, you cannot use LINQ since it does not support Table Valued Parameters yet so you have to write a function which does plain old ADO.net, takes a DataTable, and passes it to the stored procedure: I've written a generic function I use which can do this for any stored procedure as long as it takes just the one table-typed parameter, regardless of what it is public static int ExecStoredProcWithTVP(DbConnection connection, string storedProcedureName, string tableName, string tableTypeName, DataTable dt)
WHERE userId IN (SELECT UserId FROM from. Then, you need to write a stored procedure which accepts this type as a parameter: CREATE PROCEDURE udt_UserId READONLY If you are using SQL 2008, you can create a stored procedure which accepts a Table Valued Parameter (TVP) and use ADO.net to execute the stored procedure and pass a datatable to it:įirst, you need to create the Type in SQL server: CREATE TYPE.
0 kommentar(er)
