A generic way to pass a strongly typed table to SQL Server’s stored procedure using NPoco
-
Wednesday, May 30, 2018
-
By Jovica Turcinovic
How can I pass a strongly typed list of entities as a table type parameter to SQL Server’s stored procedure?
For this purpose, I want to use:
- C#
- MicroORM .NPoco
- SQL Server 2016
I’d like to reach a solution which is as generic as possible.
So, let's first check Why would we need this?
Why
- I want to import the data periodically to the DB table. The data keeps coming continuously (every minute). The target DB table contains a huge amount of data. The source data may contain the already existing data (key point :) )
- I want to retrieve the updated data on the source side comparing to the target side (the DB table)
Important Note:
I don’t recommend using stored procedures from your business logic whenever you are not facing the performance issues. Do let me know in the comments section if there are some other reasons for using stored procedures.
General advice is to write the business logic in a proper C# class library. It allows you to fully test your business logic and not to distribute the business logic to the DB engine level.
How / Discussion
Solution 1:
- Fetch the data from the source in the strongly typed collection
- Fetch the data from the target in the strongly typed collection
- Compare the data and perform the desired actions
Good:
Business logic will be in the right place
Unit tests can be written
Bad:
Performance issues may occur (especially for a large amount of data)
Out of memory exception (no need to catch the whole corpus of data from the database in order to do the in memory comparison)
Solution 2:
- Fetch the data from the source in the strongly typed collection
- Pass the retrieved data to the stored procedure responsible for the data manipulation
- Fetch the result from the stored procedure in the strongly typed manner
Good:
Performances shall be dramatically improved
The CPU/Memory consumption will be optimized
Data manipulation will be distributed to the DB engine and dedicated server
Bad:
Business logic will be distributed to the stored procedure (if we keep data manipulation in the stored procedure simple, and completely understand what is going on, based on the stored procedure name - we are still good)
Apart from unit tests, in order to cover all written codes, we need additional integration tests
If we check the list carefully, we shall see that the most important restriction is performances. We definitely have to deal with the Bad list from the Solution 2.
Implementation
In order to demonstrate the solution we are going to create a User table which will consist of certain personal related data.
On the other hand, we will simulate the source data in the C# code.
Once the data is prepared, we will pass the data to the stored procedure which has to return records which have been updated in the meantime.
DB Preparation
CREATE TABLE [dbo].[TB_User] (
[Id] [int] IDENTITY(1,1) NOT NULL,
[First_Name] [nvarchar](30) NOT NULL,
[Second_Name] [nvarchar](50) NULL,
[User_Info] [text] NULL,
[Birth_Date] [datetime] NOT NULL,
CONSTRAINT [PK_TB_User] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[TB_Worker] (
[Id] [int] IDENTITY(1,1) NOT NULL,
[First_Name_E] [varchar](30) NULL,
[Last_Name_E] [nvarchar](50) NULL,
[Employed_From] [datetime] NULL,
[User_Name] [varchar](20) NULL,
[Birth_Date] [datetime] NULL,
[More_Info] [text] NULL,
CONSTRAINT [PK_TB_Worker] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
For the testing purposes, let's populate some data:
INSERT INTO TB_User([First_Name], [Second_Name], [User_Info], [Birth_Date])
VALUES
('Nikola', 'Jokic', 'Denver''s Center', '1995-02-19')
,('Novak', 'Djokovic', 'Tennis Player', '1987-05-22')
In order to fetch the updated users based on the passed table typed parameter, let's create the table type and stored procedure:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_updated_users]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_updated_users]
GO
IF EXISTS (SELECT *
FROM [sys].[table_types]
WHERE name = N'udt_tb_user')
DROP TYPE udt_tb_user
GO
IF NOT EXISTS (SELECT *
FROM [sys].[table_types]
WHERE name = N'udt_tb_user')
CREATE TYPE [dbo].udt_tb_user AS TABLE(
[Id] [INT] NOT NULL,
[First_Name] [VARCHAR](30) NOT NULL,
[Second_Name] [VARCHAR](50) NOT NULL,
[User_Info] [TEXT] NULL,
[Birth_Date] [DATETIME] NOT NULL
);
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_updated_users]
@list [dbo].udt_tb_user READONLY
AS
BEGIN
SELECT
ExistingData.[Id],
ExistingData.[First_Name],
ExistingData.[Second_Name],
ExistingData.[User_Info],
ExistingData.[Birth_Date]
FROM
[dbo].[TB_User] AS ExistingData
LEFT JOIN
@list AS PassedData
ON
ExistingData.First_Name = PassedData.First_Name AND
ExistingData.Second_Name = PassedData.Second_Name
WHERE
CAST(ExistingData.User_Info AS NVARCHAR(MAX)) <> CAST(PassedData.User_Info AS NVARCHAR(MAX)) OR
ExistingData.Birth_Date <> PassedData.Birth_Date
END
As you can see, the tabled type has a structure identical to the original DB table. This leads to the conclusion that the table types needed for this kind of purposes may be auto-generated (an idea for certain future improvements).
As I have already mentioned at the beginning, I am going to use the NPoco library as the Micro ORM.
For further reference regarding NPoco please check the official GitHub repository.
Based on requirements, we would like to have something as follows:
// NPoco standard database object.
// DBConnection -> the name of the connection string from the config file.
IDatabase db = new Database("DbConnection");
// SqlStoredProcedureHelper represents the helper class for creating the proper SQL Server SQL command
// based on the passed entity type (in our case User).
var storedProcedureHelper = new SqlStoredProcedureHelper(db, typeof(User));
// Simulate the source data by population the c# collection.
var sourceUsers = new List<IDbEntityBase>
{
new User
{
FirstName = "Nikola",
SecondName = "Jokic",
UserInfo = "Denver's Key Player",
BirthDate = new DateTime(1995, 2, 19)
},
new User
{
FirstName = "Novak",
SecondName = "Djokovic",
UserInfo = "Tennis Player",
BirthDate = new DateTime(1987, 5, 22)
}
};
// Call the standard Fetch command which will execute the passed SQL script command and populate
// the collection of the strongly typed results.
// The GetSqlCommand accepts two parameters: the stored procedure name and the list of the arguments.
// The used stored procedure contains only one parameter which is typed table.
// We pass the strongly typed collection of the User objects.
var updatedUsers =
db.Fetch<User>(storedProcedureHelper.GetSqlCommand("sp_get_updated_users", new object[] {sourceUsers}));
// List out the list of the retrieved data.
foreach (var updatedUser in updatedUsers)
{
System.Console.WriteLine(
$"The user: {updatedUser.FirstName} {updatedUser.SecondName} has been updated.");
}
System.Console.ReadKey();
The expected result based on the data should be:
Let’s check the definition of the Entity:
[TableName("TB_User")]
[PrimaryKey("Id", AutoIncrement = true)]
[DataContract]
public class User
{
[Column("Id")]
[DataMember]
[PrimaryKeyColumn]
public int Id { get; set; }
[Column("First_Name")]
[DataMember]
public string FirstName { get; set; }
[Column("Second_Name")]
[DataMember]
public string SecondName { get; set; }
[Column("User_Info")]
[DataMember]
public string UserInfo { get; set; }
[Column("Birth_Date")]
[DataMember]
public DateTime BirthDate { get; set; }
}
Dive into the GetSqlCommand function:
public Sql GetSqlCommand(string storedProcedureName, object[] arguments)
{
return new Sql().Append(
$"EXEC {storedProcedureName} {GetSqlServerParametersQueryStringBySpNameAndArgs(storedProcedureName, arguments)}",
ConvertSqlServerArgumentBySpNameAndArgs(storedProcedureName, arguments));
}
Two functions prepare the necessary stored procedure call query strings and the arguments:
private string GetSqlServerParametersQueryStringBySpNameAndArgs(string storedProcedureName,
ICollection<object> arguments)
{
var cache = MemoryCache.Default;
var spQueryStringCacheKey = string.Concat("cc_", storedProcedureName);
if (cache[spQueryStringCacheKey] is string retVal) return retVal;
// check if the stored procedure exist.
if (!IsSysObjectExist(storedProcedureName))
{
throw new StoredProcedureNotExistException(
$"The stored procedure with name: {storedProcedureName} doesn't exist.");
}
var queryBuilder = new StringBuilder();
var parameters = GetStoredProcedureParametersByStoredProcedureName(storedProcedureName);
// check if the number of the passed arguments matches the real number of the stored procedure parameters.
if (parameters.Select(i => i.ParameterMode.Equals("IN")).Count() != arguments.Count)
throw new InsufficientNumberOfParametersException(
"The passed number of arguments doesn't meet the real number of the stored procedure's parameters");
if (arguments.Count == 0)
throw new ArgumentException("Value cannot be an empty collection.", nameof(arguments));
var iterator = 0;
foreach (var storedProcedureParameter in parameters)
{
if (!storedProcedureParameter.ParameterMode.Equals("IN")) continue;
if (iterator != 0)
{
queryBuilder.Append(", ");
}
queryBuilder.Append("@");
queryBuilder.Append(storedProcedureParameter.ParameterName);
queryBuilder.Append(" = @");
queryBuilder.Append(iterator++);
}
retVal = queryBuilder.ToString();
// Put the cache to be valid in next one hour.
cache.Set(spQueryStringCacheKey, retVal, DateTime.Now.AddMinutes(1));
return retVal;
}
private object[] ConvertSqlServerArgumentBySpNameAndArgs(string storedProcedureName, IList<object> arguments)
{
var argumentsRetVal = new object[arguments.Count];
var parameters = GetStoredProcedureParametersByStoredProcedureName(storedProcedureName);
// check if the number of the passed arguments matches the real number of the stored procedure parameters.
if (parameters.Select(i => i.ParameterMode.Equals("IN")).Count() != arguments.Count)
throw new InsufficientNumberOfParametersException(
"The passed number of arguments doesn't meet the real number of the stored procedure's parameters");
var iterator = 0;
foreach (var storedProcedureParameter in parameters)
{
if (!storedProcedureParameter.ParameterMode.Equals("IN")) continue;
if (storedProcedureParameter.DataType.ToLower() == "table type")
{
var data = CreateSqlArgument(arguments[iterator], _isIdbEntityCast);
var param = new SqlParameter
{
ParameterName = storedProcedureParameter.ParameterName,
SqlDbType = SqlDbType.Structured,
Value = data,
TypeName = storedProcedureParameter.UserDefinedTypeName
};
argumentsRetVal[iterator] = param;
}
else
{
argumentsRetVal[iterator] = arguments[iterator];
}
iterator++;
}
return argumentsRetVal;
}
The complete SqlStoredProcedureHelper class is available here:
public class SqlStoredProcedureHelper
{
private readonly IDatabase _db;
private readonly DataTable _data;
private readonly bool _isIdbEntityCast;
public SqlStoredProcedureHelper(IDatabase database, Type type, bool isIDbEntityCast = true)
{
_db = database;
_data = new DataTable(type.Name);
_isIdbEntityCast = isIDbEntityCast;
var properties = type.GetProperties();
foreach (var propertyInfo in properties)
{
if (propertyInfo.GetSetMethod(true) == null ||
Attribute.IsDefined(propertyInfo, typeof(ResultColumnAttribute)) ||
Attribute.IsDefined(propertyInfo, typeof(IgnoreAttribute)) ||
!Attribute.IsDefined(propertyInfo, typeof(ColumnAttribute))) continue;
var dbColumnAttribute =
(ColumnAttribute)Attribute.GetCustomAttribute(propertyInfo, typeof(ColumnAttribute));
_data.Columns.Add(dbColumnAttribute.Name, GetProperType(propertyInfo.PropertyType));
}
}
public Sql GetSqlCommand(string storedProcedureName, object[] arguments)
{
return new Sql().Append(
$"EXEC {storedProcedureName} {GetSqlServerParametersQueryStringBySpNameAndArgs(storedProcedureName, arguments)}",
ConvertSqlServerArgumentBySpNameAndArgs(storedProcedureName, arguments));
}
private List<SqlServerStoredProcedureParameter> GetStoredProcedureParametersByStoredProcedureName(string storedProcedureName)
{
var cache = MemoryCache.Default;
var spParametersCacheKey = string.Concat("pp_", storedProcedureName);
if (cache[spParametersCacheKey] is List<SqlServerStoredProcedureParameter> parameters) return parameters;
// as this NPoco call is going to change this property to 0.
const int saveOneTimeCommandTimeout = 500;
var sqlCommand = new Sql().Append(
@"SELECT * FROM information_schema.parameters WHERE specific_name = @0
ORDER BY ORDINAL_POSITION",
storedProcedureName);
parameters = _db.Fetch<SqlServerStoredProcedureParameter>(sqlCommand);
// Set back the saved value for the OneTimeCommandTimeout.
_db.OneTimeCommandTimeout = saveOneTimeCommandTimeout;
// Put the cache to be valid in next one hour.
cache.Set(spParametersCacheKey, parameters, DateTime.Now.AddHours(1));
return parameters;
}
private bool IsSysObjectExist(string sysObjectName)
{
if (string.IsNullOrEmpty(sysObjectName))
{
throw new ArgumentNullException();
}
var sqlCommand = new Sql().Append(
@"SELECT COUNT(*) FROM sysobjects WHERE name=@0", sysObjectName);
var numberOfItems = _db.ExecuteScalar<int>(sqlCommand);
return numberOfItems == 1;
}
private object[] ConvertSqlServerArgumentBySpNameAndArgs(string storedProcedureName, IList<object> arguments)
{
var argumentsRetVal = new object[arguments.Count];
var parameters = GetStoredProcedureParametersByStoredProcedureName(storedProcedureName);
// check if the number of the passed arguments matches the real number of the stored procedure parameters.
if (parameters.Select(i => i.ParameterMode.Equals("IN")).Count() != arguments.Count)
throw new InsufficientNumberOfParametersException(
"The passed number of arguments doesn't meet the real number of the stored procedure's parameters");
var iterator = 0;
foreach (var storedProcedureParameter in parameters)
{
if (!storedProcedureParameter.ParameterMode.Equals("IN")) continue;
if (storedProcedureParameter.DataType.ToLower() == "table type")
{
var data = CreateSqlArgument(arguments[iterator], _isIdbEntityCast);
var param = new SqlParameter
{
ParameterName = storedProcedureParameter.ParameterName,
SqlDbType = SqlDbType.Structured,
Value = data,
TypeName = storedProcedureParameter.UserDefinedTypeName
};
argumentsRetVal[iterator] = param;
}
else
{
argumentsRetVal[iterator] = arguments[iterator];
}
iterator++;
}
return argumentsRetVal;
}
private string GetSqlServerParametersQueryStringBySpNameAndArgs(string storedProcedureName,
ICollection<object> arguments)
{
var cache = MemoryCache.Default;
var spQueryStringCacheKey = string.Concat("cc_", storedProcedureName);
if (cache[spQueryStringCacheKey] is string retVal) return retVal;
// check if the stored procedure exist.
if (!IsSysObjectExist(storedProcedureName))
{
throw new StoredProcedureNotExistException(
$"The stored procedure with name: {storedProcedureName} doesn't exist.");
}
var queryBuilder = new StringBuilder();
var parameters = GetStoredProcedureParametersByStoredProcedureName(storedProcedureName);
// check if the number of the passed arguments matches the real number of the stored procedure parameters.
if (parameters.Select(i => i.ParameterMode.Equals("IN")).Count() != arguments.Count)
throw new InsufficientNumberOfParametersException(
"The passed number of arguments doesn't meet the real number of the stored procedure's parameters");
if (arguments.Count == 0)
throw new ArgumentException("Value cannot be an empty collection.", nameof(arguments));
var iterator = 0;
foreach (var storedProcedureParameter in parameters)
{
if (!storedProcedureParameter.ParameterMode.Equals("IN")) continue;
if (iterator != 0)
{
queryBuilder.Append(", ");
}
queryBuilder.Append("@");
queryBuilder.Append(storedProcedureParameter.ParameterName);
queryBuilder.Append(" = @");
queryBuilder.Append(iterator++);
}
retVal = queryBuilder.ToString();
// Put the cache to be valid in next one hour.
cache.Set(spQueryStringCacheKey, retVal, DateTime.Now.AddMinutes(1));
return retVal;
}
private object CreateSqlArgument(object argument, bool forceCast)
{
var retVal = _data;
if (argument == null) return retVal;
if (forceCast)
{
var stronglyItems = (List<IDbEntityBase>)argument;
if (stronglyItems.Count == 0) return retVal;
foreach (var item in stronglyItems)
{
var row = retVal.NewRow();
retVal.Rows.Add(GetRowByItem(row, item));
}
}
return retVal;
}
private static DataRow GetRowByItem(DataRow row, IDbEntityBase item)
{
#region [Plant Table Definition]
var type = item.GetType();
var properties = type.GetProperties();
foreach (var propertyInfo in properties)
{
if (propertyInfo.GetSetMethod(true) == null ||
Attribute.IsDefined(propertyInfo, typeof(ResultColumnAttribute)) ||
Attribute.IsDefined(propertyInfo, typeof(IgnoreAttribute)) ||
!Attribute.IsDefined(propertyInfo, typeof(ColumnAttribute))) continue;
var dbColumnAttribute =
(ColumnAttribute) Attribute.GetCustomAttribute(propertyInfo, typeof(ColumnAttribute));
var propValue = propertyInfo.GetValue(item, null);
row[dbColumnAttribute.Name] = propValue ?? DBNull.Value;
}
#endregion
return row;
}
private static Type GetProperType(Type type)
{
if (type == typeof(int?)) return typeof(int);
return type == typeof(BoolValue) ? typeof(int) : type;
}
}
Closing words
The implementation and the usage are straightforward. If you are stuck and keep getting into trouble do let me know in the comments section.
Thanks for reading and enjoy passing the strongly typed lists to the stored procedures in a quite a simple manner :)