SQL 2008 TVP資料匯入

using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
 
namespace TestTVP
{
 class Program
 {
 private static string cnStr =
 “Data Source=Sql2008Server;User Id=user; Password=pwd;Initial Catalog=Playground”;
 
 static void Main(string[] args)
 {
 int TEST_SIZE = 2000;
 
 using (SqlConnection cn = new SqlConnection(cnStr))
 {
 Stopwatch sw = new Stopwatch();
 cn.Open();
 
 //建立測試要用的Table及Table Type
 sw.Start();
 CreateTableAndTableType(cn);
 sw.Stop();
 Console.WriteLine(“建立測試環境 {0:N0}ms”, sw.ElapsedMilliseconds);
 
 //建立DataTable物件並塞入測試資料
 sw.Reset();
 sw.Start();
 DataTable t = new DataTable();
 t.Columns.Add(“Id”, typeof(Guid));
 t.Columns.Add(“Name”, typeof (string));
 t.Columns.Add(“Birthday”, typeof (DateTime));
 t.Columns.Add(“Score”, typeof (int));
 Random rnd = new Random();
 for (var i = 0; i < TEST_SIZE; i++)
 t.Rows.Add(
 Guid.NewGuid(),
 string.Format(“User{0}”, i),
 DateTime.Today.AddDays(rnd.Next(20000)),
 rnd.Next(65536)
 );
 sw.Stop();
 Console.WriteLine(“建立資料 {0:N0}ms”, sw.ElapsedMilliseconds);
 
 //測試一: 使用SqlCommand連續INSERT
 sw.Reset();
 sw.Start();
 SqlCommand cmd = cn.CreateCommand();
 cmd.CommandText =
 @”
INSERT INTO [Playground].[dbo].[Player]
 ([Id],[Name],[BirthDay],[Score])
VALUES
 (@Id, @Name, @BirthDay, @Score)
“;
 SqlParameter pId = 
 cmd.Parameters.Add(“@Id”, SqlDbType.UniqueIdentifier);
 SqlParameter pName =
 cmd.Parameters.Add(“@Name”, SqlDbType.VarChar);
 SqlParameter pBirthDay =
 cmd.Parameters.Add(“@BirthDay”, SqlDbType.DateTime);
 SqlParameter pScore =
 cmd.Parameters.Add(“@Score”, SqlDbType.Int);
 foreach (DataRow row in t.Rows)
 {
 pId.Value = row[“Id”];
 pName.Value = row[“Name”];
 pBirthDay.Value = row[“BirthDay”];
 pScore.Value = row[“Score”];
 cmd.ExecuteNonQuery();
 }
 sw.Stop();
 Console.WriteLine(“SqlCommand連環Call {0:N0}ms”, sw.ElapsedMilliseconds);
 
 sw.Reset();
 sw.Start();
 TruncateTable(cn);
 sw.Stop();
 Console.WriteLine(“清除資料表 {0:N0}ms”, sw.ElapsedMilliseconds);
 
 //測試2 使用TVP
 sw.Reset();
 sw.Start();
 cmd = cn.CreateCommand();
 cmd.CommandText = “INSERT INTO Player SELECT * FROM @Player”;
 SqlParameter pTVP = cmd.Parameters.Add(“@Player”, SqlDbType.Structured);
 pTVP.Value = t; //SqlParameter選用SqlDbType.Structured並指定TypeName
 pTVP.TypeName = “TVP_Player”; 
 cmd.ExecuteNonQuery();
 sw.Stop();
 Console.WriteLine(“TVP匯入資料表 {0:N0}ms”, sw.ElapsedMilliseconds);
 
 cn.Close();
 }
 Console.Read();
 }
 
 static void CreateTableAndTableType(SqlConnection cn)
 {
 SqlCommand cmd = new SqlCommand();
 cmd.Connection = cn;
 cmd.CommandText =
 @”
IF EXISTS (
 SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(‘Player’)

BEGIN
 DROP TABLE Player
 DROP TYPE TVP_Player
END;
CREATE TABLE [Player] (
 [Id] [uniqueidentifier] NOT NULL,
 [Name] [varchar](32) NOT NULL,
 [BirthDay] [datetime] NOT NULL,
 [Score] [int] NOT NULL,
 CONSTRAINT [PK_Player] PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TYPE dbo.TVP_Player AS TABLE (
 [Id] [uniqueidentifier] NOT NULL,
 [Name] [varchar](32) NOT NULL,
 [BirthDay] [datetime] NOT NULL,
 [Score] [int] NOT NULL,
 PRIMARY KEY ( Id )
);
“;
 cmd.ExecuteNonQuery();
 }
 
 static void TruncateTable(SqlConnection cn)
 {
 SqlCommand cmd = new SqlCommand();
 cmd.CommandText = “TRUNCATE TABLE Player”;
 cmd.Connection = cn;
 cmd.ExecuteNonQuery();
 }
 }
}

Show your support

Clapping shows how much you appreciated Alex Leung’s story.