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();
}
}
}