Software help desk

Properties#region “Properties” public DataSet RecordList {   get { return dsList; }   set { dsList = value; } } #endregion “Properties” Used to set the database data type objectpublic 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 namepublic 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 procedurepublic 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 procedurepublic 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 databasepublic Object GetOut(string sParameter) {   if (sqlCmd.

Parameters[sParameter].value != null)     return sqlCmd.Parameters[sParameter].value;   return null; } Used to Select/List the recordspublic 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 recordpublic 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 recordsStoredProcedure spList = new StoredProcedure(“spTypeList”); AdminList = spList.List(); Retrieve the list of records, passed one argumentStoredProcedure spList = new StoredProcedure(“spTypeView”); spList.AddIn(“@iRID”, iRecordID); AdminList = spList.List(); Insert, modify or delete the record, pass input and output parametersStoredProcedure 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 Daynight: SQL Server and MySQL Database Models in a C#.NET Application – Part 2