[C#]The returned value of DataContext.ExecuteCommand when executing stored procedures.

In our project, SQL scripts(CRUD in specific) are executed using the class “DataContext” in combination with LinqToSQL,
and then of course, when the method “ExecuteCommand” is invoked, the returned value indicates the rows updated.

When the SQL command executed is simply just an INSERT/UPDATE/DELETE, the result is as obvious as ever.
But when it comes to stored procedures, things might not be that simple.

Stored procedures, needless to say, is a series of CRUD commands combined with certain logic.
But what we care about is whether it generated the expected results or not.
By knowing the return value while invoking stored procedures,
we can avoid bad practices like writing methods that execute stored procedures without even returning anything.
e.g.

dac.CallSomeStoredProcedure();

This code shows no sign of success or failure, thus impossible to catch implicit errors caused by erratic data.
So if there was something wrong with the data, nobody will ever know unless it has caused problems later on,
which might be…to late and have already caused a catastrophe.

In the following paragraphs, we will be discussing the returned value under different circumstances.
Now let us build up the required classes for our test project to run.

First of all, the Dao interface:

public interface IDao<T>
{
    /// <summary>
    /// Finds the row with the specified id
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    T FindById(string id);

    /// <summary>
    /// Find all rows
    /// </summary>
    /// <returns></returns>
    IList<T> FindAll();

    /// <summary>
    /// Insert a new record
    /// </summary>
    /// <param name="t"></param>
    /// <returns></returns>
    int Create(T t);

    /// <summary>
    /// Update an existing record
    /// </summary>
    /// <param name="t"></param>
    /// <returns></returns>
    int Update(T t);

    /// <summary>
    /// Execute INSERT/UPDATE/DELETE stored procedure
    /// </summary>
    /// <param name="spString"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    int ExecuteCudSp(string spString, params object[] parameters);

    /// <summary>
    /// Execute SELECT stored procedure
    /// </summary>
    /// <param name="spString"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    IList<T> ExecuteRSp(string spString, params object[] parameters);
}

Then we implement the interface with an abstract class so that common methods can be defined.

public abstract class BaseDao<T>: IDao<T>, IDisposable
{
    /// <summary>
    /// Created by : George Chou
    /// Create Date: 2017/05/26
    /// Description:
    /// 1. Constructor for this class.
    /// 2. Made protected for access control.
    /// </summary>
    protected BaseDao()
    {
        dc = new DataContext(GetConnectionString("TSQL2012Connection"));
    }

    //Protected so only subclasses can access it
    protected DataContext dc;

    public abstract T FindById(string key);
    public abstract IList<T> FindAll();
    public abstract int Create(T t);
    public abstract int Update(T t);
    public abstract int ExecuteCudSp(string spString, params object[] parameters);
    public abstract IList<T> ExecuteRSp(string spString, params object[] parameters);

    /// <summary>
    /// Created by : George Chou
    /// Create Date: 2017/06/20
    /// Description:
    /// Returns the connection string associated with the specified key.
    /// </summary>
    /// <param name="key"></param>
    /// <returns></returns>
    private static string GetConnectionString(string key)
    {
        return ConfigurationManager.ConnectionStrings[key].ConnectionString;
    }

    /// <summary>
    /// Created by : George Chou
    /// Create Date: 2017/06/20
    /// Description:
    /// 1.Disposes DataContext member.
    /// 2.Implemented for "using" block
    /// </summary>
    public void Dispose() {dc.Dispose();}

And finally, our non-abstract implementing class

public class ProductDao : BaseDao<Product>
{
    public override int ExecuteCudSp(string spString, params object[] parameters)
    {
        return dc.ExecuteCommand(spString, parameters);
    }

    public override IList<Product> ExecuteRSp(string spString, params object[] parameters)
    {
        return dc.ExecuteQuery<Product>(spString, parameters).ToList();
    }
    //elided...
}

The two methods “ExecuteCudSp” and “ExecuteRSp” will be the main dish of this course.

Now that we’ve created the classes required, we can continue to display the results of different types of stored procedures with the help of some unit tests. We will only be showing two examples so this article won’t become too lengthy.

1.SP that contains only select statements.
Here’s the sql script…

CREATE PROCEDURE USP_selectProduct
AS
BEGIN
	SELECT ProductName,
	       UnitPrice
	FROM   Production.Products
	WHERE  ProductID BETWEEN 20 AND 50
END
GO

And the unit test…

[TestMethod]
public void Test_SP_SelectOnly()
{
    using (TransactionScope scope = new TransactionScope())
    using (ProductDao productDao = new ProductDao())
    {
        //Arrange
        string sql = "EXEC USP_selectProduct";
        //Act
        int rows = productDao.ExecuteCudSp(sql);
        //Assert
        Assert.AreEqual(-1, rows);
    }
}

We expect the result to be -1, since no rows are returned, and the test passed.

2.SP that mixes inserts and updates.
Here’s the sql script…

CREATE PROCEDURE USP_insertMultipleAndUpdate
AS
BEGIN
	INSERT INTO Production.Products
	(
		ProductName,
		SupplierID,
		CategoryID,
		UnitPrice,
		Discontinued
	)
	VALUES
	(
		'AAA',
		8,
		3,
		55.12,
		0
	)

	UPDATE Production.Products
	SET    UnitPrice = 17.25 --16.25
	WHERE  ProductID = 50
END
GO

And here’s the unit test…

[TestMethod]
public void Test_SP_InsertAndUpdateMixed()
{
    using (TransactionScope scope = new TransactionScope())
    using (ProductDao productDao = new ProductDao())
    {
        //Arrange
        string sql = "EXEC USP_insertMultipleAndUpdate";
        //Act
        int rows = productDao.ExecuteCudSp(sql);
        //Assert
        Assert.AreEqual(2, rows);
    }
}

Since there’s one insert and one update, we expect the rows to be returned is 2, and the test passed.

The following is a summary table of the different combinations of stored procedure contents and results.

If you would like to have a test run on your own, this project can be cloned from gitlab,
Git Clone Url

To conclude, by knowing the returned value of stored procedures using DataContext.ExecuteCommand,
we could write better code with while dealing with transactions.

作者: George Chou

嗨,大家好,我是George Chou,一位半路出家的小Programmer。 對程式設計相當有興趣,尤其是Java,所以踏上了程式之路, 歡迎大家一同交流學習。

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *