serving the solutions day and night

Pages

Monday, August 23, 2010

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

No comments: