Using Table-Valued Parameters to Update Multiple Rows

Downloads
128864.zip

In previous versions of SQL Server, if I needed to update numerous rows of data, I used a temporary table. Suppose, for example, that thousands of transactions needed their transaction dates updated. I would upload the transaction IDs with the new date value in a temporary table, add a join between the transaction table and the temporary table, and update the transaction table with the new values from the temporary table.

Although it seemed relatively simple to update thousands of records with temporary tables, what if I had thousands of transactions that needed multiple field values updated? Or what if the updating became a regular request and the company needed to have the functionality available in the application? The process would be difficult to implement and would have a performance impact on the database. With table-valued parameters, available in SQL Server 2008, the update process is easy to implement and has little performance impact on the database. Table-valued parameters can be passed as read-only input parameters. Because transaction logs are not recorded for table-valued parameters, performance improves. Previously, when multiple values were involved, the insert statement was executed multiple times; now, I use a table-valued parameter—with numerous values—that’s treated as one value and requires only one insert statement. The reduced number of trips between client and server boosts performance.

Creating and Using Table-Valued Parameters in T-SQL

A table-valued parameter is a new type of stored-procedure capability available in SQL Server 2008. Basically, a table-valued parameter lets you use an array of data in T-SQL, as well as send an entire data-set table as a parameter in a stored procedure or function. SQL Server 2008 stores this parameter as a user-defined table type. You can also add primary key and unique constraints to the table type.

The ddl_script.sql in Listing 1 sets up the test database MovieRentalDB with a table and the table type. Additionally, it populates the table with values.

Listing 1: Code to Populate MovieRentalDB with a Table and Table Type

CREATE DATABASE \\[MovieRentalDB\\]

USE MovieRentalDB

CREATE TABLE TransLog(

TransID int IDENTITY(101,1) PRIMARY KEY not null,

UserID int not null,

TransDate date not null,

TypeID int not null)

CREATE TYPE dbo.ChangeTransDate AS TABLE(

            TransID varchar(36) NOT NULL,

            NewDate datetime NOT NULL)

INSERT INTO TransLog VALUES(1,'8/8/2009',1)

INSERT INTO TransLog VALUES(2,'8/8/2009',2)

INSERT INTO TransLog VALUES(3,'8/8/2009',3)

INSERT INTO TransLog VALUES(4,'8/8/2009',4)

INSERT INTO TransLog VALUES(5,'8/8/2009',5)

The three steps that follow, each with its code segment, let you see how to create and execute table-valued parameters in T-SQL. First, with the code segment below, you create a new user-defined table type.

--Create new table type to use as a table-value parameter

CREATE TYPE dbo.ChangeTransDate AS TABLE(

            TransID varchar(36) NOT NULL,

            NewDate datetime NOT NULL)

Second, you create a stored procedure with a table-valued parameter, as Listing 2 shows. Third, you execute the stored procedure, as Listing 3 shows. Figure 1 shows the results of the insert statement in Listing 3.

Figure 1: Output from the Insert statement

Listing 2: Code to Create Stored Procedure

--Create procedure to update many rows

CREATE PROCEDURE \\[dbo\\].\\[UpdateTransactionDates\\]

            @TableParam ChangeTransDate READONLY

AS

UPDATE b SET b.TransDate = a.NewDate

FROM @TableParam a

JOIN TransLog b ON b.TransID=a.TransID

Listing 3: Code to Execute Stored Procedure

--Add the table-value parameter and call the stored procedure DECLARE @NewTableParam ChangeTransDate

INSERT INTO @NewTableParam(TransID,NewDate)

VALUES (101,'1/10/2009'),(102,'2/10/2009'),

(103,'3/10/2009'),(104,'4/10/2009'),(105,'5/10/2009')

EXEC dbo.UpdateTransactionDates @TableParam = @NewTableParam

--Select from the table to verify the change

SELECT * FROM TransLog

Note: The storedprocedure_script.sql is the insert procedure that passes the table-valued parameter with the matthewharrisapplication. Book3.csv, which you can download, is an example comma-delimited file of the values that will be passed with the stored procedure. Matthewharrisapplication is developed in C# and is also available for download. The application will work if the database is created on the local server. If not (if the database is created on a different instance of SQL Server than local server), SQLConnection will have to be changed.

Adding the Functionality to a .NET Application

It would be difficult to add thousands of records to the table type with the code above because I would have to use a T-SQL INSERT statement to insert each of the records. The easiest way that I’ve found to insert numerous rows into the table type is through a .NET application. At first, I thought using the .NET application would be difficult, but as I learned and as you’ll see, it involves few steps and very few lines of C# code (I used the 3.5 Framework, which isn’t necessary but is recommended.) I’ll create an application that parses the data from a .csv file into a DataGridView, sets the DataGridView as a DataTable, and passes the DataTable as the table parameter.

I then call the stored procedure to update the records. Note: I’ve updated up to about 10,000 records. Although the process is a little slow, it eliminates the time I would spend on the task, which is more valuable to the company.

Accessing Table-Valued Parameters Using a .NET Application

Before you proceed to Listing 4, a .Net application that accesses table-valued parameters, you create a new Windows Form C# Application and drag a DataGridView onto the form (an OpenfileDialog), along with two buttons (Browse and Update) and a label. Figure 2 shows the form created.

Listing 4: Code to Access Table-Valued Parameters

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

# BEGIN CALLOUT A

using System.Data.SqlClient;

using System.Text.RegularExpressions;

using System.IO;

# END CALLOUT A

namespace matthewharrisapplication

