1. Create Table type in Sqlserver2008.
CREATE TYPE dbo.WordTable as table
(
[WordText] [nchar]() 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;
} }