Blog Post

Using stored procedure to push multiple records at once

Tuesday, April 12, 2016 12:43 PM

While working on multiple projects I have noticed that many developers are not aware of the possibility of passing multiple records at once into stored procedure at once. Instead we create multiple calls and wrap it in for each loop. This way we achieve the goal but the redundancy of making the call many times to database. 

 

This of course does not mean we are bad developers but that there is a room for improvement.

 

I would like to share a another solution that can improve speed of your data access.

The usual solution

We define stored procedure as

 

CREATE PROCEDURE [dbo].updateClientRecord 
    @clientId INT,
    @key nvarchar(max),
@value nvarchar(max),
AS
    ** do your logic here to update client information
GO

 

Now we have multiple properties

  • ClientName
  • ClientSurname
  • ClientMiddleName

For each property we usually call the stored procedure executing call 3 times instead of once.

 

Now let me present another way

However, this is not to be used lightly as it can create simple problem more complex, so think about what are you trying to achieve.

1. Create a custom user type

Custom type is special object type. You can think about it as table variable that contains data you want to work with, there are some restrictions when using custom type.


Code example to create ‘user defined type’


CREATE TYPE [dbo].[CustomType] AS TABLE(
    [key] [nvarchar](max) NOT NULL,
    [value] [nvarchar](max) NOT NULL
)
GO

For more information about user type click on https://msdn.microsoft.com/en-GB/library/ms175007.aspx

2. Update stored procedure

We have our original stored procedure

CREATE PROCEDURE [dbo].updateClientRecord 
    @clientId INT,
    @key nvarchar(max),
@value nvarchar(max),
AS
    ** do your logic here to update client information
GO

 

Lets rewrite it to use our new custom type

 

CREATE PROCEDURE [dbo].updateClientRecord 
    @clientId INT,
    @customType CustomType READONLY
AS
    ** do your logic here
    Select * from @customType
GO

3. Code to execute sp (.net)

Now we have defined stored procedure and custom type lets write a code that can write stored procedure.

 

VB.NET

 

Try
Using command As New SqlCommand("dbo.updateClientRecord", _connection)
If _connection.State = ConnectionState.Closed Then _connection.Open()
  'create table
Dim dataTable As DataTable = New DataTable("customType")
Dim key As DataColumn = New DataColumn("key")
key.DataType = System.Type.GetType("System.String")
dataTable.Columns.Add(key)
Dim value As DataColumn = New DataColumn("value")
value.DataType = System.Type.GetType("System.String")
dataTable.Columns.Add(value)
   For Each CustomType As CustomType In specialCriteriaList
  Dim itemRow As DataRow
itemRow = dataTable.NewRow()
itemRow.Item("key") = CustomType.key
itemRow.Item("value") = CustomType.value
' add into table
dataTable.Rows.Add(itemRow)
Next

systemCommand.CommandType = CommandType.StoredProcedure
systemCommand.Parameters.AddWithValue("@clientId", userId)
systemCommand.Parameters.AddWithValue("@customType", dataTable)
  systemCommand.ExecuteNonQuery()
End Using
Catch Ex As SqlException
' handle exception from sql
Catch Ex As Exception
' handle generic exception
End Try

 

 

C# version of the command

 


try {
using (SqlCommand command = new SqlCommand("dbo.updateClientRecord", _connection)) {
if (_connection.State == ConnectionState.Closed)
_connection.Open();

//'create table
DataTable dataTable = new DataTable("customType");
DataColumn key = new DataColumn("key");
key.DataType = System.Type.GetType("System.String");
dataTable.Columns.Add(key);
DataColumn value = new DataColumn("value");
value.DataType = System.Type.GetType("System.String");
dataTable.Columns.Add(value);

foreach (CustomType CustomType in specialCriteriaList) {
DataRow itemRow = default(DataRow);
itemRow = dataTable.NewRow();
itemRow.Item("key") = CustomType.key;
itemRow.Item("value") = CustomType.value;
// add into table
dataTable.Rows.Add(itemRow);
}
systemCommand.CommandType = CommandType.StoredProcedure;
systemCommand.Parameters.AddWithValue("@clientId", userId);
systemCommand.Parameters.AddWithValue("@customType", dataTable);
systemCommand.ExecuteNonQuery();
}
} catch (SqlException Ex) {
// handle exception from sql
} catch (Exception Ex) {
// handle generic exception
}

 

Now we have done the hard work lets analyse the performance using sql profiler

 

 

4. SQL Profiler recorded sql

Here is example you will be able to find in sql profiler

declare @p2 dbo.CustomType
insert into @p2 values(N'ClientName',N'false')
insert into @p2 values(N'ClientSurname',N'true')
insert into @p2 values(N'ClientMiddleName',N'true')
exec dbo.updateClientRecord @clientId=123,@customType=@p2

 

 

If you have managed to read until now, I have a task for you.

If you have code where you update multiple properties, try this approach while recording time before implementation and after implementation.

Write a comment how successful you have been and if you need help.

SQL Injection

While working on this stored procedure I have been recently reminded of TOP ranking issue when testing pages. SQL Injection.

 

Make sure that all elements you are checked and be aware, exposing one incorrect sql can compromise the whole system not only one table.