public void WriteSql(DataTable table, string tableName, SqlConnectionMode connectionMode)
{
Open(connectionMode);
if (Connection == null)
{
return;
}
if (Connection.State != ConnectionState.Open)
{
return;
}
var rowArray = table.Select();
using (var bulkCopy = new SqlBulkCopy(Connection))
{
bulkCopy.DestinationTableName = tableName;
foreach (var c in table.Columns.OfType <DataColumn>())
{
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName));
}
bulkCopy.WriteToServer(rowArray);
}
Close();
}
private void Open(SqlConnectionMode connectionMode)
{
try
{
ConnectionMode = connectionMode;
//var connect= new StackExchange.Profiling.Data.ProfiledDbConnection(new SqlConnection(ConnectionString), MiniProfiler.Current);
Connection = new SqlConnection(ConnectionString);
// Connection = connect.InnerConnection as SqlConnection;
Connection.Open();
}
catch (DataException ex)
{
Close();
}
}
public int ExecuteNonQuery(string query, SqlConnectionMode connectionMode)
{
switch (connectionMode)
{
case SqlConnectionMode.DEFAULT:
Open(SqlConnectionMode.DEFAULT);
break;
default:
Open(SqlConnectionMode.DEFAULT);
break;
}
Open(connectionMode);
if (Connection == null)
{
return(-1);
}
if (Connection.State != ConnectionState.Open)
{
return(-1);
}
Command = new SqlCommand(query, Connection)
{
CommandType = CommandType.Text
};
int returnValue = 0;
try
{
returnValue = Command.ExecuteNonQuery();
}
catch (Exception ex)
{
//Helpers.LogHelper.Insert($"SQL:{query}", ex);
//LogHelper.Error(query + " exception " + ex);
}
Command.Dispose();
Close();
return(returnValue);
}
public DataTable ExecuteQuery(string query, SqlConnectionMode model)
{
//switch (ConnectionMode)
//{
// case SqlConnectionMode.BIGPARA:
// Open(SqlConnectionMode.BIGPARA);
// break;
// case SqlConnectionMode.DBMATRIKS:
// Open(SqlConnectionMode.DBMATRIKS);
// break;
// default:
// Open(SqlConnectionMode.BIGPARA);
// break;
//}
var sqlDataAdapter = new SqlDataAdapter(query, ConnectionString);
var dt = new DataTable();
sqlDataAdapter.Fill(dt);
sqlDataAdapter.Dispose();
Close();
return(dt);
}
public void ExecuteBulkInsert(DataTable _table, SqlConnectionMode connectionMode)
{
Open(connectionMode);
try
{
using (SqlBulkCopy s = new SqlBulkCopy(Connection))
{
s.DestinationTableName = _table.TableName;
foreach (var column in _table.Columns)
{
s.ColumnMappings.Add(column.ToString(), column.ToString());
}
s.WriteToServer(_table);
}
}
catch (Exception ex)
{
//LogHelper.Error(ex);
}
Close();
}
public DataSet ExecuteProcedureDataSets(string procedureName, IEnumerable <DbQueryParameter> parameters, SqlConnectionMode connectionMode)
{
Open(connectionMode);
procedureName = procedureName.Trim();
if (Connection == null)
{
return(null);
}
if (Connection.State != ConnectionState.Open)
{
return(null);
}
Command = new SqlCommand(procedureName, Connection)
{
CommandType = CommandType.StoredProcedure
};
if (parameters != null)
{
Command.Parameters.Clear();
foreach (var dbParameter in parameters)
{
var parameter = new SqlParameter
{
ParameterName = "@" + dbParameter.Name,
Direction = dbParameter.Direction
};
parameter.Value = dbParameter.Value;
Command.Parameters.Add(parameter);
}
}
var da = new SqlDataAdapter();
var dataSet = new DataSet();
try
{
da.SelectCommand = Command;
da.Fill(dataSet);
}
catch (Exception x)
{
throw new Exception();
}
finally
{
Command.Dispose();
da.Dispose();
Close();
}
return(dataSet);
}
public List <T> ExecuteProcedureWithDataAdapter <T>(string procedureName, IEnumerable <DbQueryParameter> parameters, SqlConnectionMode connectionMode) where T : class
{
List <T> returnObject = null;
Open(connectionMode);
if (Connection == null)
{
return(null);
}
if (Connection.State != ConnectionState.Open)
{
return(null);
}
Command = new SqlCommand(procedureName, Connection)
{
CommandType = CommandType.StoredProcedure
};
// pass stored procedure parameters to command
if (parameters != null)
{
Command.Parameters.Clear();
foreach (var dbParameter in parameters)
{
var parameter = new SqlParameter
{
ParameterName = "@" + dbParameter.Name,
Direction = dbParameter.Direction
};
//if (dbParameter.Value is DateTime)
// parameter.Value = Convert.ToDateTime(dbParameter.Value).ToOADate();
//else
parameter.Value = dbParameter.Value;
Command.Parameters.Add(parameter);
}
}
var da = new SqlDataAdapter();
var dt = new DataTable();
try
{
da.SelectCommand = Command;
da.Fill(dt);
returnObject = ConvertToList <T>(dt);
}
catch (Exception x)
{
//Helpers.LogHelper.Insert($"SQL:{procedureName}", x);
//LogHelper.Error(procedureName + " exception " + x);
}
finally
{
Command.Dispose();
da.Dispose();
}
return(returnObject);
}
public int ExecuteScalar(string procedureName, List <DbQueryParameter> parameters, SqlConnectionMode connectionMode)
{
Open(connectionMode);
int returnValue = 0;
try
{
returnValue = (int)ExecuteProcedure(procedureName, ExecuteType.ExecuteScalar, parameters);
}
catch (Exception ex)
{
//LogHelper.Error(procedureName + " parameters:" + GetErrorMessage(parameters) + " exception " + ex);
}
UpdateOutParameters();
Close();
return(returnValue);
}
// executes list query stored procedure and maps result generic list of objects
public List <T> ExecuteList <T>(string procedureName, List <DbQueryParameter> parameters, SqlConnectionMode connectionMode) where T : new()
{
var listObjects = new List <T>();
Open(connectionMode);
try
{
var reader = (IDataReader)ExecuteProcedure(procedureName, ExecuteType.ExecuteReader, parameters);
while (reader.Read())
{
var listItemObject = new T();
for (var i = 0; i < reader.FieldCount; i++)
{
try
{
SetObjectValue(reader.GetName(i), reader.GetValue(i), ref listItemObject);
}
catch (Exception ex)
{
var name = reader.GetName(i);
//Helpers.LogHelper.Insert($"SQL:{procedureName} parameter {name}", ex);
//LogHelper.Error(procedureName + " parameters:" + GetErrorMessage(parameters) + " result :" + reader.GetName(i) + " exception " + ex);
}
}
listObjects.Add(listItemObject);
}
reader.Close();
UpdateOutParameters();
}
catch (Exception ex)
{
//Helpers.LogHelper.Insert($"SQL:{procedureName}", ex);
//LogHelper.Error(procedureName + " exception " + ex);
}
Close();
return(listObjects);
}
// executes list query stored procedure without parameters
public List <T> ExecuteList <T>(string procedureName, SqlConnectionMode connectionMode) where T : new()
{
return(ExecuteList <T>(procedureName, null, connectionMode));
}