static public int ExecuteNonQueryWithParams(string connectionString, string sql, Dictionary <string, object> atts)
{
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))
{
foreach (KeyValuePair <string, object> entry in atts)
{
if (entry.Value == null)
{
cmd.Parameters.AddWithValue(entry.Key, DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue(entry.Key, entry.Value);
}
}
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 ..."));
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);
}
static public DataTable ExecuteQueryWithParams(string connectionString, string sql, Dictionary <string, object> atts)
{
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))
{
foreach (KeyValuePair <string, object> entry in atts)
{
cmd.Parameters.AddWithValue(entry.Key, entry.Value);
}
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 ..."));
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 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 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)));
}
}