public void ReplaceDataSource(string p) { string pOrg = p; string bMarkAsDone = ""; string bMultiRow = "N"; string iColVal = ""; DataSourceBase DataSource = null; string DSName = p.Substring(9, p.IndexOf(" DST=") - 9); foreach (DataSourceBase ds in DSList) { if (ds.Name == DSName) { DataSource = ds; } } p = p.Substring(p.IndexOf(" DST=")).Trim(); if (DataSource == null) { mValueCalculated = mValueCalculated.Replace(p, string.Format("ERROR: The Data Source Variable '{0}' was not found", DataSource)); return; } if (DataSource.DSType == DataSourceBase.eDSType.MSAccess) { if (DataSource.FileFullPath.StartsWith("~")) { DataSource.FileFullPath = DataSource.FileFullPath.Replace(@"~\", "").Replace("~", ""); DataSource.FileFullPath = Path.Combine(WorkSpace.Instance.SolutionRepository.SolutionFolder, DataSource.FileFullPath); } DataSource.Init(DataSource.FileFullPath); } string Query = ""; string updateQuery = ""; List <string> mColList = null; string rowNum = "0"; string DSTable = ""; string sAct = ""; string IRow = ""; string ExcelPath = ""; string ExcelSheet = ""; try { DSTable = p.Substring(p.IndexOf("DST=") + 4, p.IndexOf(" ") - 4); mColList = DataSource.DSC.GetColumnList(DSTable); p = p.Substring(p.TrimStart().IndexOf(" ")).Trim(); if (p.IndexOf("ACT=") != -1) { sAct = p.Substring(p.IndexOf("ACT=") + 4, p.IndexOf(" ") - 4); if (sAct == "DA") // Delete All Rows { updateQuery = "Delete From " + DSTable; p = ""; } else if (sAct == "YA") // Mark All Used { updateQuery = "Update " + DSTable + " SET GINGER_USED='True'"; p = ""; } else if (sAct == "NA") // Mark All UnUsed { updateQuery = "Update " + DSTable + " SET GINGER_USED='False'"; p = ""; } else if (sAct == "RC") // Get Row Count { Query = "Select COUNT(*) FROM " + DSTable; p = ""; } else if (sAct == "ARC") // Get Available Row Count { Query = "Select COUNT(*) FROM " + DSTable + " WHERE GINGER_USED <> 'True' or GINGER_USED is null"; p = ""; } else if (sAct == "ETE") // Get Row Count { Query = ""; p = p.Substring(p.TrimStart().IndexOf(" ")).Trim(); } else { p = p.Substring(p.TrimStart().IndexOf(" ")).Trim(); } } if (p.IndexOf("EP=") != -1) { ExcelPath = p.Substring(p.IndexOf("EP=") + 3, p.IndexOf(" ") - 3); p = p.Substring(p.TrimStart().IndexOf(" ")).Trim(); ExcelSheet = p.Substring(p.IndexOf("ES=") + 3, p.IndexOf("}") - 3); } else if (p.IndexOf("KEY=") != -1) { string KeyName = p.Substring(p.IndexOf("KEY=") + 4, p.IndexOf("}") - 4); if (sAct == "DR") { updateQuery = "DELETE FROM " + DSTable + " WHERE GINGER_KEY_NAME = '" + KeyName + "'"; } else { if (bUpdate == true) { DataTable dtTemp = DataSource.DSC.GetQueryOutput("Select count(*) from " + DSTable + " where GINGER_KEY_NAME= '" + KeyName + "'"); if (dtTemp.Rows[0].ItemArray[0].ToString() != "0") { updateQuery = "UPDATE " + DSTable + " SET GINGER_KEY_VALUE = '" + updateValue.Replace("'", "''") + "',GINGER_LAST_UPDATED_BY='" + System.Environment.UserName + "',GINGER_LAST_UPDATE_DATETIME='" + DateTime.Now.ToString() + "' WHERE GINGER_KEY_NAME = '" + KeyName + "'"; } else { updateQuery = "INSERT INTO " + DSTable + "(GINGER_KEY_NAME,GINGER_KEY_VALUE,GINGER_LAST_UPDATED_BY,GINGER_LAST_UPDATE_DATETIME) VALUES ('" + KeyName + "','" + updateValue.Replace("'", "''") + "','" + System.Environment.UserName + "','" + DateTime.Now.ToString() + "')"; } } else { Query = "Select GINGER_KEY_VALUE FROM " + DSTable + " WHERE GINGER_KEY_NAME = '" + KeyName + "'"; } } } else if (p != "" && (sAct == "MASD" || sAct == "DR" || sAct == "")) { bMarkAsDone = p.Substring(p.IndexOf("MASD=") + 5, p.IndexOf(" ") - 5); p = p.Substring(p.TrimStart().IndexOf(" ")).Trim(); if (p.IndexOf("MR=") == 0) { bMultiRow = p.Substring(p.IndexOf("MR=") + 3, p.IndexOf(" ") - 3); p = p.Substring(p.TrimStart().IndexOf(" ")).Trim(); } string DSIden = p.Substring(p.IndexOf("IDEN=") + 5, p.IndexOf(" ") - 5); p = p.Substring(p.TrimStart().IndexOf(" ")).Trim(); if (DSIden == "Query") { Query = p.Substring(p.IndexOf("QUERY=") + 6, p.Length - 7); if (Query.ToUpper().IndexOf("SELECT *") == -1) { Query = Regex.Replace(Query, " FROM ", ",[GINGER_ID] FROM ", RegexOptions.IgnoreCase); } } else { Query = "Select "; iColVal = p.Substring(p.IndexOf("ICOLVAL=") + 8, p.IndexOf("IROW=") - 9); iColVal = "[" + iColVal + "]"; p = p.Substring(p.TrimStart().IndexOf("IROW=")); Query = Query + iColVal + ",[GINGER_ID] from " + DSTable; if (p.IndexOf(" ") > 0) { IRow = p.Substring(p.IndexOf("IROW=") + 5, p.IndexOf(" ") - 5); } else { IRow = p.Substring(p.IndexOf("IROW=") + 5, p.IndexOf("}") - 5); } if (IRow == "NxtAvail") { Query = Query + " Where GINGER_USED <> 'True' or GINGER_USED is null"; } else if (IRow == "RowNum") { p = p.Substring(p.TrimStart().IndexOf("ROWNUM=")); rowNum = p.Substring(p.IndexOf("ROWNUM=") + 7, p.IndexOf("}") - 7); } else if (IRow == "Where") { if (p.TrimStart().IndexOf("COND=") != -1) { p = p.Substring(p.TrimStart().IndexOf("COND=")); string Cond = p.Substring(p.IndexOf("COND=") + 5, p.IndexOf("}") - 5); Query = Query + " Where " + Cond; } else if (p.TrimStart().IndexOf("WCOLVAL=") != -1 && p.TrimStart().IndexOf("WOPR=") != -1) { p = p.Substring(p.TrimStart().IndexOf("WCOLVAL=")); string wColVal = p.Substring(p.IndexOf("WCOLVAL=") + 8, p.IndexOf("WOPR=") - 9); wColVal = "[" + wColVal + "]"; Query = Query + " Where "; p = p.Substring(p.TrimStart().IndexOf("WOPR=")); string wOpr = ""; string wRowVal = ""; if (p.IndexOf("WROWVAL=") == -1) { wOpr = p.Substring(p.IndexOf("WOPR=") + 5, p.IndexOf("}") - 5); } else { wOpr = p.Substring(p.IndexOf("WOPR=") + 5, p.IndexOf("WROWVAL=") - 6); } if (wOpr != "Is Null" && wOpr != "Is Null") { p = p.Substring(p.TrimStart().IndexOf("WROWVAL=")); wRowVal = p.Substring(p.IndexOf("WROWVAL=") + 8, p.IndexOf("}") - 8); } if (wOpr == "Equals") { if (wColVal == "[GINGER_ID]") { Query = Query + wColVal + " = " + wRowVal + ""; } else { Query = Query + wColVal + " = '" + wRowVal + "'"; } } else if (wOpr == "NotEquals") { if (wColVal == "[GINGER_ID]") { Query = Query + wColVal + " <> " + wRowVal + ""; } else { Query = Query + wColVal + " <> '" + wRowVal + "'"; } } else if (wOpr == "Contains") { Query = Query + wColVal + " LIKE " + "'%" + wRowVal + "%'"; } else if (wOpr == "Not Contains") { Query = Query + wColVal + " NOT LIKE " + "'%" + wRowVal + "%'"; } else if (wOpr == "Starts With") { Query = Query + wColVal + " LIKE '" + wRowVal + "%'"; } else if (wOpr == "Not Starts With") { Query = Query + wColVal + " NOT LIKE '" + wRowVal + "%'"; } else if (wOpr == "Ends With") { Query = Query + wColVal + " LIKE '%" + wRowVal + "'"; } else if (wOpr == "Not Ends With") { Query = Query + wColVal + " NOT LIKE '%" + wRowVal + "'"; } else if (wOpr == "Is Null") { Query = Query + wColVal + " IS NULL"; } else if (wOpr == "Is Not Null") { Query = Query + wColVal + " IS NOT NULL"; } } else { return; } } } } } catch (Exception e) { mValueCalculated = pOrg; Console.WriteLine(e.StackTrace); } if (Query != "") { DataTable dt = DataSource.DSC.GetQueryOutput(Query); if (dt.Rows.Count == 0 && IRow == "NxtAvail" && bUpdate == true) { DataSource.DSC.RunQuery("INSERT INTO " + DSTable + "(GINGER_USED) VALUES ('False')"); dt = DataSource.DSC.GetQueryOutput(Query); } if (dt.Rows.Count == 0) { mValueCalculated = "No Row found with " + Query; return; } if (dt.Rows.Count > 0 && dt.Columns.Count > 0) { if (rowNum.All(char.IsDigit)) { mValueCalculated = mValueCalculated.Replace(pOrg, dt.Rows[Convert.ToInt32(rowNum)].ItemArray[0].ToString()); } else { mValueCalculated = "ERROR: Not Valid RowNum:" + rowNum; } } string GingerIds = ""; if (dt.Columns.Contains("GINGER_ID")) { if (bMultiRow == "Y") { foreach (DataRow row in dt.Rows) { GingerIds += row["GINGER_ID"].ToString() + ","; } GingerIds = GingerIds.Substring(0, GingerIds.Length - 1); } else { GingerIds = dt.Rows[Convert.ToInt32(rowNum)]["GINGER_ID"].ToString(); } } if (bUpdate == true) { if (updateQuery == "") { if (iColVal == "") { iColVal = dt.Columns[0].ColumnName; } if (updateValue == null) { updateValue = string.Empty; } updateQuery = "UPDATE " + DSTable + " SET "; foreach (DataColumn sCol in dt.Columns) { if (!new List <string> { "GINGER_ID", "GINGER_LAST_UPDATED_BY", "GINGER_LAST_UPDATE_DATETIME", "GINGER_KEY_NAME" }.Contains(sCol.ColumnName)) { updateQuery += "[" + sCol.ColumnName + "]='" + updateValue.Replace("'", "''") + "' ,"; } } updateQuery = updateQuery.Substring(0, updateQuery.Length - 1); if (mColList.Contains("GINGER_LAST_UPDATED_BY")) { updateQuery = updateQuery + ",GINGER_LAST_UPDATED_BY='" + System.Environment.UserName + "' "; } if (mColList.Contains("GINGER_LAST_UPDATE_DATETIME")) { updateQuery = updateQuery + ",GINGER_LAST_UPDATE_DATETIME = '" + DateTime.Now.ToString() + "' "; } updateQuery = updateQuery + "WHERE GINGER_ID IN (" + GingerIds + ")"; } DataSource.DSC.RunQuery(updateQuery); } if (bMarkAsDone == "Y" && bDone == true) { DataSource.DSC.RunQuery("UPDATE " + DSTable + " SET GINGER_USED ='True' WHERE GINGER_ID IN (" + GingerIds + ")"); } else if (sAct == "DR" && bDone == true) { DataSource.DSC.RunQuery("DELETE FROM " + DSTable + " WHERE GINGER_ID IN (" + GingerIds + ")"); } } else if (updateQuery != "" && bDone == true) { DataSource.DSC.RunQuery(updateQuery); mValueCalculated = ""; } else if (sAct == "ETE" && bDone == true) { if (ExcelSheet == "") { ExcelSheet = DSTable; } if (ExcelPath.ToLower().EndsWith(".xlsx")) { DataSource.DSC.ExporttoExcel(DSTable, ExcelPath, ExcelSheet); mValueCalculated = ""; } else { mValueCalculated = "The Export Excel can be *.xlsx only"; } } DataSource.Close(); }