public void Delete <T>(string where = null, SqlTransaction transaction = null) where T : IModelBase
{
if (transaction != null)
{
var deleteQuery = ModelBaseHelper.CreateDeleteQuery <T>(where);
SqlCommand cmd = new SqlCommand()
{
Connection = transaction.Connection, CommandText = deleteQuery, Transaction = transaction
};
cmd.ExecuteNonQuery();
}
else
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
var deleteQuery = ModelBaseHelper.CreateDeleteQuery <T>(where);
SqlCommand cmd = new SqlCommand()
{
Connection = conn, CommandText = deleteQuery
};
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
/// <summary>
/// Bulkcopies the list of data into the database
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
public void BulkCopy <T>(List <T> data) where T : IModelBase, new()
{
var columns = new T().GetFields().ToDictionary(p => p.Key, q => q.Value.GetType());
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
DataTable table = new DataTable();
table.Columns.AddRange(columns.Select(c => new DataColumn(c.Key, c.Value)).ToArray());
SqlBulkCopy bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans)
{
DestinationTableName = ModelBaseHelper.GetTableName <T>()
};
foreach (var column in columns)
{
bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(column.Key, column.Key));
}
data.ForEach(row => table.Rows.Add(row.ToObjectArray()));
bulk.WriteToServer(table);
trans.Commit();
}
}
//public void InsertInto<T>(T item, bool clearTable = false) where T : IModelBase
//{
// InsertInto<T>(new[] { item }, clearTable);
//}
public IEnumerable <long> InsertInto <T>(IEnumerable <T> dataList, bool clearTable = false) where T : IModelBase
{
var result = new List <long>();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
if (clearTable)
{
SqlCommand cmd = new SqlCommand()
{
Connection = conn, CommandText = ModelBaseHelper.CreateDeleteQuery <T>()
};
var scalar = cmd.ExecuteScalar();
if (scalar != null)
{
result.Add((long)scalar);
}
}
var insertQuery = ModelBaseHelper.CreateInsertQuery <T>();
foreach (var combiRow in dataList)
{
SqlCommand cmd = new SqlCommand()
{
Connection = conn, CommandText = insertQuery
};
combiRow.AddAsParametersToSqlCommand(cmd);
var scalar = cmd.ExecuteScalar();
if (scalar != null)
{
result.Add((long)scalar);
}
}
conn.Close();
}
return(result);
}
public void Update <T>(IEnumerable <T> dataList, string where = null, string[] fields = null) where T : IModelBase
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
var updateQuery = ModelBaseHelper.CreateUpdateQuery <T>(fields, where);
foreach (var combiRow in dataList)
{
SqlCommand cmd = new SqlCommand()
{
Connection = conn, CommandText = updateQuery
};
combiRow.AddAsParametersToSqlCommand(cmd);
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
public void Create <T>() where T : IModelBase
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
var query = ModelBaseHelper.CreateTableExistsCode(ModelBaseHelper.GetSchema <T>(), ModelBaseHelper.GetTableName <T>());
query += "BEGIN\r\n";
query += ModelBaseHelper.CreateTableCommand <T>() + ")\r\n";
query += "END";
SqlCommand cmd = new SqlCommand()
{
Connection = conn, CommandText = query
};
cmd.ExecuteNonQuery();
conn.Close();
}
}
public T SelectSingle <T>(string where = null, string[] fields = null, bool distinct = false, int top = 0) where T : IModelBase, new()
{
T result = new T();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
var selectQuery = ModelBaseHelper.CreateSelectQuery <T>(fields, distinct, where, 1);
SqlCommand cmd = new SqlCommand()
{
Connection = conn, CommandText = selectQuery
};
var reader = cmd.ExecuteReader();
while (reader.Read())
{
result = ModelBaseHelper.ReadFromSqlReader <T>(reader, fields);
}
conn.Close();
}
return(result);
}