Data Access Layer to execute Stored Procedures

This article explains about executing a single stored procedure or batch of stored procedures from the application layer. As the Data access layer completely decoupled from the Application layer we just need to change the Application layer in case of any change in the underlying database schema.

 

This article explains primarily about calling stored procedures which does not return any result set. This can be extended to handle those stored procedures also.

 

Data access layer

 

This data access layer contains the following key elements.

• ParamData Structure
• StoredProcedure class
• StoredProcedureCollection class
• Execute class

ParamData structure will contain parameter name, parameter value and data type.

struct ParamData {
    public string pName, pValue;
    public SqlDbType pDataType;
    public ParamData(string pName, SqlDbType pDataType, string pValue) {
        this.pName = pName;
        this.pDataType = pDataType;
        this.pValue = pValue;
    }
}

Stored Procedure class which will have methods to SetParam and Getparam to set and get the parameter list. This will be added to array list which will contain ParamData structures.

public void SetParam(string pName, SqlDbType pDataType, string pValue) {
    ParamData pData = new ParamData(pName, pDataType, pValue);
    // adding to array list sParams.
    sParams.Add(pData);
}

Similarly to get the parameter list GetParams method was defined which will return array list of ParamData structures.

public ArrayList GetParams() {
    if (!(sParams == null)) {
        return sParams;
    } else {
        return null;
    }
}

StoredProcedureCollection class contains the collection of stored procedure classes. This class has ADD and Remove methods to add the StroredProcedure class and remove it when it is not needed.

public void add(StoredProcedure value) {
    List.Add(value);
}
public void Remove(int index) {
    if (index > Count - 1 || index < 0) {
        Console.WriteLine("No data to remove");
    } else {
        List.RemoveAt(index);
    }
}

This will also have an ITEM method to get the StoredProcedure from the List.

public StoredProcedure Item(int Index) {
    return (StoredProcedure) List[Index];
}

Execute class has a static method ExecuteSps to execute the stored procedures from the collection.

public static bool ExecuteSps(StoredProcedureCollection spCollection, SqlConnection Connection) {
    try {
        foreach(StoredProcedure spData in spCollection) {
            SqlCommand cmd = new SqlCommand();
            int i = 0;
            if (Connection.State != ConnectionState.Open)
                Connection.Open();
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = spData.ProcName;
            IEnumerator myEnumerator = spData.GetParams().GetEnumerator();
            while (myEnumerator.MoveNext()) {
                ParamData pData = (ParamData) myEnumerator.Current;
                cmd.Parameters.Add(pData.pName, pData.pDataType);
                cmd.Parameters[i].Value = pData.pValue;
                i = i + 1;
            }
            cmd.ExecuteNonQuery();
        }
        return true;
    } catch (Exception exc) {
        return false;
    }
}

Please find the code snippet in <<DataAccessLayer.ZIP>> file.

 

Application Layer

 

In the application layer, we have to add the reference of DataAccessLayer.dll. After adding this reference we can call the data access layer functions as per our need. The advantage here is we can dynamically add/remove the parameters as and whenever there is change stored procedure definition.

private void button1_Click(object sender, System.EventArgs e) {
    SqlConnection connection = new SqlConnection();
    //change this connect string as per your environment
    string connectString = "Persist Security Info=False;Integrated Security=SSPI;database=DB1;server=Server2;Connect Timeout=60";
    connection.ConnectionString = connectString;
    if (connection.State != ConnectionState.Open)
        connection.Open();
    DataAccessLayer.StoredProcedureCollection spCollection = new DataAccessLayer.StoredProcedureCollection();
    DataAccessLayer.StoredProcedure spData = new DataAccessLayer.StoredProcedure();
    spData.ProcName = txtSpName.Text;
    spData.SetParam(txtParam1.Text, SqlDbType.VarChar, txtParamValue1.Text);
    spData.SetParam(txtParam2.Text, SqlDbType.VarChar, txtParamValue2.Text);
    spCollection.add(spData);
    if (DataAccessLayer.Execute.ExecuteSps(spCollection, connection))
        MessageBox.Show("Successfully executed");

    catch (Exception exc) {
        return false;
    }
}

Please find the code snippet for using the data access layer from the <<CallingAPP.zip>> file.