\\{

    public partial class Form1 : Form

    \\{

        public Form1()

        \\{

            InitializeComponent();

        \\}

# BEGIN CALLOUT B

        private DataTable dt;

        private void Form1_Load(object sender, EventArgs e)

        \\{

            dt = new DataTable();

            dt.Columns.Add("TransID", typeof(string));

            dt.Columns.Add("NewDate", typeof(string));

            dataGridView1.DataSource = dt.DefaultView;

        \\}

# END CALLOUT B

# BEGIN CALLOUT C

        public List parseCSV(string path)

        \\{

            List parsedData = new List();

            try

            \\{

                using (StreamReader readFile = new StreamReader(path))

                \\{

                    string line;

                    string\\[\\] row;

                    while ((line = readFile.ReadLine()) != null)

                    \\{

                        row = line.Split(',');

                        parsedData.Add(row);

                    \\}

                \\}

            \\}

            catch (Exception e)

            \\{

                MessageBox.Show(e.Message);

            \\}

            return parsedData;

        \\}

# END CALLOUT C

# BEGIN CALLOUT D

        private void button1_Click(object sender, EventArgs e)

        \\{

            this.openFileDialog1.Filter = "csv files (*.csv)|*.csv|txt files (*.txt)|*.txt|All files (*.*)|*.*";

            this.openFileDialog1.InitialDirectory = "c:\\temp";

            this.openFileDialog1.Multiselect = false;

            this.openFileDialog1.ShowDialog();

            if (this.openFileDialog1.FileName.Length > 0)

            \\{

                try

                \\{

                    this.label1.Text = this.openFileDialog1.FileName;

                    List testParse =

                  parseCSV(this.label1.Text);

                    foreach (string\\[\\] row in testParse)

                    \\{

                        dt.Rows.Add(row);

                    \\}

                    this.button1.Hide();

                \\}

                catch (Exception ex)

                \\{

                    MessageBox.Show(ex.Message, "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);

                \\}

            \\}

        \\}

# END CALLOUT D

# BEGIN CALLOUT E

        private void button2_Click(object sender, EventArgs e)

        \\{

            System.Data.SqlClient.SqlConnectionStringBuilder sb =

    new SqlConnectionStringBuilder

    ("server=(local);database=MovieRentalDB;Trusted_Connection=True");

            using (SqlConnection con = new SqlConnection(

                "server=(local);database=MovieRentalDB;Trusted_Connection=True"))

            \\{

                con.Open();

                using (SqlCommand com = new SqlCommand(

                    "dbo.UpdateTransactionDates", con))

                \\{

                    com.CommandType = CommandType.StoredProcedure;

                    com.Parameters.AddWithValue("TableParam", dt);

                    com.ExecuteNonQuery();

                \\}

            \\}

        \\}

    \\}

\\}

# END CALLOUT E

As you can see in Listing 4, using a .NET application makes it simple to access table-valued parameters. Callouts A through E highlight the steps that show you how to access table-valued parameters. (If you’re new to application development, the callout steps will help get you started.)

The code at callout A in Listing 4 adds three assembly references. The code at callout B declares the DataTable and sets the DataGridView as this DataTable. In the namespace of your application, create a private datatable. The code at callout C creates a parseCSV as a public list.

The code at callout D adds functionality to the Browse button. Clicking Browse lets the user search for the .csv file with the records that need to be updated and add those records to the DataGridView. This segment of code calls parseCSV. The code at callout E adds functionality to the Update button. I want to create a SQL connection, call the stored procedure, and pass the DataGridView as the table parameter.

The following brief procedure will help you test functionality. First, click Browse to find the .csv file, as shown in Figure 3. Second, confirm that the text in DataGridView is correct, as Figure 4 shows.

Click Update (at this point, you can add some error handling). Finally, use the following code to verify that the values have been updated in the database.

--Select all from updated table

SELECT *

FROM TransLog

As Figure 5 shows, the values have been updated. As you can see, it’s quite straightforward to use a .Net application with table-valued parameters to update multiple field values in thousands of files.

Summary: Using Table-Valued Parameters for Updating

Before I used the user-defined table type, the stored procedure I relied on involved a cursor loop that took too long to execute, caused numerous deadlocks, and had a negative effect on database performance. With SQL Server 2008’s new user-defined table type, you no longer have to spend hours trying to figure out ways to update thousands of records. You can also see how easy it is to add this functionality to an application; doing so lets others update the transactions themselves, rather than sending a request to the DBA. The user-defined table type lets you pass a table as a parameter to a stored procedure to update records. Using it is simple, secure, and doesn’t negatively affect the performance of the database.

Creating and Using Table-Valued Parameters in T-SQL

The ddl_script.sql in Listing 1 sets up the test database MovieRentalDB with a table and the table type. Additionally, it populates the table with values.

--Create new table type to use as a table-value parameter

CREATE TYPE dbo.ChangeTransDate AS TABLE(

            TransID varchar(36) NOT NULL,

            NewDate datetime NOT NULL)

Adding the Functionality to a .NET Application

Accessing Table-Valued Parameters Using a .NET Application

The following brief procedure will help you test functionality. First, click Browse to find the .csv file, as shown in Figure 3.

Second, confirm that the text in DataGridView is correct, as Figure 4 shows.

Click Update (at this point, you can add some error handling). Finally, use the following code to verify that the values have been updated in the database.

--Select all from updated table

SELECT *

FROM TransLog

As Figure 5 shows, the values have been updated.

As you can see, it’s quite straightforward to use a .Net application with table-valued parameters to update multiple field values in thousands of files.

Comments

Plain text