tag:blogger.com,1999:blog-5127798429426738073.comments2023-07-07T04:46:16.191-07:00Sql Code MasterzPratap Singhhttp://www.blogger.com/profile/00310023181491568166noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-5127798429426738073.post-57006954269380668162011-07-07T23:38:09.033-07:002011-07-07T23:38:09.033-07:00public DataSet ProjectLoad(int projectID)
...public DataSet ProjectLoad(int projectID)<br /> {<br /> const string ProcName = "ProjectLoad";<br /> try<br /> {<br /> logger.Debug(ProcName + EnterProcedure);<br /> CreateSqlCommand(ProjectLoadProcedure);<br /> AddParam("@projectId", projectID, 4);<br /> ExecuteSQL();<br /> return GetDataset();<br /> }<br /> catch (Exception ex)<br /> {<br /> logger.Error(ProcName + ErrorOccured, ex);<br /> throw;<br /> }<br /> finally<br /> {<br /> logger.Debug(ProcName + ExitProcedure);<br /> }<br /> }Ram Kumarhttps://www.blogger.com/profile/08188652439984113848noreply@blogger.comtag:blogger.com,1999:blog-5127798429426738073.post-72301967552747145032011-07-07T23:37:25.298-07:002011-07-07T23:37:25.298-07:00public DataSet GetFolderByStatus(int statusID, int...public DataSet GetFolderByStatus(int statusID, int updateStatus, int projectUpdateStatus, string machineName)<br /> {<br /> const string ProcName = "GetProjectByStatus";<br /> try<br /> {<br /> logger.Debug(ProcName + EnterProcedure);<br /> CreateSqlCommand(GetFolderByStatusProcedure);<br /> AddParam("@StatusID", statusID, 4);<br /> AddParam("@UpdateStatusID",updateStatus,4);<br /> AddParam("@ProjectUpdateStatus", projectUpdateStatus, 4);<br /> AddParam("@MachineName", machineName, 100);<br /> ExecuteSQL();<br /> return GetDataset();<br /> }<br /> catch (Exception ex)<br /> {<br /> logger.Error(ProcName + ErrorOccured, ex);<br /> throw;<br /> }<br /> finally<br /> {<br /> logger.Debug(ProcName + ExitProcedure);<br /> }<br /> }Ram Kumarhttps://www.blogger.com/profile/08188652439984113848noreply@blogger.comtag:blogger.com,1999:blog-5127798429426738073.post-27183817550091933642011-07-07T23:28:47.749-07:002011-07-07T23:28:47.749-07:003.4 public void AddParam(string paramName, DateT...3.4 public void AddParam(string paramName, DateTime? paramValue, int size, bool treatForNull)<br /> {<br /> const string ProcName = "AddParam";<br /> try<br /> {<br /> object parameterValue;<br /> if (paramValue == null)<br /> {<br /> parameterValue = DBNull.Value;<br /> }<br /> else<br /> {<br /> parameterValue = paramValue;<br /> }<br /> sqlCmd.Parameters.Add(CreateParameter(paramName, SqlDbType.DateTime,<br /> paramValue, ParameterDirection.Input, size, treatForNull));<br /> }<br /> catch (Exception ex)<br /> {<br /> logger.Error(ProcName + ErrorOccured, ex);<br /> throw;<br /> }<br /><br /> finally<br /> {<br /> logger.Debug(ProcName + ExitProcedure);<br /> }<br /> }<br /> public void AddOutputParameter(string paramName, int size)<br /> {<br /> const string ProcName = "AddOutputParameter";<br /> try<br /> {<br /> sqlCmd.Parameters.Add(CreateParameter(paramName, SqlDbType.VarChar, null, ParameterDirection.Output, size));<br /> }<br /> catch (Exception ex)<br /> {<br /> logger.Error(ProcName + ErrorOccured, ex);<br /> throw;<br /> }<br /><br /> finally<br /> {<br /> logger.Debug(ProcName + ExitProcedure);<br /> }<br /> }Ram Kumarhttps://www.blogger.com/profile/08188652439984113848noreply@blogger.comtag:blogger.com,1999:blog-5127798429426738073.post-34758309913896327072011-07-07T23:27:42.572-07:002011-07-07T23:27:42.572-07:003.3 public void AddParam(string paramName, strin...3.3 public void AddParam(string paramName, string paramValue, int size)<br /> {<br /> const string ProcName = "AddParam";<br /> try<br /> {<br /> logger.Debug(ProcName + EnterProcedure);<br /> sqlCmd.Parameters.Add(CreateParameter(paramName, SqlDbType.VarChar, paramValue, ParameterDirection.Input, size));<br /> }<br /> catch (Exception ex)<br /> {<br /> logger.Error(ProcName + ErrorOccured, ex);<br /> throw;<br /> }<br /><br /> finally<br /> {<br /> logger.Debug(ProcName + ExitProcedure);<br /> }<br /> }<br /> public void AddParam(string paramName, int paramValue, int size)<br /> {<br /> const string ProcName = "AddParam";<br /> try<br /> {<br /> sqlCmd.Parameters.Add(CreateParameter(paramName, SqlDbType.Int, paramValue, ParameterDirection.Input, size));<br /> }<br /> catch (Exception ex)<br /> {<br /> logger.Error(ProcName + ErrorOccured, ex);<br /> throw;<br /> }<br /><br /> finally<br /> {<br /> logger.Debug(ProcName + ExitProcedure);<br /> }<br /><br /> }Ram Kumarhttps://www.blogger.com/profile/08188652439984113848noreply@blogger.comtag:blogger.com,1999:blog-5127798429426738073.post-12227268104092310702011-07-07T23:27:05.873-07:002011-07-07T23:27:05.873-07:003.2 public void AddParam(string paramName, SqlDbTy...3.2 public void AddParam(string paramName, SqlDbType paramType, Object paramValue, ParameterDirection direction, int size)<br /> {<br /> const string ProcName = "AddParam";<br /> try<br /> {<br /> logger.Debug(ProcName + EnterProcedure);<br /> sqlCmd.Parameters.Add(CreateParameter(paramName, paramType, paramValue, direction, size));<br /> }<br /> catch (Exception ex)<br /> {<br /> logger.Error(ProcName + ErrorOccured, ex);<br /> throw;<br /> }<br /><br /> finally<br /> {<br /> logger.Debug(ProcName + ExitProcedure);<br /> }<br /> }Ram Kumarhttps://www.blogger.com/profile/08188652439984113848noreply@blogger.comtag:blogger.com,1999:blog-5127798429426738073.post-69587670019522251482011-07-07T23:22:51.435-07:002011-07-07T23:22:51.435-07:003.... public void CloseConnection()
{
...3.... public void CloseConnection()<br /> {<br /> const string ProcName = "CloseConnection";<br /> try<br /> {<br /> logger.Debug(ProcName + EnterProcedure);<br /> if (conn != null && conn.State == ConnectionState.Open)<br /> conn.Close();<br /> }<br /> catch (Exception ex)<br /> {<br /> logger.Error(ProcName + ErrorOccured, ex);<br /> throw;<br /> }<br /> finally<br /> {<br /> logger.Debug(ProcName + ExitProcedure);<br /> }<br /> }<br /><br /> public void CloseConnection(SqlConnection sqlConn)<br /> {<br /> const string ProcName = "CloseConnection() ";<br /> try<br /> {<br /> logger.Debug(ProcName + EnterProcedure);<br /> if (sqlConn != null && sqlConn.State == ConnectionState.Open)<br /> sqlConn.Close();<br /> }<br /> catch (Exception ex)<br /> {<br /> logger.Error(ProcName + ErrorOccured, ex);<br /> throw;<br /> }<br /> finally<br /> {<br /> logger.Debug(ProcName + ExitProcedure);<br /> }<br /> }Ram Kumarhttps://www.blogger.com/profile/08188652439984113848noreply@blogger.comtag:blogger.com,1999:blog-5127798429426738073.post-18640523738479406892011-07-07T23:22:21.265-07:002011-07-07T23:22:21.265-07:003. public static void SetDatabaseConnectionStrin...3. public static void SetDatabaseConnectionString(string connectionString)<br /> {<br /> DBConnectionManager.connection = connectionString;<br /> }<br /><br /> public void OpenConnection()<br /> {<br /> const string ProcName = "OpenConnection";<br /> try<br /> {<br /> logger.Debug(ProcName + EnterProcedure);<br /> if (conn == null || conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken<br /> || DateTime.Now.Subtract(lastExecutionTime).TotalMinutes > 30)<br /> {<br /> //open new connection <br /> conn = new SqlConnection(connection);<br /> conn.Open();<br /> <br /> }<br /><br /> lastExecutionTime = DateTime.Now;<br /> }<br /> catch (Exception ex)<br /> {<br /> logger.Error(ProcName + ErrorOccured, ex);<br /> throw;<br /> }<br /> finally<br /> {<br /> logger.Debug(ProcName + ExitProcedure);<br /> }<br /><br /> }Ram Kumarhttps://www.blogger.com/profile/08188652439984113848noreply@blogger.comtag:blogger.com,1999:blog-5127798429426738073.post-24277901875564828442011-07-07T23:20:59.035-07:002011-07-07T23:20:59.035-07:00#region Private
///
/// Object f...#region Private<br /><br /> /// <br /> /// Object for Logging<br /> /// <br /> private static ILog logger = LogManager.GetLogger(typeof(DBConnectionManager));<br /> /// <br /> /// Specifies the time when query was executed last time.<br /> /// <br /> private static DateTime 1.lastExecutionTime = DateTime.Now;<br /> /// <br /> /// Represent a command to database.<br /> /// <br /> private SqlCommand sqlCmd;<br /> /// <br /> /// Represent a connection to database.<br /> /// <br /> private SqlConnection conn;<br /> /// <br /> /// Represent a connection string.<br /> /// <br /> private static string connection;<br /> /// <br /> /// Represent a time out for connection.<br /> /// <br /> private int timeOut;<br /> /// <br /> /// Whether component is disposed or not<br /> /// <br /> private bool disposed = false;<br /><br /> #endregion<br />2. # region Properties<br /> /// <br /> /// Get Command Object<br /> /// <br /> public SqlCommand SqlCmd<br /> {<br /> get<br /> {<br /> return sqlCmd;<br /> }<br /> }<br /> /// <br /> /// Connection object to the database<br /> /// <br /> public SqlConnection Conn<br /> {<br /> get<br /> {<br /> return conn;<br /> }<br /> set<br /> {<br /> conn = value;<br /> <br /><br /> }<br /> }<br /> /// <br /> /// get or set the database command time out value<br /> /// <br /> public int TimeOut<br /> {<br /> get<br /> {<br /> return timeOut;<br /> }<br /> set<br /> {<br /> timeOut = value;<br /><br /> }<br /> }<br /> /// <br /> /// get/set Database Connection String<br /> /// <br /> public string Connection<br /> {<br /> get<br /> {<br /> return connection;<br /> }<br /> set<br /> {<br /> connection = value;<br /> }<br /> }<br /> #endregionRam Kumarhttps://www.blogger.com/profile/08188652439984113848noreply@blogger.comtag:blogger.com,1999:blog-5127798429426738073.post-27977375799699510972011-07-07T09:47:04.527-07:002011-07-07T09:47:04.527-07:00This is nice articleThis is nice articlePratap Singhhttps://www.blogger.com/profile/00310023181491568166noreply@blogger.com