static public DataTable ExecuteQuery(string connectionString, string sql) { Stopwatch tw = new Stopwatch(); tw.Start(); log.Info(string.Format("Setting up ...")); DataTable dataTable = null; try { using (SqlConnection connection = new SqlConnection(connectionString)) using (SqlCommand cmd = new SqlCommand(sql, connection)) { log.Info(string.Format("Opening Connection on '{0}' ...", HidePwd(connectionString))); connection.Open(); log.Info(string.Format("Query: {0}", sql)); log.Info(string.Format("Query execution starting ...")); using (var reader = cmd.ExecuteReader()) { dataTable = new DataTable(); dataTable.Load(reader); } log.Info(string.Format("Query executed! Result count: {0}", LibString.ItemsNumber(dataTable))); connection.Close(); log.Info("Connection Closed!"); } } catch (Exception ex) { log.Info("Excepion detected!"); log.Error(ex.Message); throw; } finally { tw.Stop(); log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed))); } return(dataTable); }
static public int DeleteOperation(string connectionString, string tabName, Dictionary <string, QueryCondition> conditions) { Stopwatch tw = new Stopwatch(); tw.Start(); int result = -1; try { string query = "DELETE FROM " + tabName + " WHERE " + string.Join(" ", conditions.Select(x => x.Value.Key + x.Value.Op + "@" + x.Key + " " + x.Value.Conj).ToArray()); Dictionary <string, object> pars = new Dictionary <string, object>(); foreach (KeyValuePair <string, QueryCondition> entry in conditions) { pars[entry.Key] = entry.Value.Value; } log.Info(string.Format("Query: {0}", query)); log.Info(string.Format("Params: {0}", string.Join("; ", pars.Select(x => x.Key + "=" + x.Value).ToArray()))); result = ExecuteNonQueryWithParams(connectionString, query, pars); log.Info(string.Format("Query Executed! Inserted {0} records!", result)); return(result); } catch (Exception ex) { string msg = "An Error occured! Exception detected!"; log.Info(msg); log.Error(msg + " " + ex.Message); throw; } finally { tw.Stop(); log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed))); } }
// GetAll public List <T> GetAll() { Stopwatch tw = new Stopwatch(); tw.Start(); log.Info(string.Format("Starting Retrieving procedure ...")); List <T> entities = null; try { string connectionString = this.DBConnectionString; string table = this.DBTabName; DataTable data = DBSQL.SelectOperation(connectionString, table, null); log.Info(string.Format("DBSQL Query Executed! Retrieved {0} record!", LibString.ItemsNumber(data))); if (data != null) { entities = MapperDataTableToEntities.Invoke(data); log.Info(string.Format("{0} Records mapped to {1}", LibString.ItemsNumber(entities), LibString.TypeName(entities))); } } catch (Exception ex) { string msg = "An Error occured! Exception detected!"; log.Info(msg); throw ex; } finally { tw.Stop(); log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed))); } return(entities); }
static public int ExecuteNonQuery(string connectionString, string sql) { Stopwatch tw = new Stopwatch(); tw.Start(); log.Info(string.Format("Setting up ...")); int result = -1; try { using (SqlConnection connection = new SqlConnection(connectionString)) using (SqlCommand cmd = new SqlCommand(sql, connection)) { log.Info(string.Format("Opening Connection on '{0}' ...", HidePwd(connectionString))); connection.Open(); log.Info(string.Format("Query: {0}", sql)); log.Info(string.Format("Query execution starting ...")); result = cmd.ExecuteNonQuery(); log.Info(string.Format("Query executed! Result count: {0}", result)); connection.Close(); log.Info("Connection Closed!"); } } catch (Exception ex) { log.Info("Excepion detected!"); log.Error(ex.Message); throw; } finally { tw.Stop(); log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed))); } return(result); }
// Delete by Entities public int Remove(List <T> items) { int result = 0; Stopwatch tw = new Stopwatch(); tw.Start(); log.Info(string.Format("Starting Deleting procedure ...")); if (items == null || items.Count == 0) { throw new ArgumentNullException("Unable to delete NULL entities!"); } string table = this.DBTabName; try { string connectionString = this.DBConnectionString; if (primaryKey == null) { throw new Exception(string.Format("No Primary Key has been defined for the entity '{0}'. Use a removing procedure based on conditions!", typeof(T).Name)); } List <Dictionary <string, object> > keys = new List <Dictionary <string, object> >(); foreach (T item in items) { Dictionary <string, object> key = new Dictionary <string, object>(); foreach (string k in this.primaryKey) { object v = item.GetType().GetProperty(k).GetValue(item, null); key.Add(k, v); } keys.Add(key); } // DELETE if (keys != null) { int i = 0; Dictionary <string, DBSQL.QueryCondition> conditions = new Dictionary <string, DBSQL.QueryCondition>(); foreach (Dictionary <string, object> key in keys) { foreach (KeyValuePair <string, object> kvPair in key) { int j = 0; bool hasNext = true; if (i == keys.Count - 1) { if (j == key.Count - 1) { hasNext = false; } } string condName = "cond" + i; DBSQL.QueryCondition condValue = new DBSQL.QueryCondition() { Key = kvPair.Key, Value = kvPair.Value, Op = DBSQL.Op.Equal, Conj = hasNext ? DBSQL.Conj.And : DBSQL.Conj.None, }; conditions.Add(condName, condValue); i++; j++; } } result = DBSQL.DeleteOperation(connectionString, table, conditions); } log.Info(string.Format("Deleted {0} records!", result)); } catch (Exception ex) { string msg = "An Error occured! Exception detected!"; log.Info(msg); throw ex; } finally { tw.Stop(); log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed))); } return(result); }
// Delete by Attributes public int RemoveByKeys(List <Dictionary <string, object> > keys) { int result = 0; Stopwatch tw = new Stopwatch(); tw.Start(); log.Info(string.Format("Starting Deleting procedure ...")); string table = this.DBTabName; try { string connectionString = this.DBConnectionString; // DELETE if (keys != null) { int i = 0; Dictionary <string, DBSQL.QueryCondition> conditions = new Dictionary <string, DBSQL.QueryCondition>(); foreach (Dictionary <string, object> key in keys) { foreach (KeyValuePair <string, object> kvPair in key) { int j = 0; bool hasNext = true; if (i == keys.Count - 1) { if (j == key.Count - 1) { hasNext = false; } } string condName = "cond" + i; DBSQL.QueryCondition condValue = new DBSQL.QueryCondition() { Key = kvPair.Key, Value = kvPair.Value, Op = DBSQL.Op.Equal, Conj = hasNext ? DBSQL.Conj.And : DBSQL.Conj.None, }; conditions.Add(condName, condValue); i++; j++; } } result = DBSQL.DeleteOperation(connectionString, table, conditions); } log.Info(string.Format("Deleted {0} records!", result)); } catch (Exception ex) { string msg = "An Error occured! Exception detected!"; log.Info(msg); throw ex; } finally { tw.Stop(); log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed))); } return(result); }
// Update public T Update(T item) { T written = default(T); Stopwatch tw = new Stopwatch(); tw.Start(); log.Info(string.Format("Starting Updating procedure ...")); if (item == null) { throw new ArgumentNullException("Unable to update NULL entity!"); } string table = this.DBTabName; try { string connectionString = this.DBConnectionString; if (primaryKey == null) { throw new Exception(string.Format("No Primary Key has been defined for the entity '{0}'. Use an updating procedure with declared conditions!", typeof(T).Name)); } int i = 0; bool hasNext = true; Dictionary <string, DBSQL.QueryCondition> conditions = new Dictionary <string, DBSQL.QueryCondition>(); foreach (string k in primaryKey) { object v = item.GetType().GetProperty(k).GetValue(item, null); if (i == primaryKey.Count - 1) { hasNext = false; } string condName = "cond" + i; DBSQL.QueryCondition condValue = new DBSQL.QueryCondition() { Key = k, Value = v, Op = DBSQL.Op.Equal, Conj = hasNext ? DBSQL.Conj.And : DBSQL.Conj.None, }; conditions.Add(condName, condValue); i++; } int result = DBSQL.UpdateOperation(connectionString, table, item, conditions, this.autoincrement); log.Info(string.Format("Updated {0} records!", result)); //Retrieve Item By Condition to give it back as result! } catch (Exception ex) { string msg = "An Error occured! Exception detected!"; log.Info(msg); throw ex; } finally { tw.Stop(); log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed))); } return(written); }
static public DataTable MultiInsertBackOperation <T>(string connectionString, string tabName, List <T> dataVOs, List <string> pk, List <string> autoincrement = null) { Stopwatch tw = new Stopwatch(); tw.Start(); if (autoincrement == null) { autoincrement = new List <string>() { } } ; DataTable data = null; try { List <Dictionary <string, object> > pars_ = new List <Dictionary <string, object> >(); List <Dictionary <string, object> > pars__ = new List <Dictionary <string, object> >(); Dictionary <string, object> into = new Dictionary <string, object>(); int i = 0; foreach (object dataVO in dataVOs) { Dictionary <string, object> pars = new Dictionary <string, object>(); foreach (var prop in dataVO.GetType().GetProperties()) { if (!autoincrement.Select(e => e.ToLower()).ToList().Contains(prop.Name.ToLower())) { string keyOfPar = string.Format("{0}{1}", prop.Name, i); pars[keyOfPar] = prop.GetValue(dataVO, null); if (i == 0) { into[prop.Name] = prop.GetValue(dataVO, null); } } } i++; pars__.Add(pars); } string query = "INSERT INTO " + tabName + " (" + string.Join(", ", into.Select(x => x.Key).ToArray()) + ") " + "OUTPUT " + string.Join(", ", pk.Select(p => "INSERTED." + p).ToArray()) + " VALUES "; into = null; Dictionary <string, object> parss = new Dictionary <string, object>(); int j = 1; foreach (Dictionary <string, object> pars in pars__) { query += "(" + string.Join(", ", pars.Select(x => "@" + x.Key).ToArray()) + ")"; parss = parss.Union(pars).ToDictionary(k => k.Key, v => v.Value); if (j < pars__.Count) { query += ", "; } j++; } using (SqlConnection connection = new SqlConnection(connectionString)) using (SqlCommand cmd = new SqlCommand(query, connection)) { foreach (KeyValuePair <string, object> entry in parss) { object dat = entry.Value != null ? entry.Value : DBNull.Value; cmd.Parameters.AddWithValue(entry.Key, dat); } log.Info(string.Format("Opening Connection on '{0}' ...", HidePwd(connectionString))); connection.Open(); log.Info(string.Format("Query: {0}", LibString.SQLCommand2String(cmd))); log.Info(string.Format("Query execution starting ...")); DataTable dataTable = new DataTable(); using (var reader = cmd.ExecuteReader()) { dataTable.Load(reader); } log.Info(string.Format("Query Insert executed! Result count: {0}", LibString.ItemsNumber(dataTable))); int t = 0; foreach (DataRow row in dataTable.Rows) { Dictionary <string, object> tmp = new Dictionary <string, object>(); foreach (string k in pk) { if (row[k] != null) { tmp[k + t.ToString()] = row[k]; } } pars_.Add(tmp); t++; } if (connection.State == ConnectionState.Open) { connection.Close(); } log.Info("Connection Closed!"); } Dictionary <string, object> pars2 = new Dictionary <string, object>(); string query2 = "SELECT * FROM " + tabName + " WHERE " + "(" + string.Join(", ", pk.Select(x => x)) + ") " + "IN ("; int r = 0; string cond_ = ""; foreach (Dictionary <string, object> tmp in pars_) { cond_ += "("; cond_ += string.Join(", ", tmp.Select(z => "@" + z.Key)); cond_ += ")"; if (r < pars_.Count - 1) { cond_ += ", "; } r++; pars2 = pars2.Concat(tmp).ToDictionary(x => x.Key, x => x.Value); } query2 += cond_; query2 += ")"; log.Info(string.Format("Query: {0}", query2)); log.Info(string.Format("Params: {0}", string.Join("; ", pars2.Select(x => x.Key + " = " + x.Value).ToArray()))); data = ExecuteQueryWithParams(connectionString, query2, pars2); log.Info(string.Format("Query Executed! Retrieved {0} records!", LibString.ItemsNumber(data))); return(data); } catch (Exception ex) { string msg = "An Error occured! Exception detected!"; log.Info(msg); log.Error(msg + " " + ex.Message); throw; } finally { tw.Stop(); log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed))); } }
static public int MultiInsertOperation <T>(string connectionString, string tabName, List <T> dataVOs, List <string> autoincrement = null) { Stopwatch tw = new Stopwatch(); tw.Start(); int result = -1; try { List <Dictionary <string, object> > pars_ = new List <Dictionary <string, object> >(); Dictionary <string, object> into = new Dictionary <string, object>(); int i = 0; foreach (object dataVO in dataVOs) { Dictionary <string, object> pars = new Dictionary <string, object>(); foreach (var prop in dataVO.GetType().GetProperties()) { if (!autoincrement.Select(e => e.ToLower()).ToList().Contains(prop.Name.ToLower())) { string keyOfPar = string.Format("{0}{1}", prop.Name, i); pars[keyOfPar] = prop.GetValue(dataVO, null); //Console.WriteLine("{0}={1}", prop.Name, prop.GetValue(chiamata, null)); if (i == 0) { into[prop.Name] = prop.GetValue(dataVO, null); } } } i++; pars_.Add(pars); } string query = "INSERT INTO " + tabName + " (" + string.Join(", ", into.Select(x => x.Key).ToArray()) + ") VALUES "; into = null; Dictionary <string, object> parss = new Dictionary <string, object>(); int j = 1; foreach (Dictionary <string, object> pars in pars_) { query += "(" + string.Join(", ", pars.Select(x => "@" + x.Key).ToArray()) + ")"; parss = parss.Union(pars).ToDictionary(k => k.Key, v => v.Value); if (j < pars_.Count) { query += ", "; } j++; } log.Info(string.Format("Query: {0}", query)); log.Info(string.Format("Params: {0}", string.Join("; ", parss.Select(x => x.Key + "=" + x.Value).ToArray()))); result = ExecuteNonQueryWithParams(connectionString, query, parss); log.Info(string.Format("Query Executed! Inserted {0} records!", result)); return(result); } catch (Exception ex) { string msg = "An Error occured! Exception detected!"; log.Info(msg); log.Error(msg + " " + ex.Message); throw; } finally { tw.Stop(); log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed))); } }
static public DataTable InsertBackOperation(string connectionString, string tabName, object dataVO, List <string> pk, List <string> autoincrement = null) { Stopwatch tw = new Stopwatch(); tw.Start(); if (autoincrement == null) { autoincrement = new List <string>() { } } ; DataTable data = null; try { Dictionary <string, object> pars = new Dictionary <string, object>(); Dictionary <string, object> pars_ = new Dictionary <string, object>(); foreach (var prop in dataVO.GetType().GetProperties()) { pars[prop.Name] = prop.GetValue(dataVO, null); } string query = "INSERT INTO " + tabName + " (" + string.Join(", ", pars.Where(l => !autoincrement.Select(d => d.ToLower()).ToList().Contains(l.Key.ToLower())).Select(x => x.Key).ToArray()) + ") " + "OUTPUT " + string.Join(", ", pk.Select(p => "INSERTED." + p).ToArray()) + " VALUES " + "(" + string.Join(", ", pars.Where(l => !autoincrement.Select(d => d.ToLower()).ToList().Contains(l.Key.ToLower())).Select(x => "@" + x.Key.ToLower()).ToArray()) + ")"; using (SqlConnection connection = new SqlConnection(connectionString)) using (SqlCommand cmd = new SqlCommand(query, connection)) { foreach (KeyValuePair <string, object> entry in pars) { if (!autoincrement.Select(d => d.ToLower()).ToList().Contains(entry.Key.ToLower())) { object dat = entry.Value != null ? entry.Value : DBNull.Value; cmd.Parameters.AddWithValue(entry.Key, dat); } } log.Info(string.Format("Opening Connection on '{0}' ...", HidePwd(connectionString))); connection.Open(); log.Info(string.Format("Query: {0}", LibString.SQLCommand2String(cmd))); log.Info(string.Format("Query execution starting ...")); DataTable dataTable = new DataTable(); using (var reader = cmd.ExecuteReader()) { dataTable.Load(reader); } log.Info(string.Format("Query Insert executed! Result count: {0}", LibString.ItemsNumber(dataTable))); DataRow row = dataTable.Rows[0]; foreach (string k in pk) { if (row[k] != null) { pars_[k] = row[k]; } } row = null; if (connection.State == ConnectionState.Open) { connection.Close(); } log.Info("Connection Closed!"); } string query2 = "SELECT * FROM " + tabName + " WHERE " + string.Join(" AND ", pars_.Select(x => x.Key + " = " + "@" + x.Key + " ").ToArray()); log.Info(string.Format("Query: {0}", query)); log.Info(string.Format("Params: {0}", string.Join("; ", pars.Select(x => x.Key + " = " + x.Value).ToArray()))); data = ExecuteQueryWithParams(connectionString, query2, pars_); log.Info(string.Format("Query Executed! Retrieved {0} records!", LibString.ItemsNumber(data))); return(data); } catch (Exception ex) { string msg = "An Error occured! Exception detected!"; log.Info(msg); log.Error(msg + " " + ex.Message); throw; } finally { tw.Stop(); log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed))); } }