Pass List parameters to SQL Server stored procedure

Question

I'm new to c# and .Net and i need your help .

I've create a procedure in the DB that accepts table value parameter ( list of id's ) , and i'm looking the appropriate equivalent for an Oracle Associative Array .

--New Data Type in the DB:

CREATE TYPE IDType AS TABLE
(
        ID bigint  
)
GO



-- DB Procedure
Create procedure TVP_PROC (@ID IDType readonly)
    Declare @TVP IDType
    Insert into @TVP select ID from @ID
As
  Update  my_tbl set id=(select ID from @TVP)

it should run like this : exec TVP_PROC @ID //@ID is alist of id's

.Net Code :

Public void UpdateID (List long<long> IDS )
{
    Datatable datatable = new datatable ();
    Datatable.columns.add (“IDS”,typeof(int64));
    Foreach (int64 id in IDS)
    {
        Datatable.Rows.Add(id);
    }
}


Var Query =hibernate.createsqlquery(“Exec TVP_PROC @ID:IDS”);

Query. ?????????

Questions :

is there any preferred way to write it except using datatable variable and assigning the ids every iteration?
More important , what is the appropriate way to assign the table\list variable ? is there any table value \ array variable which can be define to this kind of datatype ?

It's 2012 with the Table value parameter .

Tamirt
– Tamirt

2012-08-26 09:47:32 +00:00
Commented Aug 26, 2012 at 9:47 — Tamirt
– Tamirt, Commented Aug 26, 2012 at 9:47

marc_s · Accepted Answer · 2012-08-26 09:57:02Z

1

Well, now that you've created your table type and stored procedure in the database, in C#, you need code something like this:

// define connection and command object
using(SqlConnection conn = new SqlConnection("your-connection-string-here"))
using(SqlCommand cmd = new SqlCommand("dbo.TVP_PROC", conn))
{
   // define the command to be a stored procedure
   cmd.CommandType = CommandType.StoredProcedure;

   // set up parameters
   cmd.Parameters.Add("@ID", SqlDbType.Structured).Value = datatable;

   // open connection, execute stored procedure, close connection
   conn.Open();
   cmd.ExecuteNonQuery();
   conn.Close();
}

answered Aug 26, 2012 at 9:57

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Comments

Hassan Boutougha · Accepted Answer · 2012-08-26 09:57:12Z

1

    Datatable datatable = new datatable ();
    Datatable.columns.add (“IDS”,typeof(int64));
    Foreach (int64 id in IDS)
    {
        Datatable.Rows.Add(id);
    }

 // Configure the SqlCommand and table-valued parameter.
 SqlCommand insertCommand = new SqlCommand("TVP_PROC", connection);

 insertCommand.CommandType = CommandType.StoredProcedure;

 //pass here your datatable
 SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@ID", datatable);

 //specify type of your parameter
 tvpParam.SqlDbType = SqlDbType.Structured;

  // Execute the command.
  insertCommand.ExecuteNonQuery();

answered Aug 26, 2012 at 9:57

Hassan Boutougha

3,9191 gold badge19 silver badges19 bronze badges

Collectives™ on Stack Overflow

Pass List parameters to SQL Server stored procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related