public string JSONfromSQLnative(string queryin)
{
string resultout = "";
string truequery = queryin;
CloseReaderifopen();
if (!(truequery.Contains("FOR JSON path, INCLUDE_NULL_VALUES")))
{
;
}
{
truequery = Regex.Replace(truequery, @";", "");
truequery = truequery + " FOR JSON path, INCLUDE_NULL_VALUES";
}
//string Rowcheck = RowsExist(truequery);
//if (Rowcheck == "0") { return "No Rows Found in query"; }
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = truequery;
cmd.Connection = DCdatbase;
MasterReader = cmd.ExecuteReader();
if (MasterReader.HasRows)
{
while (MasterReader.Read())
{
resultout = MasterReader.GetString(0);
}
MasterReader.Close();
}
else
{
resultout = "No Records Found";
}
}
catch (Exception ex)
{
using (EventLog eventLog = new EventLog("Application"))
{
WindowsLogger EH1 = new WindowsLogger();
EH1.WriteErrorToWindowsApplicationLog(ex, this.GetType().Name, MethodBase.GetCurrentMethod().Name, "queryin = " + @queryin);
eventLog.Source = "Application";
}
resultout = resultout = "Error occurred and was logged to the Windows Application Log";
}
return(resultout);
}
//************** END Constructor ********************************
public void NonQuery(string commandin)
{
try
{
SqlCommand cmd = new SqlCommand(commandin, DCdatbase);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
using (EventLog eventLog = new EventLog("Application"))
{
WindowsLogger EH1 = new WindowsLogger();
EH1.WriteErrorToWindowsApplicationLog(ex, this.GetType().Name, MethodBase.GetCurrentMethod().Name, "commandin = " + @commandin);
eventLog.Source = "Application";
}
}
}
public string JSONfromSQL(string queryin)
{
string resultout = "";
string singlerowJSON = "";
string truequery = queryin;
CloseReaderifopen();
//string Rowcheck = RowsExist(truequery);
//if (Rowcheck == "0") { return "No Rows Found in query"; }
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = truequery;
cmd.Connection = DCdatbase;
MasterReader = cmd.ExecuteReader();
if (MasterReader.HasRows)
{
resultout = "[";
while (MasterReader.Read())
{
singlerowJSON = "{";
for (int x = 0; x < MasterReader.FieldCount; x++)
{
if (x <= MasterReader.FieldCount)
{
singlerowJSON = singlerowJSON + "\"" + MasterReader.GetName(x) + "\":\"" + ConvertIfneeded(MasterReader, x) + "\",";
}
else
{
singlerowJSON = singlerowJSON + "\"" + MasterReader.GetName(x) + "\":\"" + ConvertIfneeded(MasterReader, x) + "\"}";
}
}
resultout = resultout + singlerowJSON;
if (resultout.EndsWith(","))
{
resultout = resultout.Remove(resultout.Length - 1, 1);
resultout = resultout + "},";
}
}
if (resultout.EndsWith(","))
{
resultout = resultout.Remove(resultout.Length - 1, 1);
}
MasterReader.Close();
resultout = resultout + "]";
}
else
{
resultout = "No Records Found";
}
}
catch (Exception ex)
{
using (EventLog eventLog = new EventLog("Application"))
{
WindowsLogger EH1 = new WindowsLogger();
EH1.WriteErrorToWindowsApplicationLog(ex, this.GetType().Name, MethodBase.GetCurrentMethod().Name, "queryin = " + @queryin);
eventLog.Source = "Application";
}
resultout = resultout = "Error occurred and was logged to the Windows Application Log";
}
return(resultout);
}
