public string GetJobTasksTables(string userID) { try { List <SqlParameter> parameters = new List <SqlParameter>(); parameters.Add(new SqlParameter("@userID", userID)); //DataSet ds=SqlHelper.ExecuteDataset(connectionstring, "spTransferDataToSQLApp_ListofStepsbyUser", userID); CallSQLSP callsql = new CallSQLSP(); callsql.connectionstring = connectionstring; callsql.spname = "spTransferDataToSQLApp_ListofStepsbyUser"; callsql.parameters = parameters; callsql.ExecSPDataSetReturn(); msg = callsql.msg; if (msg == "Succeed") { DataSet ds = callsql.ds; JobsList = ds.Tables[0]; JobTasksList = ds.Tables[1]; msg = "Succeed"; } return(msg); } catch (Exception e) { msg = e.Message; return(msg); } }
public static void SQL_SP(JSON JSONString) { complete = false; CallSQLSP callsql = new CallSQLSP(); List <SqlParameter> parameters = new List <SqlParameter>(); try { for (int x = 0; x < JSONString.SP_Parameters.Count; x++) { int val = Convert.ToInt32(JSONString.SP_Parameters.ElementAt(x).GetValue("value")); parameters.Add(new SqlParameter(JSONString.SP_Parameters.ElementAt(x).GetValue("innername").ToString(), val)); } callsql.connectionstring = JSONString.ConnectionString; callsql.spname = JSONString.SP_Name; callsql.parameters = parameters; callsql.ExecSPNoReturn(); } catch (Exception e) { complete = true; UserInterface.Error = true; UserInterface.logger = LogManager.GetCurrentClassLogger(); UserInterface.logger.Error(e, "SQL_SP failed"); MessageBox.Show("SQL_SP failed", "ERROR"); return; } string msg = callsql.msg; complete = true; UserInterface.logger = LogManager.GetCurrentClassLogger(); UserInterface.logger.Debug("SQL_SP" + msg); MessageBox.Show("SQL_SP " + msg, "SQL_SP Message"); }
public static void SQL_SP_ImportData(Excel.Range fileRange, JSON JSONString) { complete = false; CallSQLSP callsql = new CallSQLSP(); string connectionstring = JSONString.ConnectionString; int rows = fileRange.Rows.Count; int cols = fileRange.Columns.Count; string msg = callsql.msg; try { for (int i = 2; i <= rows; i++) { var val = ""; List <SqlParameter> parameters = new List <SqlParameter>(); for (int j = 1; j <= cols; j++) { if (fileRange.Cells[i, j].Value2 == null) { val = null; } else { val = fileRange.Cells[i, j].Value2.ToString(); //Replace the "?" in the JSON string with the corresponding value in user's excel file if (JSONString.SP_Parameters.ElementAt(j - 1).GetValue("value").ToString().Equals("?")) { if (JSONString.SP_Parameters.ElementAt(j - 1).GetValue("type").ToString().Equals("date")) { double d = double.Parse(val); DateTime conv = DateTime.FromOADate(d); val = conv.ToShortDateString(); } } } parameters.Add(new SqlParameter("@" + JSONString.SP_Parameters.ElementAt(j - 1).GetValue("innername").ToString(), val)); } // Stored Procedure callsql.connectionstring = connectionstring; callsql.spname = JSONString.SP_Name; callsql.parameters = parameters; callsql.ExecSPNoReturn(); msg = callsql.msg; } } catch (Exception e) { complete = true; UserInterface.Error = true; UserInterface.logger = LogManager.GetCurrentClassLogger(); UserInterface.logger.Error(e, "SQL_SP_ImportData Error"); MessageBox.Show("Fail to import data to database", "ERROR"); return; } complete = true; UserInterface.logger = LogManager.GetCurrentClassLogger(); UserInterface.logger.Debug("SQL_SP_ImportData" + msg); MessageBox.Show("SQL_SP_ImportData " + msg, "SQL_SP_ImportData Message"); }