public void Delete(DataTable dt, string TableName, params string[] keyColumns)
{
List <string> p = new List <string>();
List <string> k;
try
{
foreach (DataRow dr in dt.Rows)
{
k = new List <string>();
foreach (string keys in keyColumns)
{
k.Add(string.Format("{0} = {1}", keys, QueryCom.SQLFormat(dr[keys])));
}
p.Add(string.Format("Delete from dbo.{0} Where {1};", TableName, string.Join(" AND ", k.ToArray())));
}
if (string.Join("\r\n", p.ToArray()).Trim() != "")
{
q.ExecNonQuery(string.Join("\r\n", p.ToArray()));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
p = null;
k = null;
}
}
public DataRow ReBuildDataSchema(string Table, DataRow dr)
{
using (DataTable sdt = q.mArgs.TableQuery($"SELECT * FROM dbo.fGetTableDef({ QueryCom.SQLFormat(Table) }) WHERE Computed = 0 and datatype != 'uniqueidentifier' ORDER BY Column_id"))
{
using (DataTable rdt = new DataTable(Table))
{
DateTime b = DateTime.Now;
foreach (DataRow dc in sdt.Rows)
{
rdt.Columns.Add(dc["ColumnName"].ToString(), QueryCom.GetDbType(dc["DataType"].ToString()));
if ((rdt.Columns[dc["ColumnName"].ToString()].DataType == typeof(DateTime) && DateTime.TryParse(dc["Default"].ToString(), out b)) || rdt.Columns[dc["ColumnName"].ToString()].DataType != typeof(DateTime))
{
rdt.Columns[dc["ColumnName"].ToString()].DefaultValue = dc["Default"];
}
rdt.Columns[dc["ColumnName"].ToString()].AllowDBNull = Convert.ToBoolean(dc["Nullable"]);
}
DataRow rdr = rdt.NewRow();
foreach (DataRow dc in sdt.Rows)
{
object val = DBNull.Value;
string name = dc["ColumnName"].ToString();
if (dr.Table.Columns.Contains(name))
{
val = dr[name];
val = (val.ToString() == "NULL" || val.ToString() == "") ? DBNull.Value : val;
if (!rdt.Columns[name].AllowDBNull && val == DBNull.Value)
{
if (rdt.Columns[name].DataType == typeof(DateTime))
{
val = DateTime.Now; //set by default
}
else if (rdt.Columns[name].DefaultValue != DBNull.Value)
{
val = rdt.Columns[name].DefaultValue;
}
}
rdr[name] = val;
}
}
rdt.Rows.Add(rdr);
return(rdt.Rows[0]);
}
}
}
public DataTable ReBuildDataTable(string Table, DataTable dt)
{
using (DataTable sdt = q.mArgs.TableQuery($"SELECT * FROM dbo.fGetTableDef({ QueryCom.SQLFormat(Table) }) WHERE Computed = 0 and datatype != 'uniqueidentifier' ORDER BY Column_id"))//q.ExecQuery(string.Format("SELECT * FROM dbo.fGetTableDef({0}) WHERE Computed = 0 ORDER BY Column_id", QueryCom.SQLFormat(Table))).Tables[0]
{
using (DataTable rdt = new DataTable(Table))
{
foreach (DataRow dc in sdt.Rows)
{
if (!dt.Columns.Contains(dc["ColumnName"].ToString()))
{
continue;
}
rdt.Columns.Add(dc["ColumnName"].ToString(), QueryCom.GetDbType(dc["DataType"].ToString()));
}
//check if ID is included
if (!rdt.Columns.Contains("ID"))
{
rdt.Columns.Add("ID", typeof(int));
rdt.Columns["ID"].DefaultValue = 0;
}
foreach (DataRow dr in dt.Rows)
{
DataRow rdr = rdt.NewRow();
foreach (DataRow dc in sdt.Rows)
{
if (!dt.Columns.Contains(dc["ColumnName"].ToString()))
{
continue;
}
object val = DBNull.Value;
string name = dc["ColumnName"].ToString();
if (!dt.Columns.Contains(name))
{
if (dc["Default"] != DBNull.Value)
{
if (QueryCom.GetDbType(dc["DataType"].ToString()) == typeof(bool))
{
val = Convert.ToBoolean(Convert.ToInt32(dc["Default"].ToString().Replace("(", "").Replace(")", "")));
}
else
{
val = dc["Default"];
}
}
}
else
{
if (dr[name] == DBNull.Value)
{
if (dc["Default"] != DBNull.Value)
{
if (QueryCom.GetDbType(dc["DataType"].ToString()) == typeof(bool))
{
val = Convert.ToBoolean(Convert.ToInt32(dc["Default"].ToString().Replace("(", "").Replace(")", "")));
}
else
{
val = dc["Default"];
}
}
}
else
{
val = dr[name];
}
}
rdr[name] = val;
}
rdt.Rows.Add(rdr);
}
return(rdt); //.Rows[0];
}
}
}
public static string SQLInsertString(this DataTable dt, string TableName = "") => QueryCom.BuildInsertString(dt, TableName == "" ? dt.TableName : TableName);
public static string SQLFormat(this object inp, string escapeDelimiter = "''")
{
return(QueryCom.SQLFormat(inp, escapeDelimiter));
}