/// <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)
{
DataTraceLogger.Log("Method Start bulkLoad", 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);
DataTraceLogger.Log("Method Finish bulkLoad");
}
catch (Exception ex)
{
MTSExceptionHandler.HandleException(ref ex);
}
finally
{
con.Close();
}
}
/// <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
{
DataTraceLogger.Log("Method Start ExecuteNonQuery", SPName, parameterValues);
Database db = GetDatabase();
int result = db.ExecuteNonQuery(GetCmd(db, SPName, parameterValues));
DataTraceLogger.Log("Method Finish ExecuteNonQuery");
return(result);
}
catch (Exception ex)
{
MTSExceptionHandler.HandleException(ref ex);
return(-999);
}
}
/// <summary>
/// Executes the scalar.
/// </summary>
/// <param name="SPName">Name of the SP</param>
/// <param name="parameterValues">parameter values in order of creation</param>
/// <returns></returns>
public object ExecuteScalar(string SPName, params object[] parameterValues)
{
try
{
DataTraceLogger.Log("Method Start ExecuteScalar", SPName, parameterValues);
Database db = GetDatabase();
object result = db.ExecuteScalar(GetCmd(db, SPName, parameterValues));
DataTraceLogger.Log("Method Finish ExecuteScalar");
return(result);
}
catch (Exception ex)
{
MTSExceptionHandler.HandleException(ref ex);
return(null);
}
}
/// <summary>
/// Executes the command text.
/// </summary>
/// <param name="commandText">The command text.</param>
/// <returns></returns>
public IDataReader ExecuteCommandText(string commandText)
{
try
{
DataTraceLogger.Log("Method Start ExecuteCommandText", commandText);
Database db = GetDatabase();
IDataReader _reader = db.ExecuteReader(CommandType.Text, commandText);
DataTraceLogger.Log("Method Finish ExecuteCommandText");
return(_reader);
}
catch (Exception ex)
{
MTSExceptionHandler.HandleException(ref ex);
return(null);
}
}
/// <summary>
/// </summary>
/// <param name="sql">Full Query String</param>
/// <returns></returns>
public static DataSet ExecuteSQLDataSet(string sql)
{
try
{
DataTraceLogger.Log("Method Start ExecuteSQLDataSet", sql);
Database db = GetDatabase();
DbCommand cmd = db.GetSqlStringCommand(sql);
DataSet ds = db.ExecuteDataSet(cmd);
DataTraceLogger.Log("Method Finish ExecuteSQLDataSet");
return(ds);
}
catch (Exception ex)
{
MTSExceptionHandler.HandleException(ref 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
{
DataTraceLogger.Log("Method Start ExecuteNonQueryCMD", SPName, parameterValues);
Database db = GetDatabase();
DbCommand cmd = null;
cmd = GetCmd(db, SPName, parameterValues);
db.ExecuteNonQuery(cmd);
DataTraceLogger.Log("Method Finish ExecuteNonQueryCMD");
return(cmd);
}
catch (Exception ex)
{
MTSExceptionHandler.HandleException(ref 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
{
DataTraceLogger.Log("Method Start GetDataTable", SPName, parameterValues);
Database db = GetDatabase();
DbCommand cmd = GetCmd(db, SPName, parameterValues);
DataSet ds = db.ExecuteDataSet(cmd);
DataTable dt = ds.Tables[0];
DataTraceLogger.Log("Method Finish GetDataTable");
return(dt);
}
catch (Exception ex)
{
MTSExceptionHandler.HandleException(ref 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
{
DataTraceLogger.Log("Method Start ExecuteSQLDataTable", sql);
Database db = GetSQLDatabase(ConnectionString);
DbCommand cmd = db.GetSqlStringCommand(sql);
DataSet ds = db.ExecuteDataSet(cmd);
DataTable dt = ds.Tables[0];
DataTraceLogger.Log("Method Finish ExecuteSQLDataTable");
return(dt);
}
catch (Exception ex)
{
MTSExceptionHandler.HandleException(ref 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)
{
DataTraceLogger.Log("Method Start ExecuteReader", 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);
DataTraceLogger.Log("Method Finish DataTraceLogger");
return(IdataReader);
}
catch (Exception ex)
{
MTSExceptionHandler.HandleException(ref ex);
return(null);
}
}
