private void prepareInsertStatement(string[] state, string tableName)
{
List <DBClause> cs = new List <DBClause>();
CSVData data = CSVData.ParseString(state, true);
all = data.Items;
List <string> c = new List <string>();
foreach (TableField tf in DataSchema.Fields)
{
cs.Add(DBParam.ParamWithDelegate(delegate {
if (tf.Type == FieldType._datetime)
{
if (item[data.GetOffset(tf.Field)] != "")
{
return(Convert.ToDateTime(item[data.GetOffset(tf.Field)]).ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
return(null);
}
}
else if (tf.Type == FieldType._bool)
{
if (item[data.GetOffset(tf.Field)] != "")
{
if (item[data.GetOffset(tf.Field)] == "true" || item[data.GetOffset(tf.Field)] == "True" || item[data.GetOffset(tf.Field)] == "1")
{
return(true);
}
else
{
return(false);
}
// return Convert.ToDateTime(item[data.GetOffset(tf.Field)]).ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
return(false);
}
}
else
{
if (item[data.GetOffset(tf.Field)] != "")
{
return(item[data.GetOffset(tf.Field)].Trim());
}
else
{
return(null);
}
}
}));
c.Add(tf.Field);
}
insert = DBQuery.InsertInto(tableName)
.Fields(c.ToArray())
.Values(cs.ToArray());
}
public void _12_InsertSimpleXmlTest()
{
DBParam pname = DBParam.ParamWithDelegate(delegate { return("My Customer Name"); });
DBParam paddress = DBParam.ParamWithDelegate(delegate { return("My Customer Address"); });
DBQuery q = DBQuery.InsertInto("CUSTOMERS").Fields("customer_name", "customer_address")
.Values(pname, paddress);
q = SerializeAndDeserialzeQueryToMatch(q, "Simple Insert");
}
public bool Save(ref DataTable dtFields)
{
DataRow item = null;
//dtFields.Columns.Add(new DataColumn("fieldid"));
//for (int i = 0; i < dtFields.Rows.Count; i++)
//{
// dtFields.Rows[i]["fieldid"] = Shared.generateID();
//}
DBParam dbfieldid = DBParam.ParamWithDelegate(DbType.String, delegate { return(item["fieldid"] == null ? Shared.generateID() : item["fieldid"]); });
DBParam dbTableid = DBParam.ParamWithDelegate(DbType.String, delegate { return(item["tableid"] == null ? "" : item["tableid"]); });
DBParam dbfieldName = DBParam.ParamWithDelegate(DbType.String, delegate { return(item["fieldname"] == null ? "" : item["fieldname"]); });
DBParam dbfieldtype = DBParam.ParamWithDelegate(DbType.Int32, delegate { return(item["fieldtype"] == null ? 1 : item["fieldtype"]); });
DBParam dbisnull = DBParam.ParamWithDelegate(DbType.Boolean, delegate { return(item["isnull"] == null ? false : item["isnull"]); });
DBParam dbisprimary = DBParam.ParamWithDelegate(DbType.Boolean, delegate { return(item["isprimary"] == null?false: item["isprimary"]); });
DBParam dblength = DBParam.ParamWithDelegate(DbType.Int32, delegate { return(item["length"] == null? 0: item["length"]); });
DBQuery insert = DBQuery.InsertInto(TzAccount.Field.Table).Fields(
TzAccount.Field.TableID.Name,
TzAccount.Field.FieldID.Name,
TzAccount.Field.FieldName.Name,
TzAccount.Field.FieldType.Name,
TzAccount.Field.Length.Name,
TzAccount.Field.IsNullable.Name,
TzAccount.Field.ISPrimaryKey.Name
).Values(
dbTableid,
dbfieldid,
dbfieldName,
dbfieldtype,
dblength,
dbisnull,
dbisprimary
);
int sum = 0;
using (DbTransaction trans = db.BeginTransaction())
{
for (int i = 0; i < dtFields.Rows.Count; i++)
{
item = dtFields.Rows[i];
sum += db.ExecuteNonQuery(trans, insert);
}
trans.Commit();
}
if (sum == dtFields.Rows.Count)
{
return(true);
}
else
{
return(false);
}
}
public void _13_InsertWithSubSelectXmlTest()
{
DBParam pduplicate = DBParam.ParamWithDelegate("dupid", System.Data.DbType.Int32, delegate { return(10); });
DBQuery q = DBQuery.InsertInto("CUSTOMERS").Fields("customer_name", "customer_address")
.Select(DBQuery.Select().Fields("customer_name", "customer_address")
.From("CUSTOMERS").As("DUP")
.WhereFieldEquals("customer_id", pduplicate));
q = SerializeAndDeserialzeQueryToMatch(q, "Sub Select Insert");
}
public void _14_ScriptTest()
{
DBParam pname = DBParam.ParamWithDelegate(delegate { return("My Customer Name"); });
DBParam paddress = DBParam.ParamWithDelegate(delegate { return("My Customer Address"); });
DBQuery q = DBQuery.Begin(
DBQuery.InsertInto("CUSOMERS").Fields("customer_name", "customer_address")
.Values(pname, paddress),
DBQuery.Select(DBFunction.LastID()))
.End();
q = SerializeAndDeserialzeQueryToMatch(q, "Simple Script");
}
public void Test_01_SelectOneCategoryTest()
{
int id = 1;
DBSelectQuery select = DBQuery.SelectAll()
.From("Categories").As("C")
.WhereField("CategoryID", Compare.Equals, DBParam.ParamWithDelegate(DbType.Int32, delegate { return(id); }))
.GroupBy("CategoryName")
.OrderBy("ProductCount", Order.Descending);
this.OutputSql(select, " SELECT ONE CATEGORY");
this.OutputXML(select, " SELECT ONE CATEGORY");
}
private void prepareInsertStatement(string state, string tb, string pFields)
{
List <DBClause> cs = new List <DBClause>();
CSVData data = CSVData.ParseString(string.Join(Environment.NewLine, state), true);
all = data.Items;
List <string> c = new List <string>();
string[] flds;
flds = pFields.Split(System.Convert.ToChar(","));
foreach (string s in flds)
{
cs.Add(DBParam.ParamWithDelegate(() => itm[data.GetOffset(s)]));
c.Add(s);
}
insert = DBQuery.InsertInto(tb).Fields(c.ToArray()).Values(cs.ToArray());
}