public List <SQL_utils.dbfield> Hashtable_to_dbfields(Hashtable hash, string tbl, string db)
{
SQL_utils sql = new SQL_utils(db);
DataTable dt = new DataTable();
DataTable dt_flds = FieldDatatypes_for_table(tbl, db);
List <SQL_utils.dbfield> flds = new List <SQL_utils.dbfield>();
foreach (DictionaryEntry d in hash)
{
//Get the data type for the field from the db
try
{
string fldtype = dt_flds.AsEnumerable().Where(r => r.Field <string>("column_name").ToLower() == d.Key.ToString().ToLower()).Select(r => r.Field <string>("data_type")).First();
SQL_utils.dbfield fld = new SQL_utils.dbfield();
fld.fieldname = d.Key.ToString();
fld.value = d.Value.ToString();
fld.sqldbtype = fldtype;
flds.Add(fld);
}
catch (Exception ex)
{
string x = ex.Message;
//field not in table
}
}
sql.Close();
return(flds);
}
public List <SQL_utils.dbfield> Hashtable_to_dbfields(Hashtable hash, string tbl, string db, string pk)
{
//Do not include the pk field
SQL_utils sql = new SQL_utils(db);
DataTable dt = new DataTable();
DataTable dt_flds = FieldDatatypes_for_table(tbl, db);
List <SQL_utils.dbfield> flds = new List <SQL_utils.dbfield>();
foreach (DictionaryEntry d in hash)
{
//check to see if this field is present in this table
int fld_in_table = dt_flds.AsEnumerable().Where(r => r.Field <string>("column_name").ToLower() == d.Key.ToString().ToLower()).Select(r => r.Field <string>("data_type")).Count();
if (fld_in_table >= 1) //if this field is in the table then add it to the list
{
//Get the data type for the field from the db
string fldtype = dt_flds.AsEnumerable().Where(r => r.Field <string>("column_name").ToLower() == d.Key.ToString().ToLower()).Select(r => r.Field <string>("data_type")).First();
//Do not create a dbfield for the pk as this is an update and the pk is only used in the where clause
if (d.Key.ToString() != pk)
{
SQL_utils.dbfield fld = new SQL_utils.dbfield();
fld.fieldname = d.Key.ToString();
fld.value = d.Value.ToString();
fld.sqldbtype = fldtype;
flds.Add(fld);
}
}
}
sql.Close();
return(flds);
}
public static List <SQL_utils.dbfield> dxGrid_UpdateData_CreateDbFields(SQL_utils sql, string pk, OrderedDictionary newvalues, string tbl, string schema) //, string db, string pk )
{
//Do not include the pk field
string sql_get_fields = "select column_name, data_type from information_schema.columns where table_schema = '" + schema + "'" + " and table_name = '" + tbl + "'";
DataTable dt_flds = sql.DataTable_from_SQLstring(sql_get_fields);
List <SQL_utils.dbfield> flds = new List <SQL_utils.dbfield>();
IDictionaryEnumerator enumerator = newvalues.GetEnumerator();
enumerator.Reset();
while (enumerator.MoveNext())
{
string fieldname = enumerator.Key.ToString().ToLower();
//check to see if this field is present in this table
int fld_in_table = dt_flds.AsEnumerable().Where(r => r.Field <string>("column_name").ToLower() == fieldname).Select(r => r.Field <string>("column_name")).Count();
if (fld_in_table >= 1) //if this field is in the table then add it to the list
{
//Get the data type for the field from the db
string fldtype = dt_flds.AsEnumerable().Where(r => r.Field <string>("column_name").ToLower() == enumerator.Key.ToString().ToLower()).Select(r => r.Field <string>("data_type")).First();
SQL_utils.dbfield fld = new SQL_utils.dbfield();
fld.fieldname = enumerator.Key.ToString();
if (enumerator.Value != null)
{
fld.value = enumerator.Value.ToString();
}
fld.sqldbtype = fldtype;
flds.Add(fld);
}
}
return(flds);
}