how to use table type in .net

浏览数：21 / 时间：2015年06月09日

1. Create Table type in Sqlserver2008.

CREATE TYPE  dbo.WordTable as table
(
    [WordText] [nchar](100) NULL,
    [WordCount] [int] NULL
)

And the target table is:

CREATE TABLE [dbo].[A_WordCount](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[WordText] [nchar](100) NULL,
	[WordCount] [int] NULL
)

2.Create Store Procedure.

ALter PROCEDURE [dbo].[A_Words]
    @Word as dbo.WordTable READONLY
AS

BEGIN
    insert into dbo.A_WordCount(WordCount,WordText)
    select w.WordCount,w.WordText from @Word w
    
END

3. First we will create a table. Then we will pass this table to the store procedure.

        public static DataTable ConvertToTable(List<WordClass> wordLst)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("WordText", typeof (string));
            dt.Columns.Add("Count", typeof (int));

            foreach (var word in wordLst)
            {
                DataRow row = dt.NewRow();
                row["WordText"] = word.WordValue;
                row["Count"] = word.Count;
                dt.Rows.Add(row);
            }
            return dt;
        }

        public static void WriteData(DataTable dtTable)
        {
            commandText = "dbo.[A_Words]";
            SqlConnection con;
            try
            {
                using (con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand(commandText, con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Word", SqlDbType.Structured));
                    cmd.Parameters["@Word"].Value = dtTable;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
            catch (Exception)
            {
                throw;
            }

        }