Pass table valued parameter using ADO.Net

How to pass table valued parameter to stored procedure using ADO.Net?

Answers


1 Create type in sql server:

    CREATE TYPE [dbo].[MyDataType] As Table
(
    ID INT,
    Name NVARCHAR(50)
)

2 Create Procedure

CREATE PROCEDURE [dbo].[MyProcedure]
(
@myData As [dbo].[MyDataType] Readonly
)
AS

Begin

    Select * FROM @myData
End

3 Create datatable in C#

DataTable myDataTable = new DataTable("MyDataType");
            myDataTable.Columns.Add("Name", typeof(string));
            myDataTable.Columns.Add("Id", typeof(Int32));
            myDataTable.Rows.Add("XYZ", 1);
            myDataTable.Rows.Add("ABC", 2);

4 Create sql parameter

SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = "@myData";
            parameter.SqlDbType = System.Data.SqlDbType.Structured;
            parameter.Value = myDataTable;
            command.Parameters.Add(parameter); 

I tried this and received the exception:

The table type parameter '@MyDataType' must have a valid type name.

I had to set the "TypeName" property of the SqlParameter:

parameter.TypeName = "MyDataType";

Its good question, I took more help in this blog. You can check it once.

http://www.enukesoftware.com/blog/table-value-parameters-for-easy-asp-net-implementations/


See this article on MSDN:

Table-Valued Parameters in SQL Server 2008 (ADO.NET)


This question is a duplicate of How to pass table value parameters to stored procedure from .net code. Please see that question for an example illustrating the use of either a DataTable or an IEnumerable<SqlDataRecord>.


You can prefix with Exec

using( SqlConnection con = new SqlConnection( "Server=.;database=employee;user=sa;password=12345" ) )
    {
        SqlCommand cmd = new SqlCommand( " exec ('drop table '+@tab)" , con );
        cmd.Parameters.AddWithValue( "@tab" ,"Employee" );
        con.Open( );
        cmd.ExecuteNonQuery( );
    }

Need Your Help


About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.