SQL Server and MySQL Database Models in a C#.NET Application - Part 2

Properties

#region "Properties"
public DataSet RecordList
{
  get { return dsList; }
  set { dsList = value; }
}
#endregion "Properties"

Used to set the database data type object

public object DBDataType(string sDataType)
{
  Object oType= null;
  switch (sDataType)
  {
    case "VarChar":
      #if MS_SQLSERVER
        oType = SqlDbType.VarChar;
      #else
        oType = MySqlDbType.VarChar;
      #endif
      break;
  };
  return oType;
}

Constructor used to initialized the database connection and pass the stored procedure name

public StoredProcedure(string sProcedureName)
{
  #if MS_SQLSERVER
    sqlConn = new SqlConnection(CONNECTION_STRING);
  #else
    sqlConn = new MySqlConnection(CONNECTION_STRING);
  #endif
  sqlCmd.Connection = sqlConn;
  sqlCmd.CommandType = CommandType.StoredProcedure;
  sqlCmd.CommandText = sProcedureName;
}

If MS SQL Server is defined, then SqlConnection method is enabled.

Used to pass input parameter name and value to stored procedure

public void AddIn(string sParameter, Object oValue)
{
  sqlCmd.Parameters.AddWithValue(sParameter, oValue);
  sqlCmd.Parameters[sParameter].Direction = ParameterDirection.Input;
}

Used to pass output parameter name to stored procedure

public void AddOut(string sParameter, string sDataType)
{
  Object oValue = DBDataType(sDataType);
  #if MS_SQLSERVER
    sqlCmd.Parameters.Add(new SqlParameter(sParameter, (SqlDbType)oValue, 1000));
  #else
    sqlCmd.Parameters.Add(new MySqlParameter(sParameter, (MySqlDbType)oValue, 1000));
  #endif
  sqlCmd.Parameters[sParameter].Direction = ParameterDirection.Output;
}

If MS SQL Server is defined, then SqlParameter method is enabled.

Retrieve Output/Return value from the database

public Object GetOut(string sParameter)
{
  if (sqlCmd.Parameters[sParameter].Value != null)
    return sqlCmd.Parameters[sParameter].Value;
  return null;
}

Used to Select/List the records

public DataSet List()
{
  try
  {
    sqlConn.Open();
    sqlDAList.SelectCommand = sqlCmd;
    sqlDAList.Fill(RecordList, "RecordList");
    sqlCmd.Dispose();
  }
  catch (Exception excep)
  {
    throw new Exception(excep.Message);
  }
  finally
  {
    sqlConn.Close();
  }
  return RecordList;
}

Used to insert, modify and delete the record

public void IUD()
{
  try
  {
    sqlConn.Open();
    sqlCmd.ExecuteNonQuery();
    sqlCmd.Dispose();
  }
  catch (Exception excep)
  {
    throw new Exception(excep.Message);
  }
  finally
  {
    sqlConn.Close();
  }
}

Section 2 - Call the stored procedure functions

Retrieve the list of records

StoredProcedure spList = new StoredProcedure("spTypeList");
AdminList = spList.List();

Retrieve the list of records, passed one argument

StoredProcedure spList = new StoredProcedure("spTypeView");
spList.AddIn("@iRID", iRecordID);
AdminList = spList.List();

Insert, modify or delete the record, pass input and output parameters

StoredProcedure spIUD = new StoredProcedure("spTypeIUD");
spIUD.AddIn("@iRID", iRecordID);
spIUD.AddIn("@sDML", DML);
spIUD.AddIn("@sDesc", Desc);
spIUD.AddOut("@sResult", "VarChar");
spIUD.IUD();
Result = (string)spIUD.GetOut("@sResult");

That's it. Hope it will help you to write smart way. Let me know if you any thoughts and share in comments box.

SQL Server and MySQL Database Models in a C#.NET Application - Part 1