Jerry Nixon on Windows: Passing a table-type parameter in ADO.Net

Jerry Nixon on Windows

Monday, June 8, 2009

Passing a table-type parameter in ADO.Net

In SQL, you have always been able to create a custom type. But now you have the ability to create a custom type that is a TABLE type. With a TABLE type you literal define columns and column types. But you can then use this type as the input parameter type of a stored procedure. An age-old problem finally solved. How do you do it? I have a simple working demo below that shows the SQL and the ADO.Net code in C#. Happy coding!

// tSQL setup scripts
// create the SQL table

create table Bugs
(
BugId int primary key
identity(1,1)
,Title varchar(50)
,Details varchar(50)
)

// create the SQL table TYPE

create type BugsType as table
(
Title varchar(50)
,Details varchar(50)
)

// create the SQL insert stored proc

create proc BugsInsert
(@x BugsType readonly) as
set nocount on
insert into Bugs
select * from @x

declare @p BugsType
insert into @p values
('Login Fails', 'Password rejected')
,('Login Fails', 'Username rejected')
exec BugsInsert @p

// C# // how to insert a table-type parameter using ADO.Net

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace TableValuedParameters
{
public partial class Form1 : Form
{
DataTable m_DataTable = new DataTable();
public Form1()
{
InitializeComponent();
m_DataTable.Columns.Add(new DataColumn("Title", typeof(string)));
m_DataTable.Columns.Add(new DataColumn("Details", typeof(string)));
// you will need to add your own dataGridView to your form
dataGridView1.DataSource = m_DataTable;
}

// this is the save button event handler (this form only inserts)
private void button1_Click(object sender, EventArgs e)
{
string _ConnStr = @"server=.\sql2008express;database=jerry;integrated security=sspi;";
using (SqlConnection _SqlConnection = new SqlConnection(_ConnStr))
{
_SqlConnection.Open();
SqlCommand _SqlCommand = new SqlCommand("BugsInsert", _SqlConnection);
_SqlCommand.CommandType = CommandType.StoredProcedure;
// here's the magic, pass a datatable
_SqlCommand.Parameters.AddWithValue("x", m_DataTable);
_SqlCommand.ExecuteNonQuery();
}
this.Close();
}
}
}