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();
        }
    }
}