\t\t在MSSQL中定义和使用C#自定义类型 SQL Server08表类型参数传递

最新推荐文章于 2013-06-09 15:37:05 发布

原创最新推荐文章于 2013-06-09 15:37:05 发布 · 1.6k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

c# 研究

本文介绍了如何在SQL Server 2008中利用自定义表类型作为存储过程参数，通过C#创建DataTable实例并传递，简化批量数据操作。示例包括创建自定义表类型、存储过程，以及C#代码实现数据插入。

在学习SQL Server 2008的过程中，突然发现SQL Server支持自定义表类型，我们可以轻松的将一个SQL Server 2008表类型作为参数传递给存储过程。C#下实现了SQL Server 2008表类型参数传递

本示例中用到的类型在数据库中的位置：

创建一个自定义表类型
CREATE TYPE [dbo].[UserDetailsType] AS TABLE
(
      [ID] [varchar](50) NULL,
      [Name] [varchar](50) NULL,
      [Sex] [varchar](50) NULL,
      [Age] [decimal](18, 0) NULL
)

创建一个名为User的表，结构如下：
在MSSQL中定义和使用C自定义类型 SQL Server08表类型参数传递 - yandavid - 我的博客
创建一个存储过程
CREATE PROCEDURE [dbo].[InsertUserInfo]
@UserInfo [UserDetailsType] readonly--指示不能在过程的主体中更新或修改参数。如果参数类型为用户定义的表类型，则必须指定 READONLY
AS
BEGIN

insert into [User]
([ID], [Name], [Sex], [Age])
select [ID], [Name], [Sex], [Age]
from @UserInfo;

END

//启动Visual Studio 2008，创建一个默认的窗体应用程序后，我们需要先在内存中创建一个数据库表DataTable的实例，如下：
private static DataTable PrepareDatatable()
{
    DataTable dt = new DataTable("dt");
    DataColumn[] dtc = new DataColumn[4];
    dtc[0] = new DataColumn("ID", System.Type.GetType("System.String"));
    dtc[1] = new DataColumn("Name", System.Type.GetType("System.String"));
    dtc[2] = new DataColumn("Sex", System.Type.GetType("System.String"));
    dtc[3] = new DataColumn("Age", System.Type.GetType("System.Decimal"));
    dt.Columns.AddRange(dtc);
    return dt;
}

//然后，通过SqlCommand执行刚才我们创建的Test数据库存储过程InsertUserInfo，并传递我们在内存中创建的DataTable的实例，如下：
private static void SaveUserInfoDetails()
{
    DataTable dt = PrepareDatatable();
    for (int i = 0; i < = 5; i++)
    {
        DataRow dr = dt.NewRow();
        dr[0] = i.ToString();
        dr[1] = "Name" + i.ToString();
        dr[2] = "男";
        dr[3] = (i*10).ToString();
        dt.Rows.Add(dr);
    }
    using (SqlConnection conn = new SqlConnection("server=Rithia;database=Test;integrated security=SSPI"))
    {
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "dbo.InsertUserInfo";

        SqlParameter param = cmd.Parameters.AddWithValue("@UserInfo", dt);
        conn.Open();
        cmd.ExecuteNonQuery();
    }

}

通过上面的示例，我们可以在程序客户端先创建好要传递的表类型数据，然后传递给存储过程，而存储过程则将SQL Server 2008表类型参数中的记录一次性的添加到了数据库实体表中，这种操作在需要传递给存储过程数组形式的参数时非常非常方便。

--------------原始做法：---------------------------------------------

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace MyDBType
{
    [Serializable]//标记为是可序列化的
    [SqlUserDefinedType(Format.UserDefined, Name = "Person", MaxByteSize = 100)]
    /*
        SqlUserDefinedType特性支持以下属性：

Format——用语指定如何在SQL Server数据库中序列化用户自定义类型。其取值为Native和UserDefined;

IsByteOrdered——用于使该自定义用户类型按其本身的字节表示方法排序；

IsFixedLength——用于指定这个类型的所有实例是否都具有相同的长度；

MaxByteSize——用于指定该用户自定义类型的最大字节数；

Name——用于为用户自定义类型指定名称；

ValidationMethodName——用于指定校验用户自定义类型是否有效的方法名称。

        Format属性最为重要，他自动用户自定义类型如何进行序列化，选项设置为Native时是让SQL Server数据库自动处理所有序列化问题而不需要用户做任何额外的操作。
                但原生序列化方式只能应用到简单类上。如果类公开了非值类型的属性（如String)，那么该类将不能使用原生序列化。
     */
    public class Person : IBinarySerialize, INullable
    {
        public string Name;
        public int Age;
        public char Sex;

        /// <summary>
        /// 在SQL查询中对该属性进行比较操作所必须指定的特性
        /// </summary>
        [SqlFacet(Precision = 38, Scale = 2)]
        public Decimal Account;

        #region IBinarySerialize 成员
        /// <summary>
        /// 用于从BinaryReader对象中读取数据到类中的属性
        /// </summary>
        /// <param name="r"></param>
        public void Read(System.IO.BinaryReader r)
        {
            string s = r.ReadString();
            string[] values = s.Split('|');
            Name = values[0];
            if (values.Length > 1) Int32.TryParse(values[1], out Age);
            if (values.Length > 2) Char.TryParse(values[2], out Sex);
            if (values.Length > 3) Decimal.TryParse(values[3], out Account);
        }

        /// <summary>
        /// 把类中的属性写入到BinaryReader对象
        /// </summary>
        /// <param name="w"></param>
        public void Write(System.IO.BinaryWriter w)
        {
            w.Write(string.Format("{0}|{1}|{2}|{3}", Name, Age.ToString(), Sex, Account));
        }

#endregion

        #region INullable 成员
        /// <summary>
        /// 用于在sql中判断该类型的变量是否为null
        /// </summary>
        public bool IsNull
        {
            get { return string.IsNullOrEmpty(Name); }
        }

        #endregion
        /// <summary>
        /// 实现静态的返回类型为当前类类型的Null只读属性，返回一个在sql中认为为null的实例
        /// </summary>
        public static Person Null
        {
            get
            {
                return new Person { Name = string.Empty };
            }
        }
        /// <summary>
        /// 类型转换函数
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static Person Parse(SqlString str)
        {
            string[] values = str.Value.Split('|');
            var p = new Person
                        {
                            Name = values[0],
                        };
            if (values.Length > 1) Int32.TryParse(values[1], out p.Age);
            if (values.Length > 2) Char.TryParse(values[2], out p.Sex);
            if (values.Length > 3) Decimal.TryParse(values[3], out p.Account);

return p;
}

        public override string ToString()
        {
            return this.IsNull ? "NULL" : string.Format("{0}|{1}|{2}|{3}", Name, Age.ToString(), Sex,Account);
        }
    }
}

---------SQL脚本

select * from sys.assemblies

--引用该类所在的程序集

CREATE assembly MyType

from 'C:\MyDBType.dll'

--创建具体的类型 create type 类型名external name sql中程序集名.[C#类完全限定名]

CREATE TYPE person

external name MyType.[MyDBType.Person]

--创建以自定义类型为参数的存储过程

create proc MyDBTypeTest

@p person

print @p.Name