protected override IDataReader PrepareSQLDatasetInternal(String sql, bool schemaOnly)
{
SACommand command;
SADataReader reader;
try
{
if (!Connected)
{
Connect();
}
command = _connection.CreateCommand();
command.CommandTimeout = CommandTimeout;
command.CommandText = sql;
if (schemaOnly)
{
reader = command.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
}
else
{
reader = command.ExecuteReader();
}
}
catch (Exception e)
{
throw new QueryBuilderException(ErrorCode.ErrorExecutingQuery,
e.Message + "\n\n" + Helpers.Localizer.GetString("strQuery", Constants.strQuery) + "\n" + sql);
}
return(reader);
}
public override DataTable ExecuteSelectCommand(string CommandName, CommandType cmdType)
{
SACommand cmd = null;
DataTable table = new DataTable();
cmd = _connection.CreateCommand();
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
try
{
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
}
SADataAdapter da = null;
using (da = new SADataAdapter(cmd))
{
da.Fill(table);
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
cmd = null;
_connection.Close();
}
return(table);
}
public string Note(string note_key)
{
SACommand myCommand = myConnection.CreateCommand();
myCommand.CommandText =
$@"SELECT note
FROM case_notes cn
where cn.note_key = {note_key}";
SADataReader myDataReader = myCommand.ExecuteReader();
DataSet ds = new DataSet();
ds.EnforceConstraints = false;
ds.Tables.Add("CaseNotes");
ds.Tables[0].Load(myDataReader);
myDataReader.Close();
return(ds.Tables[0].Rows[0]["note"].ToString());
}
public void CreateStoredProcedures()
{
using (var cn = new SAConnection(Properties.Settings.Default.ConnectionString))
{
cn.Open();
using (var cmd = cn.CreateCommand())
{
var script = Regex.Split(Properties.Resources.DatabaseReset, @"^\s*;\s*$", RegexOptions.Multiline)
.Select(s=>s.Trim())
.Where(s=> !String.IsNullOrWhiteSpace(s));
foreach (var sql in script)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
}
}
private NeedlesModel GetOpenCheckList(SAConnection myConnection)
{
SACommand myCommand = myConnection.CreateCommand();
myCommand.CommandText =
@"select cl.case_id as 'case'
, names.last_long_name + ', ' + names.prefix + ' ' + names.first_name as 'party_name'
, cl.code
, cl.description
, cl.staff_assigned as assigned
, cl.due_date
, cl.status
, cl_d.repeat_period
, cases.lim_stat
from case_checklist cl
inner join checklist_dir cl_d on cl_d.matcode = cl.matcode and cl_d.code = cl.code
inner join cases on cases.casenum = cl.case_id
inner join party on party.case_id = cases.casenum
inner join names on names.names_id = party.party_id and names.name_location = party.party_id_location and party.our_client = 'Y'
where cl.staff_assigned = 'KALAI' and cl.status = 'Open' //and cl.code = 'FEE'
order by cl.due_date asc";
SADataReader myDataReader = myCommand.ExecuteReader();
DataSet dsChecklist = new DataSet();
dsChecklist.Tables.Add("Checklist");
dsChecklist.Tables[0].Load(myDataReader);
NeedlesModel model = new NeedlesModel
{
CheckListCount = dsChecklist.Tables[0].Rows.Count,
CheckList = JsonConvert.SerializeObject(dsChecklist)
};
myDataReader.Close();
return(model);
}