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