SQL. Table-Valued types

Table value type is a new feature in SQL Server 2008, I personally think it is very useful, has never been used before, since came to the present company after many things, so share with you.

------Build Table value type Syntax--------------------------

CREATE TYPE Tptest as TABLE (

Name NVARCHAR () NULL,

Age int NULL,

[Address] NVARCHAR ($) NULL

)

--------the most common usage

DECLARE @table Dbo.tptest

---------Insert

INSERT into @table (name,age,address)

SELECT ' Xiao Wang ', 43, ' Qinghe '

---------Query

SELECT * from @table

--------------------------------------------------------I'm a gorgeous split-line--------------------------------------------------------- ---------------

Another usage is that C # is used when calling stored procedures, a method that is used extensively for feature implementations such as imports.

------Build a Table--------------------------

CREATE TABLE Tbtest (

ID INT IDENTITY (PRIMARY KEY),

Name NVARCHAR () NULL,

Age int NULL,

[Address] NVARCHAR ($) NULL

)

Go

------Build Stored Procedures--------------------------

CREATE PROCEDURE Sp_test_insert

@execelDT tptest READONLY

As

BEGIN

-------Read the value of a table type and insert it into the datasheet

INSERT into dbo. Tbtest (name, age, Address)

SELECT name,age,address from @execelDT

End

------------------------------------------------------C # Background Code-------------------------------------------------------------------

static void Main (string[] args)

{

stringconnectionstring = "server=.; Initial catalog=test;userid=sa;password=sa; Connect timeout=30 ";

Sqlconnectionconnectionobj = new SqlConnection (connectionString); Establish a Connection object

sqlcommandsqlcom =new SqlCommand ("Sp_test_insert", connectionobj);

DataTable DT =new DataTable ();

Dt. Columns.Add ("name", typeof (String));

Dt. Columns.Add ("Age", typeof (int));

Dt. Columns.Add ("Address", typeof (String));

DataRow Dr =dt. NewRow ();

dr["name"] = "Zhang San";

Dr["age"] = 22;

dr["Address" = "Hebei";

Dt. Rows.Add (DR);

DataRow DR2 =dt. NewRow ();

dr2["name"] = "John Doe";

Dr2["age"] = 33;

dr2["Address" = "Beijing";

Dt. Rows.Add (DR2);

Try

{

Connectionobj.open (); Connection Open

/* Create SqlCommand, etc. for database operations */

Sqlcom.commandtype = CommandType.StoredProcedure;

SqlParameter sp = new SqlParameter ("@execelDT", sqldbtype.structured);//table value type to use structured

Sp. Value = DT;

Sqlcom. Parameters.Add (SP);

int index=sqlcom. ExecuteNonQuery ();

if (index>0)

{

Console.WriteLine ("Insert succeeded! ");

}

Connectionobj.close ();//Connection off

}

catch (Exception ex)

{

Throw

}

Console.readkey ();

}

--------------------------------------------Summary---------------------------------------------------

The greatest convenience of table types is that the DataTable can be passed directly to the database as a variable.

Simplifies the complex distribution design of manipulating large amounts of data into data.

Note: The column names in the table type and the column names in the DataTable can be inconsistent (but not recommended), but the order of the columns and variable categories must be the same.

Monitoring the request with SQL Server Profiler finds that he simply translates the data in the DataTable into the INSERT statement into the table type, so it is not related to the name.

SQL. Table-Valued types