/// <summary> /// Bulks the load. /// </summary> /// <param name="tableName">Name of the table.</param> /// <param name="dt">Data table with values.</param> /// <param name="colMapping">The column mapping with insert table.</param> public void bulkLoad(string tableName, DataTable dt, Dictionary <string, string> colMapping) { new TraceLogger().Log("Method Start", tableName); DbConnection con = GetDatabase().CreateConnection(); try { SqlBulkCopy bulkCopy = new SqlBulkCopy((SqlConnection)con, SqlBulkCopyOptions.KeepIdentity, null); foreach (var item in colMapping) { bulkCopy.ColumnMappings.Add(item.Key, item.Value); } bulkCopy.DestinationTableName = tableName; con.Open(); bulkCopy.WriteToServer(dt); new TraceLogger().Log("Method Finish"); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } } finally { con.Close(); } }
/// <summary> /// This is private helper method /// </summary> /// <param name="SPName"> Name of the Store procedure</param> /// <param name="parameterValues">parameter values for input of store procedure</param> /// <returns>DbCommand</returns> private DbCommand GetCmd(Database db, string SPName, params object[] parameterValues) { try { DbCommand cmd = null; if (parameterValues == null) { cmd = db.GetStoredProcCommand(SPName); } else { cmd = db.GetStoredProcCommand(SPName, parameterValues); } return(cmd); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(null); } }
/// <summary> /// /// </summary> /// <param name="SPName"></param> /// <param name="recordcount"></param> /// <param name="parameterValues"></param> /// <returns></returns> public IDataReader ExecuteReader(string SPName, out Int64 recordcount, params object[] parameterValues) { new TraceLogger().Log("Method Start", SPName, parameterValues); recordcount = 0; IDataReader IdataReader; try { Database db = GetDatabase(); DbCommand cmd = GetCmd(db, SPName, parameterValues); IdataReader = db.ExecuteReader(cmd); recordcount = Convert.ToInt64(cmd.Parameters["@Return_Value"].Value); new TraceLogger().Log("Method Finish"); return(IdataReader); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(null); } }
/// <summary> /// This is private helper method /// </summary> /// <param name="SPName"> Name of the SP</param> /// <param name="parameterValues">parameter values in order of creation</param> /// <returns>DbCommand</returns> private static DbCommand GetCmd(Database db, string SPName, params object[] parameterValues) { try { DbCommand cmd = null; if (parameterValues == null) { cmd = db.GetStoredProcCommand(SPName); } else { cmd = db.GetStoredProcCommand(SPName, parameterValues); } cmd.CommandTimeout = Convert.ToInt32(string.IsNullOrEmpty(_CommandTimeOut) ? "30" : _CommandTimeOut); return(cmd); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(null); } }
/// <summary> /// Executes the non query for delete the image - input parameter is image doc id /// </summary> /// <param name="imagedocid">The image doc id.</param> /// <returns></returns> public static void DeleteImage(Int64 imagedocid) { try { Database db = GetDatabase(); db.ExecuteNonQuery(GetCmd(db, "CUS_DELETEIMAGE", imagedocid)); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } } }
/// <summary> /// This method is used in GetCmdParameters method, send the command from GetCmdParameters /// </summary> /// <param name="cmd"></param> /// <returns></returns> private int ExecuteNonQuery(Database db, DbCommand cmd) { try { return(db.ExecuteNonQuery(cmd)); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(-999); } }
/// <summary> /// Gets the image. /// </summary> /// <param name="imagedocid">The image doc id for input of Store procedure.</param> /// <returns> /// data reader with image /// </returns> public static IDataReader GetImages(Int64 imagedocid) { try { Database db = GetDatabase(); return(db.ExecuteReader(GetCmd(db, "CUS_GETIMAGE", imagedocid))); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(null); } }
/// <summary> /// This is private helper method /// </summary> /// <param name="SPName"> Name of the Store procedure</param> /// <param name="parameterValues">parameter values for input of store procedure</param> /// <returns>DbCommand</returns> private static DbCommand GetCmd(Database db, string SPName, params object[] parameterValues) { try { DbCommand cmd = null; if (parameterValues == null) { cmd = db.GetStoredProcCommand(SPName); } else { cmd = db.GetStoredProcCommand(SPName, parameterValues); foreach (SqlParameter parameter in cmd.Parameters) { if (parameter.SqlDbType == SqlDbType.Structured) { string name = parameter.TypeName; int index = name.IndexOf("."); if (index == -1) { continue; } name = name.Substring(index + 1); if (name.Contains(".")) { parameter.TypeName = name; } } } } cmd.CommandTimeout = Convert.ToInt32(string.IsNullOrEmpty(_CommandTimeOut) ? "30" : _CommandTimeOut); return(cmd); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(null); } }
/// <summary> /// This method gets all stored procedure parameter and fill in the Command as out parameter /// </summary> /// <param name="SPName"> Name of the SP</param> /// <returns>DbCommand</returns> private DbCommand GetCmdParameters(string SPName) { try { Database db = GetDatabase(); DbCommand cmd = GetCmd(db, SPName); db.DiscoverParameters(cmd); return(cmd); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(null); } }
/// <summary> /// Executes the non query. /// </summary> /// <param name="SPName">Name of the SP</param> /// <param name="parameterValues">parameter values in order of creation</param> /// <returns></returns> public int ExecuteNonQuery(string SPName, params object[] parameterValues) { try { new TraceLogger().Log("Method Start", SPName, parameterValues); Database db = GetDatabase(); int result = db.ExecuteNonQuery(GetCmd(db, SPName, parameterValues)); new TraceLogger().Log("Method Finish"); return(result); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(-999); } }
/// <summary> /// Executes the reader. /// </summary> /// <param name="SPName">Name of the SP</param> /// <param name="parameterValues">parameter values in order of creation</param> /// <returns></returns> public static IDataReader ExecuteReader(string SPName, params object[] parameterValues) { try { new TraceLogger().Log("Method Start", SPName, parameterValues); Database db = GetDatabase(); IDataReader result = db.ExecuteReader(GetCmd(db, SPName, parameterValues)); new TraceLogger().Log("Method Finish"); return(result); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(null); } }
/// <summary> /// Executes the command text. /// </summary> /// <param name="commandText">The command text.</param> /// <returns></returns> public IDataReader ExecuteCommandText(string commandText) { try { new TraceLogger().Log("Method Start", commandText); Database db = GetDatabase(); IDataReader _reader = db.ExecuteReader(CommandType.Text, commandText); new TraceLogger().Log("Method Finish"); return(_reader); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw; } return(null); } }
/// <summary> /// Puts the image. /// </summary> /// <param name="frontimage">The front image.</param> /// <param name="rearimage">The rear image.</param> /// <returns></returns> public static Int64 PutImage(byte[] frontimage, byte[] rearimage) { try { Database db = GetDatabase(); DbCommand cmd = null; cmd = GetCmd(db, "CUS_PUTIMAGE", frontimage, rearimage); db.ExecuteNonQuery(cmd); return(Convert.ToInt64(cmd.Parameters["@RETURN_VALUE"].Value)); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(-999); } }
public static int Checkimageexist(Int64 imagedocid) { try { Database db = GetDatabase(); DbCommand cmd = null; cmd = GetCmd(db, "CUS_CHECKIMAGEEXIST", imagedocid); db.ExecuteNonQuery(cmd); return(Convert.ToInt32(cmd.Parameters["@RETURN_VALUE"].Value)); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(-999); } }
/// <summary> /// Gets the data table with image. /// </summary> /// <param name="ImageDocId">The image doc id for input of Store procedure.</param> /// <returns>data table with image</returns> public static DataTable GetImage(Int64 ImageDocId) { try { Database db = GetDatabase(); DbCommand cmd = GetCmd(db, "CUS_GETIMAGE", ImageDocId); DataSet ds = db.ExecuteDataSet(cmd); DataTable dt = ds.Tables[0]; return(dt); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(null); } }
/// <summary> /// /// </summary> /// <param name="sql">Full Query String</param> /// <returns></returns> public DataSet ExecuteDataSet(string sql) { try { new TraceLogger().Log("Method Start", sql); Database db = GetDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql); DataSet ds = db.ExecuteDataSet(cmd); new TraceLogger().Log("Method Finish"); return(ds); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(null); } }
/// <summary> /// /// </summary> /// <param name="ConnectionString">DataBase Connection String</param> /// <param name="sql">Full Query String</param> /// <returns></returns> public static DataTable ExecuteSQLDataTable(string ConnectionString, string sql) { try { new TraceLogger().Log("Method Start", sql); Database db = GetSQLDatabase(ConnectionString); DbCommand cmd = db.GetSqlStringCommand(sql); DataSet ds = db.ExecuteDataSet(cmd); DataTable dt = ds.Tables[0]; new TraceLogger().Log("Method Finish"); return(dt); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(null); } }
/// <summary> /// This method used where output parameters are required. /// </summary> /// <param name="SPName"> Name of the SP</param> /// <param name="parameterValues">parameter values in order of creation</param> /// <returns></returns> public DbCommand ExecuteNonQueryCMD(string SPName, params object[] parameterValues) { try { new TraceLogger().Log("Method Start", SPName, parameterValues); Database db = GetDatabase(); DbCommand cmd = null; cmd = GetCmd(db, SPName, parameterValues); db.ExecuteNonQuery(cmd); new TraceLogger().Log("Method Finish"); return(cmd); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(null); } }
/// <summary> /// Gets the data table. /// </summary> /// <param name="spName">Name of the store procedure.</param> /// <param name="parameters">The parameters of store procedure</param> /// <returns></returns> public DataTable GetDataTable(string SPName, params object[] parameterValues) { try { new TraceLogger().Log("Method Start", SPName, parameterValues); Database db = GetDatabase(); DbCommand cmd = GetCmd(db, SPName, parameterValues); DataSet ds = db.ExecuteDataSet(cmd); DataTable dt = ds.Tables[0]; new TraceLogger().Log("Method Finish"); return(dt); } catch (Exception ex) { bool rethrow = DataAccessExceptionHandler.HandleException(ref ex); if (rethrow) { throw ex; } return(null); } }