/// <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);
}
}