首页 > 代码库 > how to use table type in .net

how to use table type in .net

2024-07-21 21:12:23   220人阅读

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 READONLYASBEGIN    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 =http://www.mamicode.com/ dtTable;                    con.Open();                    cmd.ExecuteNonQuery();                    con.Close();                }            }            catch (Exception)            {                throw;            }        }

 

how to use table type in .net


联系
我们