/// <summary> /// /// Execute Multiple queries and return result of each query in a list /// /// </summary> /// <param name="sql">sql splitted by '/'</param> /// <param name="lstParams">Parameters for each query </param> /// <returns> /// json dataset</returns> public JsonResult ExecuteNonQuery(SqlParamsIn sqlIn) { SqlHelper h = new SqlHelper(); object data = h.ExecuteNonQuery(sqlIn); return(Json(JsonConvert.SerializeObject(data), JsonRequestBehavior.AllowGet)); }
/// <summary> /// json object representation of dataset /// --has table or tables - /// -- /// </summary> /// <param name="sqlIn">sql param datacontract </param> /// <returns> /// json dataset</returns> public JsonResult JDataSetWithCode(SqlParamsIn sqlIn) { SqlHelper h = new SqlHelper(); Ctx ctx = Session["ctx"] as Ctx; h.SetContext(ctx); if (sqlIn.sql.IndexOf("~") > -1) { string[] sql = sqlIn.sql.Split('~'); for (int i = 0; i < sql.Length; i++) { if (i == 0) { sqlIn.sql = QueryCollection.GetQuery(sql[i], ctx); } else { sqlIn.sql += "~" + QueryCollection.GetQuery(sql[i], ctx); } } } else { sqlIn.sql = QueryCollection.GetQuery(sqlIn.sql, ctx); } object data = h.ExecuteJDataSet(sqlIn); return(Json(JsonConvert.SerializeObject(data), JsonRequestBehavior.AllowGet)); }
/// <summary> /// Attribute Listing Tree.. /// </summary> /// <returns></returns> public object createTreeAttributeName() { StringBuilder sql = new StringBuilder(); sql.Append(MRole.GetDefault(_ctx).AddAccessSQL("SELECT M_Attribute_id , Name FROM M_Attribute WHERE IsActive='Y'", "M_Attribute", true, true)); sql.Append("~"); sql.Append(MRole.GetDefault(_ctx).AddAccessSQL("SELECT ma.M_Attribute_id,mv.m_attributevalue_id, ma.Name as NameAttribute,mv.Name FROM M_Attribute ma inner JOIN m_attributevalue mv ON ma.M_Attribute_id=mv.M_Attribute_id", "M_Attribute", true, true)); SqlParamsIn sqlpar = new SqlParamsIn(); sqlpar.sql = sql.ToString(); sqlpar.pageSize = 0; VIS.Helpers.SqlHelper sHelper = new VIS.Helpers.SqlHelper(); DataSet ds = sHelper.ExecuteDataSet(sqlpar); if (ds == null || ds.Tables.Count < 1) { return(null); } VA005_TreeStructure tree = new VA005_TreeStructure(); tree.text = Msg.GetMsg(_ctx, "VA005_AttributeTree"); tree.NodeID = 0; tree.items = new List <VA005_TreeStructure>(); LoadAttributeName(ds, tree); List <VA005_TreeStructure> final = new List <VA005_TreeStructure>(); final.Add(tree); return(final); }
//private bool isDataAccess(string sql) //{ //} public DataSet ExecuteDataSet(SqlParamsIn sqlIn) { if (String.IsNullOrEmpty(sqlIn.sql)) { return(null); } string sql = sqlIn.sql; bool doPaging = sqlIn.pageSize > 0; SqlParams[] paramIn = sqlIn.param == null ? null : sqlIn.param.ToArray(); Trx trxName = null; string[] tables = sql.Split('~'); DataSet ds = new DataSet(); int i = 0; foreach (string table in tables) { string tableName = "Table" + i; DataSet dsTemp = null; if (!doPaging) { dsTemp = VIS.DBase.DB.ExecuteDataset(table, paramIn, trxName); if (dsTemp != null && dsTemp.Tables.Count > 0) { DataTable data = dsTemp.Tables[0]; dsTemp.Tables.Remove(data); data.TableName = tableName; ds.Tables.Add(data); } i++; // ds = VAdvantage.DataBase.DB.SetUtcDateTime(ds); } else //Paging { ds = VIS.DBase.DB.ExecuteDatasetPaging(sql, sqlIn.page, sqlIn.pageSize); } } if (ds == null || ds.Tables.Count < 1) { return(null); } ds = VAdvantage.DataBase.DB.SetUtcDateTime(ds); return(ds); }
public ActionResult GetTextButtonQueryResult(string sql) { Ctx ctx = Session["ctx"] as Ctx; SqlParamsIn sqlP = new SqlParamsIn(); sql = SecureEngineBridge.DecryptByClientKey(sql, ctx.GetSecureKey()); sqlP.sql = sql; VIS.Helpers.SqlHelper help = new Helpers.SqlHelper(); return(Json(JsonConvert.SerializeObject(help.ExecuteJDataSet(sqlP)), JsonRequestBehavior.AllowGet)); }
public JsonResult JDataSet(SqlParamsIn sqlIn) { SqlHelper h = new SqlHelper(); sqlIn.sql = Server.HtmlDecode(sqlIn.sql); VAdvantage.Utility.Ctx ctx = Session["ctx"] as Ctx; sqlIn.sql = SecureEngineBridge.DecryptByClientKey(sqlIn.sql, ctx.GetSecureKey()); object data = h.ExecuteJDataSet(sqlIn); return(Json(JsonConvert.SerializeObject(data), JsonRequestBehavior.AllowGet)); }
/// <summary> /// /// Execute Multiple queries and return result of each query in a list /// /// </summary> /// <param name="sql">sql splitted by '/'</param> /// <param name="lstParams">Parameters for each query </param> /// <returns> /// json dataset</returns> public JsonResult ExecuteNonQueryWithCode(SqlParamsIn sqlIn) { SqlHelper h = new SqlHelper(); Ctx ctx = Session["ctx"] as Ctx; h.SetContext(ctx); //sqlIn.sql = SecureEngineBridge.DecryptByClientKey(sqlIn.sql, ctx.GetSecureKey()); sqlIn.sql = QueryCollection.GetQuery(sqlIn.sql, ctx); object data = h.ExecuteNonQuery(sqlIn); return(Json(JsonConvert.SerializeObject(data), JsonRequestBehavior.AllowGet)); }
/// <summary> /// USed to Create Tree. This is main function which intialize tree creation. /// </summary> public object CreateTree(string Expend) { exp = Expend; StringBuilder sql = new StringBuilder(); sql.Append(MRole.GetDefault(_ctx).AddAccessSQL("SELECT m_attributeSet_ID , Name FROM m_attributeSet WHERE IsActive='Y' ORDER BY LOWER(Name),m_attributeSet_ID", "M_AttributeSet", true, true)); sql.Append("~"); sql.Append(MRole.GetDefault(_ctx).AddAccessSQL(@"SELECT m_attribute.m_attribute_ID,m_attribute.Name,m_attributeuse.m_attributeset_id,m_attribute.ATTRIBUTEVALUETYPE FROM m_attributeuse JOIN m_attribute ON m_attributeuse.m_attribute_ID=m_attribute.m_attribute_ID WHERE m_attributeuse.IsActive='Y' AND m_attributeuse.IsActive='Y' ORDER BY m_attribute.Name,m_attribute.m_attribute_ID", "M_AttributeUse", true, true)); sql.Append("~"); sql.Append(MRole.GetDefault(_ctx).AddAccessSQL("SELECT m_attribute_ID , name, M_AttributeValue_ID FROM M_AttributeValue WHERE IsActive='Y' ORDER BY name,m_attribute_ID ", "M_AttributeValue", true, true)); sql.Append("~"); SqlParamsIn sqlpar = new SqlParamsIn(); sqlpar.sql = sql.ToString(); sqlpar.pageSize = 0; VIS.Helpers.SqlHelper sHelper = new VIS.Helpers.SqlHelper(); DataSet ds = sHelper.ExecuteDataSet(sqlpar); if (ds == null || ds.Tables.Count < 1) { return(null); } VA005_TreeStructure tree = new VA005_TreeStructure(); tree.text = Msg.GetMsg(_ctx, "VA005_AttributeSetListing"); tree.NodeID = 0; //tree.ParentID = 0; tree.expanded = true; tree.visibility = "none"; tree.ShowInfo = "none"; tree.padding = "8px 10px 8px 10px"; tree.margin = "0 0 0 0"; //tree.ImageSource = "Areas/VA005/Images/1.png"; tree.items = new List <VA005_TreeStructure>(); LoadAttributeSet(ds, tree); //hidden List <VA005_TreeStructure> final = new List <VA005_TreeStructure>(); final.Add(tree); return(final); }
/// <summary> /// retrun Grid window Model json object against window Id /// </summary> /// <param name="windowNo">window number</param> /// <param name="AD_Window_ID">window Id</param> /// <returns>grid window json result</returns> public JsonResult GetWindowRecords(List <string> fields, SqlParamsIn sqlIn, int rowCount, string sqlCount, int AD_Table_ID) { object data = null; if (Session["ctx"] == null) { } else { using (var w = new WindowHelper()) { data = w.GetWindowRecords(sqlIn, fields, Session["ctx"] as Ctx, rowCount, sqlCount, AD_Table_ID); } } return(Json(JsonConvert.SerializeObject(data), JsonRequestBehavior.AllowGet)); }
/// <summary> /// retrun Grid window Model json object against window Id /// </summary> /// <param name="windowNo">window number</param> /// <param name="AD_Window_ID">window Id</param> /// <returns>grid window json result</returns> public JsonResult GetWindowRecordsForTreeNode(List <string> fields, SqlParamsIn sqlIn, int rowCount, string sqlCount, int AD_Table_ID, int treeID, int treeNodeID, List <string> obscureFields) { object data = null; if (Session["ctx"] == null) { } else { using (var w = new WindowHelper()) { Ctx ctx = Session["ctx"] as Ctx; sqlIn.sql = SecureEngineBridge.DecryptByClientKey(sqlIn.sql, ctx.GetSecureKey()); sqlIn.sql = Server.HtmlDecode(sqlIn.sql); sqlCount = SecureEngineBridge.DecryptByClientKey(sqlCount, ctx.GetSecureKey()); data = w.GetWindowRecordsForTreeNode(sqlIn, fields, ctx, rowCount, sqlCount, AD_Table_ID, treeID, treeNodeID, obscureFields); } } return(Json(JsonConvert.SerializeObject(data), JsonRequestBehavior.AllowGet)); }
public int ExecuteNonQuery(SqlParamsIn sqlIn) { SqlParams[] paramIn = sqlIn.param == null ? null : sqlIn.param.ToArray(); string sql = sqlIn.sql; //if (!CanDeletingADValue(sql)) //{ // return -1; //} //string sql = sqlIn.sql; //if (!IsTableAccessForExecute(sql)) // return -1; if (IsChangingSystemSettings(sql)) { return(-1); } return(VIS.DBase.DB.ExecuteQuery(sqlIn.sql, paramIn, null)); }
public ActionResult GetPrintFormatDetails(int AD_Table_ID) { Ctx ctx = Session["ctx"] as Ctx; FormModel model = new FormModel(ctx); string sql = MRole.GetDefault(ctx).AddAccessSQL( "SELECT AD_PrintFormat_ID, Name, Description,IsDefault " + "FROM AD_PrintFormat " + "WHERE AD_Table_ID= " + AD_Table_ID + " ORDER BY Name", "AD_PrintFormat", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO); SqlParamsIn sqlP = new SqlParamsIn(); sqlP.sql = sql; VIS.Helpers.SqlHelper help = new Helpers.SqlHelper(); List <JTable> result = help.ExecuteJDataSet(sqlP); return(Json(JsonConvert.SerializeObject(result), JsonRequestBehavior.AllowGet)); }
/// <summary> /// fetch Data from DataServer and create list of Jtable objects /// </summary> /// <param name="sqlIn">input parameter</param> /// <returns>null if error , other wise list of JTable object </returns> public List <JTable> ExecuteJDataSet(SqlParamsIn sqlIn) { try { List <JTable> outO = new List <JTable>(); JTable obj = null; DataSet ds = null; string sql = sqlIn.sql; //string sql = sqlIn.sql; //if (!IsTableAccess(sql)) // return null; ds = ExecuteDataSet(sqlIn); if (ds == null || ds.Tables.Count < 1) { return(null); } // bool singleTable = ds.Tables.Count >= 0; //StringBuilder tables = new StringBuilder("["); // StringBuilder columns = new StringBuilder(!singleTable ? "[{" : "{"); int MAX_ROWS = 10001; for (int table = 0; table < ds.Tables.Count; table++) { obj = new JTable(); var dt = ds.Tables[table]; obj.name = dt.TableName; obj.records = ds.Tables[table].Rows.Count; obj.page = sqlIn.page; obj.total = 1; for (int column = 0; column < dt.Columns.Count; column++) { var cc = new JColumn(); cc.index = column; cc.name = dt.Columns[column].ColumnName.ToLower(); cc.type = dt.Columns[column].DataType.ToString().ToLower(); obj.columns.Add(cc); } int count = dt.Rows.Count; for (int row = 0; row < count; row++) { if (row > MAX_ROWS) { break; } JRow r = new JRow(); r.id = row; for (int column = 0; column < dt.Columns.Count; column++) { //var c = new Dictionary<string,object>(); //c[dt.Columns[column].ColumnName.ToLower()] = dt.Rows[row][column]; r.cells[dt.Columns[column].ColumnName.ToLower()] = dt.Rows[row][column]; //rows.Append(dt.Columns[column].ColumnName).Append(":").Append(dt.Rows[row][column]); } obj.rows.Add(r); } outO.Add(obj); } ds.Tables.Clear(); ds.Dispose(); return(outO); } catch { return(null); } }
public int ExecuteNonQuery(SqlParamsIn sqlIn) { SqlParams[] paramIn = sqlIn.param == null ? null : sqlIn.param.ToArray(); return(VIS.DBase.DB.ExecuteQuery(sqlIn.sql, paramIn, null)); }