Table valued parameters i sql-server 2008

Jag vill tipsa om en ny funktion i sql-server 2008 som gör det enkelt att skicka in flera värden till en lagrad procedur. Detta är användbart exempelvis när man behöver skapa flera nya poster samtidigt, eller kanske radera. Innan sql-server 2008 och möjligheten med table valued parameters så fick man skicka in en kommaseparerad sträng som man delar upp och kör en insert/update/delete för varje del.

Med table valued parameters så kan man skicka in en lista, exempelvis datatable, eller en List som innehåller ditt data. Detta sparar givetvis en hel del jobb eftersom man då kan skicka sin kollektion direkt utan att behöva göra om den till en kommaseparerad sträng. Dessutom får man på databassidan direkt ett set att jobba med som man exempelvis kan använda för att göra MERGE eller JOIN. Jag skriver om merge i mitt inlägg om nyheter i sql-server 2008.

Det krävs några saker för att få table valued parameters att fungera. Först behöver man skapa en tabell (som kan användas i exemplet). Exemplet nedan går ut på att vi har en tabell med namn. Tanken är att vi skall kunna skicka in en lista på namn till vår lagrade procedur som sedan lagras i vår tabell.

CREATE TABLE Names(
	[UserId] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[Firstname] [varchar](50) NOT NULL,
	[Lastname] [varchar](50) NOT NULL,
)

Det vi behöver göra sedan är att skapa en egendefinierad tabelltyp som motsvarar den data vi skickar in till vår lagrade procedur. Det gör vi på följande sätt.

CREATE TYPE NamesTVP AS TABLE(Firstname nvarchar(50), Lastname nvarchar(50))

Sedan behöver vi skapa vår lagrade procedur som använder sig av vår nyskapade typ som inparameter. Notera att parametern måste vara READONLY för att table valued parameters skall fungera.

CREATE PROCEDURE InsertNames (@names NamesTVP READONLY)
AS
INSERT INTO Names (Firstname, Lastname)
	SELECT * FROM @names

Nu kan vi exempelvis anropa proceduren på följande sätt

DECLARE @names AS NamesTVP
INSERT INTO @names (Firstname, Lastname) VALUES ('john', 'doe')
INSERT INTO @names (Firstname, Lastname) VALUES ('jane', 'doe')
EXEC InsertNames @names
SELECT * FROM Names

Notera att datatypen för variabeln @names är NamesTVP. Jag skapar två poster och kör sedan min lagrade procedur med min nya datatyp som inparameter. Nu innehåller tabellen Names dessa två poster.

Men jag vill framförallt visa hur man gör i c#.

static void Main(string[] args)
{
    using (SqlConnection conn = new SqlConnection("Data Source=localhost\\SQLExpress;Initial Catalog=MYDB;Integrated Security=SSPI;"))
    {
        using (SqlCommand cmd = new SqlCommand("InsertNames", conn))
        {
            List<SqlDataRecord> names = new List<SqlDataRecord>();

            names.Add(CreateSqlDataRecord("john", "doe"));
            names.Add(CreateSqlDataRecord("jane", "doe"));
            
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter sp = cmd.Parameters.AddWithValue("@names", names);
            sp.SqlDbType = SqlDbType.Structured;
            sp.TypeName = "NamesTVP";

            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

private static SqlDataRecord CreateSqlDataRecord(string firstname, string lastname)
{
    SqlDataRecord dr = new SqlDataRecord(
        new SqlMetaData[] 
        { 
            new SqlMetaData("Firstname", SqlDbType.VarChar, 50), 
            new SqlMetaData("Lastname", SqlDbType.VarChar, 50) 
        });
    dr.SetSqlString(0, firstname);
    dr.SetSqlString(1, lastname);

    return dr;
}

Som ni ser är det bara två skillnader mot hur man normalt gör i c#.

sp.SqlDbType = SqlDbType.Structured; – anger att det är table valued parameters
sp.TypeName = ”NamesTVP”; – namnet på den egendefinierade tabelltypen.

Det går också bra att skicka in ett datatable, då kan man göra på följande sätt.

DataTable dt = new DataTable();
dt.Columns.Add("Firstname", typeof(string));
dt.Columns.Add("Lastname", typeof(string));

DataRow dr = dt.NewRow();
dr["Firstname"] = "john";
dr["Lastname"] = "doe";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr["Firstname"] = "jane";
dr["Lastname"] = "doe";
dt.Rows.Add(dr);

cmd.CommandType = CommandType.StoredProcedure;
SqlParameter sp = cmd.Parameters.AddWithValue("@names", dt);
sp.SqlDbType = SqlDbType.Structured;
sp.TypeName = "NamesTVP";

conn.Open();
cmd.ExecuteNonQuery();

1 kommentar

  1. […] bättre prestanda eftersom färre databasanrop krävs. Jag har skrivit ett längre blogginlägg om table valued parameters, läs det gärna för att lära dig […]

RSS feed for comments on this post

Kommentarer inaktiverade.

%d bloggare gillar detta: