快速玩转 SQL Server中带表类型参数的存储过程

摘要：

　　　　最近做项目中遇到批量添加和修改的问题，在老大的指导下学会了使用表类型参数来做批量操作。为了巩固强化，围绕这个技术又做了个小demo。

开始正题：

　　　　首先，我们在SQL Server 2008下创建一个示例数据库名为TableTypeTest，再在该数据库下创建一个名为Class和Student的表，结构如下：

再在TableTypeTest数据库下创建一个自定义表类型，取名StudentType，如下：

View Code

CREATE TYPE [dbo].[StudentType] AS TABLE(
    [SID] [int] NOT NULL,
    [CID] [int] NOT NULL,
    [SName] [nvarchar](50) NOT NULL
)
GO

然后，创建两个存储过程，批量添加和批量修改，分别为InserNewStudent和UpdateStudent，如下

InserNewStudent：

View Code

CREATE PROCEDURE [dbo].[InserNewStudent] 
    @Dt dbo.StudentType readonly
AS
BEGIN
    insert into dbo.Student(CID,SName) select t.CID,t.SName  from @Dt as t 
END
GO

UpdateStudent：

View Code

CREATE PROCEDURE [dbo].[UpdateStudent] 
    @Dt dbo.StudentType readonly
AS
BEGIN
    Update dbo.Student set CID=t.CID,SName=t.SName from @Dt as t where dbo.Student.[SID]=t.[SID]
END
GO

启动Visual Studio 2010，创建一个默认的窗体应用程序，窗体用于显示所有班级列表，操作每个班级下的学生通过选中该班级，然后右键操作。如下：

创建显示班级下的所有学生列表窗体，如下：

创建批量添加学生的窗体，如下：

关键代码如下：

View Code

/// <summary>
         /// 批量添加
         /// </summary>
         public static bool AddBantch(DataTable dt) {
             string pName = "dbo.InserNewStudent";
             List<SqlParameter> pList = new List<SqlParameter>() {
             DbHelper.CreateSqlParemeterStructured("@Dt",dt)
             };
             try
             {
                 DbHelper.RunProcedure(pName, pList);
                 return true;
             }
             catch
             {
                 return false;
             }
         }

View Code

private void button1_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(this.textBox1.Text) || string.IsNullOrEmpty(this.textBox4.Text) || string.IsNullOrEmpty(this.textBox6.Text))
            {
                MessageBox.Show("不能有空文本框");
            }
            else {
                DataTable dt = new DataTable();
                dt.Columns.Add("SID");
                dt.Columns.Add("CID");
                dt.Columns.Add("SName");
                DataRow dr;
                dr = dt.NewRow();
                dr["SID"] = 0;//此列虽然在添加的时候无用，但必须赋值，否则报错
                dr["CID"] = CID;
                dr["SName"] = this.textBox1.Text;
                dt.Rows.Add(dr);
                dr = dt.NewRow();
                dr["SID"] = 0;
                dr["CID"] = CID;
                dr["SName"] = this.textBox4.Text;
                dt.Rows.Add(dr);
                dr = dt.NewRow();
                dr["SID"] = 0;
                dr["CID"] = CID;
                dr["SName"] = this.textBox6.Text;
                dt.Rows.Add(dr);

                if (Library.AddBantch(dt))
                {
                    MessageBox.Show("批量添加成功！");
                    this.Close();
                }
                else
                {
                    MessageBox.Show("批量添加失败！");
                    this.Close();
                }
            }
        }

创建批量更改班级学生姓名的窗体，如下：

关键代码如下：

View Code

/// <summary>
         /// 批量修改
         /// </summary>
         public static bool UpdateBantch(DataTable dt) {
             string pName = "dbo.UpdateStudent";
             List<SqlParameter> pList = new List<SqlParameter>() { 
             DbHelper.CreateSqlParemeterStructured("@Dt",dt)
             };
             try
             {
                 DbHelper.RunProcedure(pName, pList);
                 return true;
             }
             catch 
             {
                 return false;
             }
         }

View Code

private void button1_Click(object sender, EventArgs e)
        {
            int rows = this.dataGridView1.Rows.Count;
            DataTable dt = new DataTable();
            dt.Columns.Add("SID");
            dt.Columns.Add("CID");
            dt.Columns.Add("SName");
            DataRow dr;
            for (int i = 0; i < rows;i++ ) {
            DataGridViewRow dtr=this.dataGridView1.Rows[i];
            dr = dt.NewRow();
            dr["SID"] = dtr.Cells[0].Value;
            dr["CID"] = dtr.Cells[1].Value;
            dr["SName"] = dtr.Cells[2].Value;
            dt.Rows.Add(dr);
            }
            if (Library.UpdateBantch(dt))
            {
                MessageBox.Show("批量更改成功！");
            }
            else {
                MessageBox.Show("批量更改失败！");
            }
        }