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