This article explains how we can pass our data table to a stored procedure in C#. We all passed parameters to our stored procedures right? What if we get a situation that to pass a bunch of arguments to a stored procedure, for example a normal data table which we create. This post helps you understand it in a step by step manner. I hope you will like it.
Step 1
Create a table:
[sql]
create table TableTest
(
ID INT IDENTITY(1,1) PRIMARY KEY
,NAME VARCHAR(10)
,ADDR VARCHAR(10)
)
[/sql]
Step 2
Create table types:
[sql]
CREATE TYPE TABLETEST AS TABLE
(ID INT)
[/sql]
Step 3
Insert some value:
[sql]
INSERT INTO dbo.TABLETEST VALUES (‘SIBI’, ‘ABCD’)
INSERT INTO dbo.TABLETEST VALUES (‘SIBEESH’, ‘EFGH’)
[/sql]
Step 4
Create a procedure with table as parameter:
[sql]
CREATE PROCEDURE SELECTTABLETEST
(
@TABVAR TABLETEST READONLY
)
AS
BEGIN
SELECT * FROM TABLETEST WHERE ID IN (SELECT ID FROM @TABVAR)
END
[/sql]
C# Function
[csharp]
private void passindDataTableToProcedure()
{
try
{
DataTable dt = new DataTable();
dt.Columns.Add("ID");
var dr = dt.NewRow();
dr["ID"] = 1;
dt.Rows.Add(dr);
using (SqlConnection cn = new SqlConnection(@"Data Source=SIBEESH\SQLEXPRESS;Initial Catalog=Task;Integrate
d Security=True"))
{
if (cn.State == ConnectionState.Open)
cn.Close();
cn.Open();
using (SqlCommand cmd = new SqlCommand("SELECTTABLETEST"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.Parameters.AddWithValue("@TABVAR", dt);
using(SqlDataAdapter da=new SqlDataAdapter(cmd))
{
da.Fill(dt);
}
}
}
}
catch
{
}
}
[/csharp]
Conclusion
What do you think about this? Are you using Spire.PDF yet? Do you plan to try this?Did I miss anything that you may think which is needed?. I hope you liked this article. Please share me your valuable suggestions and feedback.
Your turn. What do you think?
A blog isn’t a blog without comments, but do try to stay on topic. If you have a question unrelated to this post, you’re better off posting it on Stack Overflow instead of commenting here. Tweet or email me a link to your question there and I’ll definitely try to help if I am able to.
Kindest Regards
Sibeesh Venu