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");
}