Combining SqlConnection and SqlCommand in One Class in C#

• Download database (RAR) - 1.9 KB
• Download database (zip) - 1.9 KB

Introduction

To access stored procedure in the database one way is to instantiate SqlConnection, set the connectionString and Open/Close the connection, instantiate SqlCommand and set the properties like CommandType, CommandMode and add parameters.

Problem Statement

I found this way too long, so I created a class that can combine the two classes and get the RETURN value from the stored procedure.

Using the Code

Solution

To used this class, kindly download the attached file and put the class file to your Visual Studio C# class library project.

To get the database class; import the CustomDatabase namespace at the top of your code.

using CustomDatabase;

In this class, there are useful methods in executing SqlCommand.

1. ExecuteCommandScalar()
2. ExecuteCommandNonQuery(out int nRowsAffected)
3. ExecuteCommandReader(ref DataTable oTable)
4. ExecuteCommandText(string Query)
5. FetchDataTable(string Query)
6. FetchDataSet(string Query)
7. FetchString(string Query, string Column)

ExecuteCommandScalar()

This method is useful when you are executing stored produre and get only the integer return value from the stored procedure.

ExecuteCommandNonQuery(out int NumberRowsAffected)

Execute the Save or Update command in stored procedure and get the number of rows affected in the database.

ExecuteCommandReader(ref DataTable oTable)

Execute Select command in the stored procedure and get the data that filled in the DataTable ref parameter.

ExecuteCommandText(string Query)

Execute command from the parameter string Query.

FetchDataTable(string Query)

Execute command from the parameter string Query and return data filled in DataTable.

FetchDataSet(string Query)

Execute command from the parameter string Query and return data filled in DataSet.

FetchString(string Query, string column)

Execute command from the parameter string Query and column and return string.

To use the above methods, please see sample code below that uses the ExecuteCommandReader(ref DataTable oTable).

 private int ManageCredential(ref string Token)
    {
        DataTable oTable = new DataTable();
        int ReturnVal;
        using(Database db = new Database(connection_string))
        {
            try
            {
                db.Open();
                db.CommandText = "ManageCredential";
                db.AddParameter("@login_name", _username, DbType.String);
                db.AddParameter("@password", _password, DbType.String);                   
                ReturnVal = db.ExecuteCommandReader(ref oTable);
                if (ReturnVal == 1)
                {
                    if (oTable.Rows.Count > 0)
                    {
                        DataRow oRow = oTable.Rows[0];
                        Token = oRow["session_token"].ToString();
                    }
                }
            }
            finally
            {
                db.Close();
            }
            return ReturnVal;
        }
    }

Sample Stored Procedure Code

   ALTER PROCEDURE [dbo].[ManageCredential]
    @login_name VARCHAR(100),
    @password VARCHAR(100) =  NULL
  
AS
BEGIN
        IF EXISTS(SELECT * FROM tbl_name  WHERE login_name COLLATE Latin1_General_CS_AS = @login_name 
       AND password COLLATE Latin1_General_CS_AS = @password)
            BEGIN  
             SELECT session_token FROM tbl_name   WHERE login_name COLLATE Latin1_General_CS_AS = 
             @login_name
                RETURN 1;
            END
        ELSE
            BEGIN
                RETURN 0;
            END
END

Code Explanation

Put the connection string to the database constructor:

using(Database db = new Database(ConfigurationManager.ConnectionStrings["con"].ConnectionString){}

Open the SqlConnection and put the Stored procedure name in the CommandText property:

  db.Open(); // Open the Connection
  db.CommandText = "ManageCredential"; 

Add the stored procedure parameter:

• First parameter is string datatype, the name of the parameter
• Second parameter is object datatype, the value of the parameter
• Third parameter is the DbType, the parameter that uses DbType enum

 db.AddParameter("@login_name", _username, DbType.String); 

Transaction

In database class, there is also a method for Transaction that handles in saving/updating multiple tables.

1. BeginTransaction()
2. CommitTransaction()
3. RollbackTransaction()

 private void CreateToken()
    {       
        using (Database db = new Database(connection_string))
        {
            try
            {
                db.Open();
                db.BeginTransaction();
                db.CommandText = @"UPDATE Table1 SET session_token = @session_token WHERE
                login_name = @login_name";
                db.AddParameter("@login_name", _username, DbType.String);
                db.AddParameter("@session_token", _session_token, DbType.String);
                db.ExecuteCommandNonQuery(CommandTypeEnum.Text);
                db.CommandText = @"UPDATE Table2 SET session_token = @session_token WHERE 
                login_name = @login_name";
                db.AddParameter("@login_name", _username, DbType.String);
                db.AddParameter("@session_token", _session_token, DbType.String);
                db.ExecuteCommandNonQuery(CommandTypeEnum.Text);
                db.CommitTransaction();
            }
            catch
            {
                db.RollbackTransaction();
            }
        }
    }