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