/// <summary>
///
/// </summary>
/// <param name="clientid"></param>
/// <param name="componentitemrelationid"></param>
/// <returns></returns>
public bool RemoveItemRelation(string clientid,
string componentitemrelationid)
{
DBDatabase db;
db = base.Database;
DBConst dbclientID = DBConst.String(clientid);
DBConst dbcomponentitemrelationid = DBConst.String(componentitemrelationid);
DBComparison Client = DBComparison.Equal(DBField.Field(TzAccount.ComponentModalRelation.ClientID.Name), dbclientID);
DBComparison compModalID = DBComparison.Equal(DBField.Field(TzAccount.ComponentModalRelation.ComponentModalRelationID.Name), dbcomponentitemrelationid);
DBQuery del = DBQuery.DeleteFrom(TzAccount.ComponentModalRelation.Table)
.WhereAll(Client, compModalID);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="clientID"></param>
/// <param name="name"></param>
/// <param name="category"></param>
/// <param name="ComponentModalID"></param>
/// <returns></returns>
public bool Update(string clientID, string name,
string category, string ComponentModalID)
{
DBDatabase db;
db = base.Database;
DBConst dbclientID = DBConst.String(clientID);
DBConst dbname = DBConst.String(name);
DBConst dbcategory = DBConst.String(category);
DBConst dbComponentModalID = DBConst.String(ComponentModalID);
DBComparison Client = DBComparison.Equal(DBField.Field(TzAccount.ComponentModal.ClientID.Name), dbclientID);
DBComparison compModalID = DBComparison.Equal(DBField.Field(TzAccount.ComponentModal.ComponentModalID.Name), dbComponentModalID);
DBQuery update = DBQuery.Update(TzAccount.ComponentModal.Table).Set(
TzAccount.ComponentModal.Name.Name, dbname
).Set(
TzAccount.ComponentModal.Catgory.Name, dbcategory
).WhereAll(compModalID, Client);
int i = db.ExecuteNonQuery(update);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public void _05_AggregateXmlTest()
{
DBQuery q = DBQuery.Select().Field(DBField.Field("UnitPrice") * DBField.Field("Quantity") * (DBConst.Const(1.0) - DBField.Field("Discount"))).As("OrderValue")
.From("Orders").As("0");
q = SerializeAndDeserialzeQueryToMatch(q, "Aggregate test");
}
public bool RemoveAppComponent(string clientID, string appid, string elementid)
{
DBDatabase db;
db = base.Database;
DBConst dbClientID = DBConst.String(clientID);
DBConst dbappid = DBConst.String(appid);
DBConst dbelement = DBConst.String(elementid);
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.AppElements.ClientID.Name), dbClientID);
DBComparison app = DBComparison.Equal(DBField.Field(TzAccount.AppElements.AppID.Name), dbappid);
DBComparison element = DBComparison.Equal(DBField.Field(TzAccount.AppElements.ElementID.Name), dbelement);
DBQuery del = DBQuery.DeleteFrom(TzAccount.AppElements.Table)
.WhereAll(client, app, element);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Update(string clientid, string groupID,
string groupName,
string description,
int context,
bool isBase)
{
DBComparison SecurityGroupID = DBComparison.Equal(DBField.Field(TzAccount.SecurityGroup.SecurityGroupID.Name), DBConst.String(groupID));
DBComparison Client = DBComparison.Equal(DBField.Field(TzAccount.SecurityGroup.ClientID.Name), DBConst.String(clientid));
DBQuery update = DBQuery.Update(TzAccount.SecurityGroup.Table).Set(
TzAccount.SecurityGroup.GroupName.Name, DBConst.String(groupName)
).Set(
TzAccount.SecurityGroup.Description.Name, DBConst.String(description)
).Set(
TzAccount.SecurityGroup.IsBaseType.Name, DBConst.Const(DbType.Boolean, isBase)
).Set(
TzAccount.SecurityGroup.Context.Name, DBConst.Int32(context)).WhereAll(SecurityGroupID, Client);
int i = db.ExecuteNonQuery(update);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="clientid"></param>
/// <param name="componentlookupid"></param>
/// <param name="isactive"></param>
/// <returns></returns>
public bool UpateActive(string clientid,
string componentlookupid,
bool isactive)
{
DBDatabase db;
db = base.Database;
DBConst dbComponentLookupID = DBConst.String(componentlookupid);
DBConst dbclientID = DBConst.String(clientid);
DBConst dbisactive = DBConst.Const(DbType.Boolean, isactive);
DBComparison componentlookup = DBComparison.Equal(DBField.Field(TzAccount.ComponentLookUpItem.ComponentLookupItemID.Name), dbComponentLookupID);
DBComparison Client = DBComparison.Equal(DBField.Field(TzAccount.ComponentLookUpItem.ClientID.Name), dbclientID);
DBQuery update = DBQuery.Update(TzAccount.ComponentLookUpItem.Table).Set(
TzAccount.ComponentLookUpItem.IsActive.Name, dbisactive
).WhereAll(componentlookup, Client);
int i = db.ExecuteNonQuery(update);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool UpdateRole(string clientID, string userID, string role)
{
DBDatabase db;
db = base.Database;
DBConst dbUserID = DBConst.String(userID);
DBConst dbclientID = DBConst.String(clientID);
DBConst dbStatus = DBConst.Const(DbType.String, role);
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.User.ClientID.Name), dbclientID);
DBComparison user = DBComparison.Equal(DBField.Field(TzAccount.User.UserID.Name), dbUserID);
DBQuery update = DBQuery.Update(TzAccount.User.Table).Set(
TzAccount.User.UserRole.Name, dbStatus)
.WhereAll(client, user);
int i = db.ExecuteNonQuery(update);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
// public string Save( DataTable dt
// )
// {
// string clientid;
// string groupid;
//// string userid,
// string componentid;
// int componentype;
// bool add;
// bool edit;
// bool view;
// bool remove;
// string a = Shared.generateID();
// var insert = DBQuery.InsertInto(TzAccount.SecurityPrivilege.Table)
// .Fields(TzAccount.SecurityPrivilege.PrivilegeID.Name,
// TzAccount.SecurityPrivilege.ClientID.Name,
// TzAccount.SecurityPrivilege.SecurityGroupID.Name,
// //TzAccount.SecurityPrivilege.UserID.Name,
// TzAccount.SecurityPrivilege.SecurityComponentID.Name,
// TzAccount.SecurityPrivilege.ComponentType.Name,
// TzAccount.SecurityPrivilege.Add.Name,
// TzAccount.SecurityPrivilege.Edit.Name,
// TzAccount.SecurityPrivilege.Remove.Name,
// TzAccount.SecurityPrivilege.View.Name
// ).
// Values(DBConst.String(a),
// DBConst.String(clientid),
// DBConst.String(groupid),
// // DBConst.String(userid),
// DBConst.String(componentid),
// DBConst.Int32(componentype),
// DBConst.Const(DbType.Boolean, add),
// DBConst.Const(DbType.Boolean, edit),
// DBConst.Const(DbType.Boolean, view),
// DBConst.Const(DbType.Boolean, remove));
// int val = 0;
// using (DbTransaction trans = db.BeginTransaction())
// {
// val = db.ExecuteNonQuery(trans, insert);
// trans.Commit();
// }
// if (val > 0)
// {
// return a;
// }
// else
// {
// return "";
// }
// }
/// <summary>
///
/// </summary>
/// <param name="clientid"></param>
/// <param name="privilegeid"></param>
/// <param name="groupid"></param>
/// <param name="componentID"></param>
/// <param name="add"></param>
/// <param name="edit"></param>
/// <param name="view"></param>
/// <param name="remove"></param>
/// <returns></returns>
public bool update(string clientid,
string privilegeid,
string groupid,
string componentID,
bool add,
bool edit,
bool view,
bool remove)
{
DBComparison privilege = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.PrivilegeID.Name), DBConst.String(privilegeid));
DBComparison component = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.SecurityComponentID.Name), DBConst.String(componentID));
DBComparison SecurityGroupID = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.SecurityGroupID.Name), DBConst.String(groupid));
DBComparison Client = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.ClientID.Name), DBConst.String(clientid));
DBQuery update = DBQuery.Update(TzAccount.SecurityPrivilege.Table).Set(
TzAccount.SecurityPrivilege.Add.Name, DBConst.Const(DbType.Boolean, add)
).Set(
TzAccount.SecurityPrivilege.Edit.Name, DBConst.Const(DbType.Boolean, edit)
).Set(
TzAccount.SecurityPrivilege.View.Name, DBConst.Const(DbType.Boolean, view)
).Set(
TzAccount.SecurityPrivilege.Remove.Name, DBConst.Const(DbType.Boolean, remove)).
WhereAll(SecurityGroupID, Client, privilege, component);
int i = db.ExecuteNonQuery(update);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public void _06_FullComplexInnerSelectXmlTest()
{
DBSelectQuery orderValue = DBQuery.Select()
.Sum(DBField.Field("UnitPrice") * DBField.Field("Quantity") * (DBConst.Const(1.0) - DBField.Field("Discount"))).As("OrderValue")
.And(DBFunction.IsNull("OrderID", DBConst.Const("No ID")))
.Field("O", "customerID")
.From("Orders").As("O")
.InnerJoin("Order Details").As("OD")
.On("OD", "OrderID", Compare.Equals, "O", "OrderID")
.GroupBy("O", "CustomerID");
DBQuery q = DBQuery.SelectTopN(10)
.Field("CUST", "CompanyName").As("Company Name")
.Field("CUST", "CustomerID").As("Customer ID")
.And(DBFunction.IsNull("CUST", "ContactName", DBConst.Const("No Contact"))).As("Contact")
.Sum("ORD", "OrderValue").As("Total Value")
.From("Customers").As("CUST")
.InnerJoin(orderValue).As("ORD")
.On("ORD", "CustomerID", Compare.Equals, "CUST", "CustomerID")
.WhereField("ORD", "OrderValue", Compare.GreaterThanEqual, DBConst.Const(500.0))
.GroupBy("CUST", "CompanyName").And("CUST", "CustomerID")
.OrderBy("Total Value", Order.Descending);
q = SerializeAndDeserialzeQueryToMatch(q, "Complex Inner Select Test");
}
public bool Update(string clientid,
string appid,
string appName,
string description,
string category)
{
DBDatabase db;
db = base.Database;
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.App.ClientID.Name), DBConst.String(clientid));
DBComparison app = DBComparison.Equal(DBField.Field(TzAccount.App.AppID.Name), DBConst.String(appid));
DBQuery update = DBQuery.Update(TzAccount.App.Table).Set(
TzAccount.App.Name.Name, DBConst.String(appName)
).Set(
TzAccount.App.Description.Name, DBConst.String(description)
)
//.Set(
//TzAccount.Component.PrimaryKeys.Name, dbprimarykeys
//)
.Set(
TzAccount.App.Category.Name, DBConst.String(category)
).WhereAll(client, app);
int i = db.ExecuteNonQuery(update);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="scriptID"></param>
/// <param name="script"></param>
/// <returns></returns>
public bool Update(string scriptID, string script, string name, string category)
{
DBConst dbscript = DBConst.String(script);
DBDatabase db;
db = base.Database;
DBComparison scriptid = DBComparison.Equal(DBField.Field(TzAccount.DataScript.ScriptID.Name), DBConst.String(scriptID));
//DBComparison server = DBComparison.Equal(DBField.Field(TzAccount.DataScript.ServerID.Name), DBConst.String(serverID));
DBQuery update = DBQuery.Update(TzAccount.DataScript.Table).Set(
TzAccount.DataScript.Script.Name, dbscript
).Set(
TzAccount.DataScript.ScriptName.Name, DBConst.String(name)
).Set(
TzAccount.DataScript.Category.Name, DBConst.String(category)
).WhereAll(scriptid);
int i = db.ExecuteNonQuery(update);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
protected internal DataTable GetImport(int cid)
{
DBComparison client = DBComparison.Equal(DBField.Field(TalentozTemplate.ClientID.Name), DBConst.Int32(cid));
DBQuery select = DBQuery.SelectAll().From(TalentozTemplate.Table).Where(client).OrderBy(TalentozTemplate.Name.Name, Order.Ascending);
return(db.GetDatatable(select));
}
public bool ChangeShape(string formid,
string formfieldid,
string clientid,
string attribute
)
{
DBDatabase db;
db = base.Database;
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.FormFields.ClientID.Name), DBConst.String(clientid));
DBComparison form = DBComparison.Equal(DBField.Field(TzAccount.FormFields.FormID.Name), DBConst.String(formid));
DBComparison formfield = DBComparison.Equal(DBField.Field(TzAccount.FormFields.FieldID.Name), DBConst.String(formfieldid));
DBQuery update = DBQuery.Update(TzAccount.FormFields.Table).Set(
TzAccount.FormFields.FieldAttribute.Name, DBConst.String(attribute)
).WhereAll(client, form, formfield);
int i = db.ExecuteNonQuery(update);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="clientid"></param>
/// <param name="componentlookupid"></param>
/// <param name="categoryid"></param>
/// <returns></returns>
public bool RemoveAllLookupItems(string clientid,
string componentlookupid
)
{
DBDatabase db;
db = base.Database;
DBConst dbcomponentlookupid = DBConst.String(componentlookupid);
DBConst dbclientid = DBConst.String(clientid);
DBComparison component = DBComparison.Equal(DBField.Field(TzAccount.ComponentLookUpItem.ComponentLookupItemID.Name), dbcomponentlookupid);
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.ComponentLookUpItem.ClientID.Name), dbclientid);
DBQuery del = DBQuery.DeleteFrom(TzAccount.ComponentLookUpItem.Table)
.WhereAll(component, client);
int i = db.ExecuteNonQuery(del);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="clientid"></param>
/// <param name="lookupid"></param>
/// <param name="name"></param>
/// <returns></returns>
public bool UpdateLookup(string clientid,
string lookupid,
string name, string description,
bool isCore)
{
DBDatabase db;
db = base.Database;
DBConst dbComponentLookupID = DBConst.String(lookupid);
DBConst dbclientID = DBConst.String(clientid);
DBConst dbparentid = DBConst.String(name);
DBComparison componentlookup = DBComparison.Equal(DBField.Field(TzAccount.ComponentLookUp.LookupID.Name), dbComponentLookupID);
DBComparison Client = DBComparison.Equal(DBField.Field(TzAccount.ComponentLookUp.ClientID.Name), dbclientID);
DBQuery update = DBQuery.Update(TzAccount.ComponentLookUp.Table).Set(
TzAccount.ComponentLookUp.LookUpName.Name, dbparentid
).Set(
TzAccount.ComponentLookUp.Description.Name, DBConst.String(description)
).Set(
TzAccount.ComponentLookUp.IsCore.Name, DBConst.Const(DbType.Boolean, isCore)
).WhereAll(componentlookup, Client);
int i = db.ExecuteNonQuery(update);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public void Test_03_ScriptOfSelectCustomersWithOrdersOver500()
{
DBSelectQuery orderValue = DBQuery.Select()
.Sum(DBField.Field("UnitPrice") * DBField.Field("Quantity") * (DBConst.Const(1.0) - DBField.Field("Discount"))).As("OrderValue")
.And(DBFunction.IsNull("OrderID", DBConst.Const("No ID")))
.Field("O", "customerID")
.From("Orders").As("O")
.InnerJoin("Order Details").As("OD")
.On("OD", "OrderID", Compare.Equals, "O", "OrderID")
.GroupBy("O", "CustomerID");
DBQuery q = DBQuery.SelectTopN(10)
.Field("CUST", "CompanyName").As("Company Name")
.Field("CUST", "CustomerID").As("Customer ID")
.And(DBFunction.IsNull("CUST", "ContactName", DBConst.Const("No Contact"))).As("Contact")
.Sum("ORD", "OrderValue").As("Total Value")
.From("Customers").As("CUST")
.InnerJoin(orderValue).As("ORD")
.On("ORD", "CustomerID", Compare.Equals, "CUST", "CustomerID")
.WhereField("ORD", "OrderValue", Compare.GreaterThanEqual, DBConst.Const(500.0))
.GroupBy("CUST", "CompanyName").And("CUST", "CustomerID")
.OrderBy("Total Value", Order.Descending);
string sql = this.OutputSql(q, "SelectCustomersWithOrdersOver500", true);
string xml = this.OutputXML(q, "SelectCustomersWithOrdersOver500");
ValidateXml(xml, sql, "SelectCustomersWithOrdersOver500");
}
public DataTable GetView(string viewID)
{
DBComparison dbView = DBComparison.Compare(DBField.Field(TalentozViewSchema.ViewID.Name),
Compare.Equals, DBConst.String(viewID));
DBQuery se = DBQuery.SelectAll().From(TalentozView.Table).Where(dbView);
return(db.GetDatatable(se));
}
/// <summary>
/// We are really hacking the underlying String builder. Not Pretty
/// </summary>
protected virtual void WrapSelectInTopROWNUM()
{
StringBuilder sb = this.GetWritersInnerBuilder();
if (null == sb)
{
throw new InvalidOperationException(Errors.CannotUseTopAsInnerWriterNotSupportPosition);
}
if (_hastop == false)
{
throw new InvalidOperationException("Calling wrap top but not set as a TOP N");
}
string subselect = sb.ToString(_selectstart, sb.Length - _selectstart);
sb.Length = _selectstart;
sb.Append(SELECTTOPSTART);
if (null != _assignReceivers && _assignReceivers.Count > 0)
{
bool lastwasselect = _isInSelectList;
_isInSelectList = true;
_hastop = false;
for (int i = 0; i < _assignReceivers.Count; i++)
{
if (i > 0)
{
sb.Append(",");
}
this.WriteAssignValue(_assignReceivers[i], DBField.Field(SELECTTOPFIELDSTEM + (i + 1).ToString()));
}
_isInSelectList = lastwasselect;
}
else
{
sb.Append("*");
}
sb.Append(SELECTTOPMIDDLE);
sb.Append(subselect);
string end;
if (_toptype == TopType.Range)
{
end = string.Format(SELECTTOPRANGEEND, _offsetcount, _offsetcount + _topcount);
}
else if (_toptype == TopType.Count)
{
end = string.Format(SELECTTOPNEND, _topcount);
}
else
{
throw new NotSupportedException("The TOP operation '" + _toptype + " is not supported");
}
sb.Append(end);
}
public bool RemoveView(string ViewID)
{
DBComparison dbView = DBComparison.Compare(DBField.Field(TalentozView.ViewID.Name),
Compare.Equals, DBConst.String(ViewID));
DBQuery deleteView = DBQuery.DeleteFrom(TalentozView.Table).Where(dbView);
db.ExecuteNonQuery(deleteView);
return(true);
}
protected internal DataTable GetImport(string importID, int cid)
{
DBComparison client = DBComparison.Equal(DBField.Field(TalentozTemplate.ClientID.Name), DBConst.Int32(cid));
DBComparison comp = DBComparison.Equal(DBField.Field(TalentozTemplate.TemplateID.Name), DBConst.String(importID));
DBComparison code = DBComparison.Equal(DBField.Field(TalentozTemplate.TemplateCode.Name), DBConst.String(importID));
DBQuery select = DBQuery.SelectAll().From(TalentozTemplate.Table).WhereAny(comp, code).And(client).OrderBy(TalentozTemplate.Name.Name, Order.Ascending);
return(db.GetDatatable(select));
}
protected internal DataTable GetComponent(string compID)
{
DBComparison comp = DBComparison.Equal(DBField.Field(TalentozSchema.ComponentID.Name), DBConst.String(compID));
DBComparison tb = DBComparison.Equal(DBField.Field(TalentozSchema.TableName.Name), DBConst.String(compID));
DBComparison cname = DBComparison.Equal(DBField.Field(TalentozSchema.ComponentName.Name), DBConst.String(compID));
DBQuery select = DBQuery.SelectAll().From(TalentozSchema.Table).WhereAny(comp, tb, cname).OrderBy(TalentozSchema.ComponentName.Name, Order.Ascending);
return(db.GetDatatable(select));
}
/// <summary>
///
/// </summary>
/// <param name="clientid"></param>
/// <param name="componentID"></param>
/// <param name="componentName"></param>
/// <param name="componenttype"></param>
/// <param name="tableid"></param>
/// <param name="title"></param>
/// <param name="primarykeys"></param>
/// <param name="titleformat"></param>
/// <param name="newlayout"></param>
/// <param name="readlayout"></param>
/// <returns></returns>
public bool UpdateComponent(string clientid, string componentID,
string componentName,
int componenttype,
string title,
string primarykeys,
string titleformat,
string newlayout,
string readlayout,
string category
)
{
DBDatabase db;
db = base.Database;
DBConst dbcomponentid = DBConst.String(componentID);
DBConst dbcomponentName = DBConst.String(componentName);
DBConst dbcomponenttype = DBConst.Int32(componenttype);
// DBConst dbtableid = DBConst.String(tableid);
DBConst dbtitle = DBConst.String(title);
DBConst dbprimarykeys = DBConst.String(primarykeys);
DBConst dbtitleformat = DBConst.String(titleformat);
DBConst dbnewlayout = DBConst.String(newlayout);
DBConst dbreadlayout = DBConst.String(readlayout);
DBConst dbcategory = DBConst.String(category);
DBComparison componentid = DBComparison.Equal(DBField.Field(TzAccount.Component.ComponentID.Name), dbcomponentid);
DBQuery update = DBQuery.Update(TzAccount.Component.Table).Set(
TzAccount.Component.ComponentName.Name, dbcomponentName
).Set(
TzAccount.Component.Category.Name, dbcategory
)
//.Set(
//TzAccount.Component.PrimaryKeys.Name, dbprimarykeys
//)
.Set(
TzAccount.Component.Title.Name, dbtitle
).Set(
TzAccount.Component.TitleFormat.Name, dbtitleformat
).Set(
TzAccount.Component.NewLayout.Name, dbnewlayout
).Set(
TzAccount.Component.ReadLayout.Name, dbreadlayout
).WhereAll(componentid);
int i = db.ExecuteNonQuery(update);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
public DataTable GetSecurityGroup(string clientid, string groupID)
{
DBQuery select;
// DBComparison baseType= DBComparison.Equal(DBField.Field(TzAccount.SecurityGroup.BaseType .Name), DBConst.Int32(pBaseType));
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.SecurityGroup.ClientID.Name), DBConst.String(clientid));
DBComparison group = DBComparison.Equal(DBField.Field(TzAccount.SecurityGroup.SecurityGroupID.Name), DBConst.String(groupID));
select = DBQuery.SelectAll(TzAccount.SecurityGroup.Table).From(TzAccount.SecurityGroup.Table)
.WhereAll(client, group);
return(db.GetDatatable(select));
}
public static bool SaveImportContext(string importID, string importContext, int step, string actionby, string conn, int clientID)
{
DataBase db = new DataBase();
db.InitDbs(conn);
if (GetImportContext(importID, conn).Rows.Count == 0)
{
DBQuery insert = DBQuery.InsertInto("tz_import_log")
.Field("importID")
.Field("ImportSchema")
.Field("Step")
.Field("ActionBy")
.Field("LastUPD")
//.Field("CreatedOn")
.Field("ClientID")
.Value(DBConst.String(importID))
.Value(DBConst.String(importContext))
.Value(DBConst.Int32(step))
.Value(DBConst.String(actionby))
.Value(DBConst.DateTime(DateTime.Now))
//.Value(DBConst.DateTime(DateTime.Now))
.Value(DBConst.Int32(clientID)
);
if (db.Database.ExecuteNonQuery(insert) > 0)
{
return(true);
}
else
{
return(false);
}
}
else
{
DBComparison dbcompo = DBComparison.Equal(DBField.Field("importID"), DBConst.String(importID));
// DBComparison dbclient = DBComparison.Equal(DBField.Field("NextID"), DBConst.Int32 (nextindex));
DBQuery update = DBQuery.Update("tz_import_log")
.Set("ImportSchema", DBConst.String(importContext))
.AndSet("ActionBy", DBConst.String(actionby))
.AndSet("Step", DBConst.Int32(step))
.AndSet("LastUPD", DBConst.DateTime(DateTime.Now)
).WhereAll(dbcompo);
int i = db.Database.ExecuteNonQuery(update);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
}
public void Test_19_TableHints()
{
DBQuery hinted = DBQuery.SelectAll()
.From("dbo", "Categories").WithHint(DBTableHint.NoLock).WithHint(DBTableHint.Index, "pk_Categories")
.InnerJoin("dbo", "Customers").WithHint(DBTableHint.ReadUncommitted)
.On("Categories", "category_id", Compare.Equals, "Customers", "category_id")
.WhereIn(DBField.Field("Categroies", "category_id"), DBConst.Int32(1), DBConst.Int32(2), DBConst.Int32(3))
.WithQueryOption(DBQueryOption.Fast, 10).WithQueryOption(DBQueryOption.HashJoin);
this.OutputAndValidate(hinted, " Hinted joined select query");
}
public DataTable GetComponentPrivilege(string clientid, string groupid)
{
DBQuery select;
DBComparison dbcomponentType = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.ComponentType.Name), DBConst.Int32(1));
// DBComparison user = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.UserID.Name), DBConst.String(userid));
DBComparison SecurityGroupID = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.SecurityGroupID.Name), DBConst.String(groupid));
DBComparison Client = DBComparison.Equal(DBField.Field(TzAccount.SecurityPrivilege.ClientID.Name), DBConst.String(clientid));
select = DBQuery.SelectAll(TzAccount.SecurityGroup.Table).From(TzAccount.SecurityGroup.Table)
.WhereAll(dbcomponentType, SecurityGroupID, Client);
return(db.GetDatatable(select));
}
protected internal bool ClearTemplate(int clientID)
{
DBComparison client = DBComparison.Equal(DBField.Field(TalentozSchemaClientInfo.ClientID.Name), DBConst.Int32(clientID));
DBQuery deleteView = DBQuery.DeleteFrom(TalentozTemplate.Table).WhereAll(client);
db.ExecuteNonQuery(deleteView);
deleteView = DBQuery.DeleteFrom(TalentozImportLog.Table).WhereAll(client);;
db.ExecuteNonQuery(deleteView);
return(true);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public DataTable GetTables(string clientid, string serverid)
{
DBQuery select;
DBComparison client = DBComparison.Equal(DBField.Field(TzAccount.ClientServer.ClientID.Name), DBConst.String(clientid));
DBComparison server = DBComparison.Equal(DBField.Field(TzAccount.ClientServer.ServerID.Name), DBConst.String(serverid));
DBConst dbclientid = DBConst.String(clientid);
select = DBQuery.SelectAll(TzAccount.Tables.Table).From(TzAccount.Tables.Table)
.WhereAll(client, server);
return(db.GetDatatable(select));
}
public static DataTable GetImportContext(string importID, string conn)
{
DataBase db = new DataBase();
db.InitDbs(conn);
DBQuery select;
DBComparison dbcompo = DBComparison.Equal(DBField.Field("importID"), DBConst.String(importID));
string[] a = { "importID", "ImportSchema", "ActionBy", "Step" };
select = DBQuery.Select().Fields(a).From("tz_import_log").Distinct().Where(dbcompo);
return(db.Database.GetDatatable(select));
}
public static DataTable GetLookup(int clientID, string conn)
{
DataBase db = new DataBase();
db.InitDbs(conn);
DBQuery select;
DBComparison dbcompo = DBComparison.Equal(DBField.Field("ClientID"), DBConst.Int32(clientID));
DBComparison dbclient = DBComparison.Equal(DBField.Field("ClientID"), DBConst.Int32(0));
string[] a = { "FieldInstanceID", "Name", "Type" };
select = DBQuery.Select().Fields(a).From("sys_lookup").Distinct().WhereAny(dbcompo, dbclient);
return(db.Database.GetDatatable(select));
}