public List <ModuleItem> GetAllModuleItem()
{
try
{
SqlCommand scom = new SqlCommand("SELECT * FROM dbo.TblModuleItem", DbConnector.Connect());
scom.CommandType = CommandType.Text;
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(scom);
adapter.Fill(ds);
List <ModuleItem> lstModuleItem = new List <ModuleItem>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
ModuleItem moduleItem = new ModuleItem();
moduleItem.Id = Convert.ToInt32(ds.Tables[0].Rows[i]["Id"]);
moduleItem.Code = Convert.ToString(ds.Tables[0].Rows[i]["Code"]);
moduleItem.Name = Convert.ToString(ds.Tables[0].Rows[i]["Name"]);
moduleItem.Active = Convert.ToBoolean(ds.Tables[0].Rows[i]["Active"]);
moduleItem.ModuleId = Convert.ToInt32(ds.Tables[0].Rows[i]["ModuleId"]);
lstModuleItem.Add(moduleItem);
}
return(lstModuleItem);
}
catch (Exception ex)
{
return(null);
}
return(null);
}
public int Create(ImageFile image)
{
try
{
SqlCommand scom = new SqlCommand(ProcedureConstants.Sp_AddImageFile, DbConnector.Connect());
scom.CommandType = CommandType.StoredProcedure;
scom.Parameters.AddWithValue("@FileName", image.FileName);
scom.Parameters.AddWithValue("@FilePath", image.FilePath);
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(scom);
adapter.Fill(ds);
if (ds == null || ds.Tables.Count != 1 || ds.Tables[0].Rows.Count != 1 || ds.Tables[0].Rows[0][0].ToString() == "1")
{
return(1);
}
else
{
return(Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString()));
}
}
catch (Exception ex)
{
return(1);
}
return(1);
}
public List <Gender> GetAllGender()
{
try
{
SqlCommand scom = new SqlCommand("SELECT * FROM tblGender", DbConnector.Connect());
scom.CommandType = CommandType.Text;
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(scom);
adapter.Fill(ds);
List <Gender> lstGender = new List <Gender>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Gender gender = new Gender();
gender.GenderID = Convert.ToInt32(ds.Tables[0].Rows[i]["Gender_Id"]);
gender.GenderCode = Convert.ToString(ds.Tables[0].Rows[i]["Gender_Code"]);
gender.GenderName = Convert.ToString(ds.Tables[0].Rows[i]["Gender_Name"]);
lstGender.Add(gender);
}
return(lstGender);
}
catch (Exception ex)
{
return(null);
}
return(null);
}
public static void GetPermissionId(string description, out string errorMessage)
{
errorMessage = null;
DbConnector testConnector = new DbConnector();
testConnector.Connect();
var cmd = new NpgsqlCommand();
try
{
cmd.Connection = testConnector.Connection;
cmd.CommandText = "SELECT priority_id, description, created FROM priority WHERE priority_id = 1";
//TODO modify following examples
//cmd.CommandText = "SELECT priority_id FROM priority WHERE description = (@p)";
//cmd.Parameters.AddWithValue("p", description);
var reader = cmd.ExecuteReader();
Console.WriteLine("\nExporting priority table entries: \n");
while (reader.Read())
{
Console.WriteLine("{0} | {1} | {2}", reader.GetDataTypeName(0),
reader.GetDataTypeName(1), reader.GetDataTypeName(2));
}
}
catch (Exception e)
{
Console.WriteLine(e);
errorMessage = e.ToString();
throw;
}
testConnector.Disconnect();
}
protected override async Task <IDbConnection> CreateConnection(bool isReadOnly = false)
{
var connection = await DbConnector.Connect(DbFilePath, false, false, 10000).ConfigureAwait(false);
connection.RunQueries(new[]
{
"pragma temp_store = memory"
}, Logger);
return(connection);
}
private void OnDoWork(object sender, DoWorkEventArgs e)
{
_dbConnector = new DbConnector();
_dbConnector.Connect();
BackgroundWorker worker = (BackgroundWorker)sender;
while (!worker.CancellationPending)
{
// Just cause an invoke
worker.ReportProgress(0, null);
Thread.Sleep(120000);
}
}
public ReturnMessage Import(List <Patient> lstPatient)
{
try
{
SqlCommand scom = new SqlCommand("", DbConnector.Connect());
scom.CommandType = CommandType.StoredProcedure;
ListToDataTable lsttodt = new ListToDataTable();
DataTable dtPatient = lsttodt.ToDataTable(lstPatient);
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(DbConnector.Connect()))
{
bulkCopy.ColumnMappings.Add("PatientCode", "Patient_Code");
bulkCopy.ColumnMappings.Add("PatientName", "Patient_Name");
bulkCopy.ColumnMappings.Add("GenderID", "GenderID");
bulkCopy.ColumnMappings.Add("Address", "Address");
bulkCopy.ColumnMappings.Add("Created_By", "Created_By");
bulkCopy.ColumnMappings.Add("Created_On", "Created_On");
bulkCopy.ColumnMappings.Add("Modified_By", "Modified_By");
bulkCopy.ColumnMappings.Add("Modified_On", "Modified_On");
bulkCopy.ColumnMappings.Add("Active", "Active");
bulkCopy.DestinationTableName = "tblPatient";
bulkCopy.WriteToServer(dtPatient);
}
return(new ReturnMessage()
{
RetCode = 1, RetMessage = "Success!"
});
}
catch (Exception ex)
{
return(new ReturnMessage()
{
RetCode = 0, RetMessage = ex.Message
});
}
return(new ReturnMessage()
{
RetCode = 0, RetMessage = "Unknown error in estimate plan creating!"
});
}
public List <Patient> GetAllPatient()
{
try
{
SqlCommand scom = new SqlCommand("SELECT * FROM VW_GetAllPatient", DbConnector.Connect());
scom.CommandType = CommandType.Text;
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(scom);
adapter.Fill(ds);
List <Patient> lstPatient = new List <Patient>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Patient patient = new Patient();
patient.PatientID = Convert.ToInt32(ds.Tables[0].Rows[i]["Patient_Id"]);
patient.PatientCode = Convert.ToString(ds.Tables[0].Rows[i]["Patient_Code"]);
patient.PatientName = Convert.ToString(ds.Tables[0].Rows[i]["Patient_Name"]);
patient.GenderID = Convert.ToInt32(ds.Tables[0].Rows[i]["GenderID"]);
patient.GenderName = Convert.ToString(ds.Tables[0].Rows[i]["Gender_Name"]);
if (ds.Tables[0].Rows[i]["DateOfBirth"] != DBNull.Value)
{
patient.DOB = Convert.ToDateTime(ds.Tables[0].Rows[i]["DateOfBirth"]);
}
patient.Address = Convert.ToString(ds.Tables[0].Rows[i]["Address"]);
if (ds.Tables[0].Rows[i]["FileID"] != DBNull.Value)
{
patient.FileID = Convert.ToInt32(ds.Tables[0].Rows[i]["FileID"]);
}
patient.FileName = Convert.ToString(ds.Tables[0].Rows[i]["FileName"]);
patient.FilePath = Convert.ToString(ds.Tables[0].Rows[i]["FilePath"]);
lstPatient.Add(patient);
}
return(lstPatient);
}
catch (Exception ex)
{
return(null);
}
return(null);
}
public int EditPatient(Patient patient)
{
try
{
SqlCommand scom = new SqlCommand(ProcedureConstants.Sp_EditPatient, DbConnector.Connect());
scom.CommandType = CommandType.StoredProcedure;
scom.Parameters.AddWithValue("@Patient_Id", patient.PatientID);
scom.Parameters.AddWithValue("@Patient_Code", patient.PatientCode);
scom.Parameters.AddWithValue("@Patient_Name", patient.PatientName);
scom.Parameters.AddWithValue("@DOB", patient.DOB);
scom.Parameters.AddWithValue("@GenderID", patient.GenderID);
scom.Parameters.AddWithValue("@Address", patient.Address);
scom.Parameters.AddWithValue("@FileID", patient.FileID);
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(scom);
adapter.Fill(ds);
if (ds == null || ds.Tables.Count != 1 || ds.Tables[0].Rows.Count != 1 || ds.Tables[0].Rows[0][0].ToString() == "1")
{
return(1);
}
else if (ds.Tables[0].Rows[0][0].ToString() == "2")
{
return(2);
}
else
{
return(0);
}
}
catch (Exception ex)
{
return(1);
}
return(1);
}
public List <ModuleItem> GetModuleItemByRoleId(int roleId)
{
try
{
// SqlCommand scom = new SqlCommand("SELECT * FROM Common.TblModuleItem", DbConnector.Connect());
SqlCommand scom = new SqlCommand(ProcedureConstants.Sp_GetModuleItemByRoleId, DbConnector.Connect());
scom.CommandType = CommandType.StoredProcedure;
scom.Parameters.AddWithValue("@RoleId", roleId);
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(scom);
adapter.Fill(ds);
List <ModuleItem> lstModuleItem = new List <ModuleItem>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
ModuleItem moduleItem = new ModuleItem();
moduleItem.Id = Convert.ToInt32(ds.Tables[0].Rows[i]["Id"]);
moduleItem.Code = Convert.ToString(ds.Tables[0].Rows[i]["Code"]);
moduleItem.Name = Convert.ToString(ds.Tables[0].Rows[i]["Name"]);
moduleItem.Active = Convert.ToBoolean(ds.Tables[0].Rows[i]["Active"]);
moduleItem.ModuleId = Convert.ToInt32(ds.Tables[0].Rows[i]["ModuleId"]);
moduleItem.Allowed = Convert.ToBoolean(ds.Tables[0].Rows[i]["Allowed"]);
moduleItem.ModuleName = Convert.ToString(ds.Tables[0].Rows[i]["ModuleName"]);
lstModuleItem.Add(moduleItem);
}
return(lstModuleItem);
}
catch (Exception ex)
{
return(null);
}
return(null);
}
public int GetAllImageFile()
{
try
{
SqlCommand scom = new SqlCommand("SELECT TOP(1) * From tblImageFile Order By FileID DESC", DbConnector.Connect());
scom.CommandType = CommandType.Text;
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(scom);
adapter.Fill(ds);
int FileID = Convert.ToInt32(ds.Tables[0].Rows[0]["FileID"]);
return(FileID);
}
catch (Exception ex)
{
return(0);
}
}
public List <Attachments> GetAllTmpAttachmentsByIssueId(int issueId)
{
try
{
SqlCommand scom = new SqlCommand("SELECT * FROM Tmp.TblAttachments WHERE IssueId = " + issueId + " AND IsSaved =" + 1, DbConnector.Connect());
scom.CommandType = CommandType.Text;
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(scom);
adapter.Fill(ds);
List <Attachments> lstAttachments = new List <Attachments>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Attachments attachment = new Attachments();
attachment.Id = Convert.ToInt32(ds.Tables[0].Rows[i]["Id"]);
attachment.FileCode = Convert.ToString(ds.Tables[0].Rows[i]["FileCode"]);
attachment.FileName = Convert.ToString(ds.Tables[0].Rows[i]["FileName"]);
attachment.FileType = Convert.ToString(ds.Tables[0].Rows[i]["FileType"]);
attachment.IsSaved = Convert.ToBoolean(ds.Tables[0].Rows[i]["IsSaved"]);
attachment.IssueId = Convert.ToInt32(ds.Tables[0].Rows[i]["IssueId"]);
attachment.AttachedBy = Convert.ToInt32(ds.Tables[0].Rows[i]["AttachedBy"]);
attachment.AttachedOn = Convert.ToDateTime(ds.Tables[0].Rows[i]["AttachedOn"]);
lstAttachments.Add(attachment);
}
return(lstAttachments);
}
catch (Exception ex)
{
return(null);
}
return(null);
}
public List <FtpServer> GetAllFtpServerList()
{
try
{
SqlCommand scom = new SqlCommand("SELECT * FROM [dbo].[TblFTPServer]", DbConnector.Connect());
scom.CommandType = CommandType.Text;
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(scom);
da.Fill(ds);
List <FtpServer> lstFtpServer = new List <FtpServer>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
FtpServer ftpServer = new FtpServer();
ftpServer.Id = Convert.ToInt32(ds.Tables[0].Rows[i]["Id"]);
ftpServer.ServerAddress = Convert.ToString(ds.Tables[0].Rows[i]["ServerAddress"]);
ftpServer.UserName = Convert.ToString(ds.Tables[0].Rows[i]["UserName"]);
ftpServer.Password = Convert.ToString(ds.Tables[0].Rows[i]["Password"]);
ftpServer.CreatedBy = Convert.ToInt32(ds.Tables[0].Rows[i]["CreatedBy"]);
ftpServer.CreatedOn = Convert.ToDateTime(ds.Tables[0].Rows[i]["CreatedOn"]);
ftpServer.ModifiedBy = Convert.ToInt32(ds.Tables[0].Rows[i]["ModifiedBy"]);
ftpServer.ModifiedOn = Convert.ToDateTime(ds.Tables[0].Rows[i]["ModifiedOn"]);
ftpServer.Active = Convert.ToBoolean(ds.Tables[0].Rows[i]["Active"]);
lstFtpServer.Add(ftpServer);
}
return(lstFtpServer);
}
catch (Exception ex)
{
return(null);
}
return(null);
}
public void Delete(Attachments attachments)
{
SqlCommand scom = new SqlCommand("DELETE FROM Tmp.TblAttachments WHERE Id=" + attachments.Id + " AND FileCode='" + attachments.FileCode + "' AND AttachedBy =" + attachments.AttachedBy, DbConnector.Connect());
scom.CommandType = CommandType.Text;
scom.ExecuteNonQuery();
}
public ReturnMessage Create(Attachments attachments)
{
try
{
SqlCommand scom = new SqlCommand(ProcedureConstants.SP_InsertAttachments, DbConnector.Connect());
scom.CommandType = CommandType.StoredProcedure;
scom.Parameters.AddWithValue("@FileCode", attachments.FileCode);
scom.Parameters.AddWithValue("@FileName", attachments.FileName);
scom.Parameters.AddWithValue("@FileType", attachments.FileType);
scom.Parameters.AddWithValue("@UserId", attachments.AttachedBy);
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(scom);
adapter.Fill(ds);
if (ds == null || ds.Tables.Count != 1 || ds.Tables[0].Rows.Count != 1 || Convert.ToInt32(ds.Tables[0].Rows[0][0]) < 0)
{
return(new ReturnMessage()
{
RetCode = 0, RetMessage = "Unknown error!"
});
}
else if (Convert.ToInt32(ds.Tables[0].Rows[0][0]) < 0)
{
return(new ReturnMessage()
{
RetCode = 0, RetMessage = Convert.ToString(ds.Tables[0].Rows[0][1])
});
}
else
{
return(new ReturnMessage()
{
RetCode = Convert.ToInt32(ds.Tables[0].Rows[0][0]), RetMessage = Convert.ToString(ds.Tables[0].Rows[0][1])
});
}
}
catch (Exception ex)
{
return(new ReturnMessage()
{
RetCode = 0, RetMessage = ex.Message
});
}
return(new ReturnMessage()
{
RetCode = 0, RetMessage = "Unknown error in attachments inserting!"
});
}
