public static DataTable GetDataByStoredProcedure(string spName)
{
DataTable dt = new DataTable();
SqlConnection cn = new SqlConnection(ConnectionString());
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
da.Fill(dt);
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message, "DALCommon.vb, GetDataUsingDataTable");
}
return(dt);
}
public static DataTable GetDataByQuery(string Query)
{
DataTable dt = new DataTable();
SqlConnection cn = new SqlConnection(ConnectionString());
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = Query;
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
da.Fill(dt);
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message, "DALCommon.vb, GetDataUsingDataTableByQuery");
}
return(dt);
}
public static int ExecuteNonQuery(string spName, SqlParameter[] paramtr)
{
int recCount = 0;
SqlConnection cn = new SqlConnection(ConnectionString());
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
for (int i = 0; i <= paramtr.Length - 1; i++)
{
cmd.Parameters.Add(paramtr[i]);
}
try
{
if ((cn.State == ConnectionState.Closed))
{
cn.Open();
}
recCount = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message, "DALCommon.vb, ExecuteNonQuery");
}
finally
{
cmd = null;
if ((cn.State == ConnectionState.Open))
{
cn.Close();
}
}
return(recCount);
}
public static int SendEmail(string ToEmail, string fromEmail, string Subject, string Body, string SMTP, string Username, string Password)
{
try
{
System.Net.Mail.MailMessage mailMessage = new System.Net.Mail.MailMessage(new MailAddress(fromEmail), new MailAddress(ToEmail));
System.Net.NetworkCredential SmtpUser = new System.Net.NetworkCredential(Username, Password);
StringBuilder emailHeader = new StringBuilder();
StringBuilder emailFooter = new StringBuilder();
//message body and subject property
mailMessage.Subject = Subject;
mailMessage.Body = emailHeader.ToString() + Body + emailFooter.ToString();
mailMessage.IsBodyHtml = true;
//create smtp client
SmtpClient smtpMail = new SmtpClient();
//assign smtp properties
smtpMail.Host = SMTP;
smtpMail.Port = 587;
smtpMail.EnableSsl = true;
smtpMail.DeliveryMethod = SmtpDeliveryMethod.Network;
smtpMail.UseDefaultCredentials = false;
smtpMail.Credentials = SmtpUser;
//send mail
smtpMail.Send(mailMessage);
return(1);
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message + "SendEmail");
return(0);
}
}
public static int ExecuteTransaction(string spName, SqlCommand cmd, SqlParameter[] paramtr)
{
int intCount = 0;
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
for (int i = 0; i <= paramtr.Length - 1; i++)
{
cmd.Parameters.Add(paramtr[i]);
}
try
{
intCount = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message, "DALCommon.vb, ExecuteTransaction");
}
return(intCount);
}
//Get Repor Info Data Table
public static string GetReportFileNamePath(int ReportId)
{
string reportFileName = "";
try
{
DataTable tblReportsList = new DataTable();
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@ReportId", ReportId);
tblReportsList = DALCommon.GetDataUsingDataTable("[sp_Admin_GetReportInfoByReportId]", param);
if (tblReportsList.Rows.Count > 0)
{
reportFileName = Convert.ToString(tblReportsList.Rows[0]["ReportFilePathName"]);
}
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message, "GetReportListDropdown, DALCommonFormData");
}
return(reportFileName);
}
public int InsertStudentMonthlyFeeInsertTransaction(DataTable studentList, ModelStudentFee objModelStudentFee, int FeeMonth, decimal AddedBy)
{
//Initialization
conn = new SqlConnection(ConnectionString());
strErrorMsg = string.Empty;
intStatus = 0;
int totalStudent = studentList.Rows.Count;
int updatedTotalRecord = 0;
try
{
//Open Connection
if ((conn.State == System.Data.ConnectionState.Closed))
{
conn.Open();
}
//Begin Transaction
sqlTrans = conn.BeginTransaction();
//Initialize Command
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = sqlTrans;
//Update Transaction Response
foreach (DataRow aStudent in studentList.Rows)
{
SqlParameter[] paramStudentFee = new SqlParameter[12];
paramStudentFee[0] = new SqlParameter("@StudentId", aStudent["StudentId"]);
paramStudentFee[1] = new SqlParameter("@ComputerCode", aStudent["ComputerCode"]);
paramStudentFee[2] = new SqlParameter("@FeeMonth", FeeMonth);
paramStudentFee[3] = new SqlParameter("@MonthlyFee", aStudent["MonthlyFee"]);
paramStudentFee[4] = new SqlParameter("@AdmissionFee", objModelStudentFee.AdmissionFee);
paramStudentFee[5] = new SqlParameter("@Admission", objModelStudentFee.Admission);
paramStudentFee[6] = new SqlParameter("@ReAdmissionFee", objModelStudentFee.ReAdmissionFee);
paramStudentFee[7] = new SqlParameter("@RegistrationFee", objModelStudentFee.RegistrationFee);
paramStudentFee[8] = new SqlParameter("@ComputerFee", objModelStudentFee.ComputerFee);
paramStudentFee[9] = new SqlParameter("@Fine", objModelStudentFee.Fine);
paramStudentFee[10] = new SqlParameter("@ExamFee", objModelStudentFee.ExamFee);
paramStudentFee[11] = new SqlParameter("@GeneratorFee", objModelStudentFee.GeneratorFee);
intStatus = DALCommon.ExecuteTransaction("[sp_Admin_InsertStudentMonthlyFeeBulkStudentId]", cmd, paramStudentFee);
if (intStatus >= 1)
{
updatedTotalRecord = updatedTotalRecord + 1;
}
else
{
DALUtility.ErrorLogBulkFeeInsertion(aStudent["StudentId"].ToString(), aStudent["MonthlyFee"].ToString(), "Bulk Fee noy Insert", AddedBy.ToString());
}
}
if (totalStudent == updatedTotalRecord)
{
//Commit Transaction
sqlTrans.Commit();
intStatus = 1;
}
else if (intStatus == -1)
{
strErrorMsg = "Error Occured while inserting record";
}
else if (intStatus == -2)
{
strErrorMsg = "Fee already exists for this month";
}
else if (intStatus == -3)
{
strErrorMsg = "Error occured while generation Serial Number index";
}
else if (intStatus == -4)
{
strErrorMsg = "Student stucked off";
}
//If Error Field Is Not Empty
if ((!string.IsNullOrEmpty(strErrorMsg)))
{
DALUtility.ErrorLog(strErrorMsg, "BulkFeeInsertionTransaction.cs, InsertStudentMonthlyFeeInsertTransaction");
}
}
catch (Exception ex)
{
sqlTrans.Rollback();
DALUtility.ErrorLog(ex.Message, "BulkFeeInsertionTransaction.cs, InsertStudentMonthlyFeeInsertTransaction");
}
finally
{
if ((conn.State == System.Data.ConnectionState.Open))
{
conn.Close();
}
cmd = null;
sqlTrans = null;
}
return(intStatus);
}
public int InsertUnPaidMonthlyFeeVoucherTransaction(string VoucherDueDate, DataTable studentList, decimal AddedBy)
{
//Initialization
conn = new SqlConnection(ConnectionString());
strErrorMsg = string.Empty;
intStatus = 0;
int totalStudent = studentList.Rows.Count;
int updatedTotalRecord = 0;
int ResultStatus = 0;
try
{
//Open Connection
if ((conn.State == System.Data.ConnectionState.Closed))
{
conn.Open();
}
//Begin Transaction
sqlTrans = conn.BeginTransaction();
//Initialize Command
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = sqlTrans;
//Update Transaction Response
foreach (DataRow aStudent in studentList.Rows)
{
double voucherId = 0;
SqlParameter[] paramStudentFee = new SqlParameter[3];
paramStudentFee[0] = new SqlParameter("@StudentId", aStudent["StudentId"]);
paramStudentFee[1] = new SqlParameter("@VoucherDueDate", VoucherDueDate);
paramStudentFee[2] = new SqlParameter("@AddedBy", AddedBy);
voucherId = DALCommon.ExecuteTransactionReturnIdentity("[sp_Admin_InsertStudentFeeVoucher]", cmd, paramStudentFee);
if (voucherId >= 1)
{
DataTable tblUnPaidFeeMonth = new DataTable();
SqlParameter[] paramUnPaidFeeMonth = new SqlParameter[3];
paramUnPaidFeeMonth[0] = new SqlParameter("@VoucherId", voucherId);
paramUnPaidFeeMonth[1] = new SqlParameter("@StudentId ", aStudent["StudentId"]);
paramUnPaidFeeMonth[2] = new SqlParameter("@AddedBy ", AddedBy);
//Bulk Monthly Fee Voucher Creating
ResultStatus = DALCommon.ExecuteTransaction("[sp_Admin_InsertFeeVoucherMonthsDataByVoucherID]", cmd, paramUnPaidFeeMonth);
if (ResultStatus > 0)
{
updatedTotalRecord = updatedTotalRecord + 1;
}
else
{
intStatus = -2;
strErrorMsg = "Error Occured while inserting record";
DALUtility.ErrorLog("Error occured inserting Monthly Fee Voucher record", "StudentFeeVoucherTransaction.cs, InsertUnPaidMonthlyFeeVoucherTransaction");
}
}
else
{
intStatus = -3;
strErrorMsg = "Error Occured while inserting record";
DALUtility.ErrorLog("Error occured generating VoucherId", "StudentFeeVoucherTransaction.cs, InsertUnPaidMonthlyFeeVoucherTransaction");
}
}
//If Error Field Is Not Empty
if ((!string.IsNullOrEmpty(strErrorMsg)))
{
DALUtility.ErrorLog(strErrorMsg, "BulkFeeInsertionTransaction.cs, InsertStudentMonthlyFeeInsertTransaction");
}
if (updatedTotalRecord == totalStudent)
{
sqlTrans.Commit();
intStatus = 1;
}
}
catch (Exception ex)
{
sqlTrans.Rollback();
DALUtility.ErrorLog(ex.Message, "StudentFeeVoucherTransaction.cs, InsertUnPaidMonthlyFeeVoucherTransaction");
}
finally
{
if ((conn.State == System.Data.ConnectionState.Open))
{
conn.Close();
}
cmd = null;
sqlTrans = null;
}
return(intStatus);
}
public int InsertFeeVoucherFeePaidTransaction(string VoucherPaidDate, double FeeVoucherId, DataTable FeeIdList, double AddedBy)
{
//Initialization
conn = new SqlConnection(ConnectionString());
strErrorMsg = string.Empty;
intStatus = 0;
int totalFeeId = FeeIdList.Rows.Count;
int updatedTotalRecord = 0;
int ResultStatus = 0;
try
{
//Open Connection
if ((conn.State == System.Data.ConnectionState.Closed))
{
conn.Open();
}
//Begin Transaction
sqlTrans = conn.BeginTransaction();
//Initialize Command
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = sqlTrans;
//Update Transaction Response
foreach (DataRow aFeeId in FeeIdList.Rows)
{
int ResultRecord = 0;
SqlParameter[] paramStudentFee = new SqlParameter[3];
paramStudentFee[0] = new SqlParameter("@FeeId", aFeeId["FeeId"]);
paramStudentFee[1] = new SqlParameter("@FeeDate", VoucherPaidDate);
paramStudentFee[2] = new SqlParameter("@AddedBy", AddedBy);
ResultRecord = DALCommon.ExecuteTransaction("[sp_Admin_UpdateStudentFeePaidMarkedByFeeId]", cmd, paramStudentFee);
if (ResultRecord > 0)
{
updatedTotalRecord = updatedTotalRecord + 1;
}
else
{
intStatus = -2;
strErrorMsg = "Error occured while marking fee month paid with fee Id";
}
}
if (updatedTotalRecord == totalFeeId)
{
SqlParameter[] paramFeeVoucher = new SqlParameter[1];
paramFeeVoucher[0] = new SqlParameter("@VoucherId", FeeVoucherId);
//Mark voucher paid
ResultStatus = DALCommon.ExecuteTransaction("[sp_Admin_UpdateStudentFeeVoucherPaidMarkedByVoucherId]", cmd, paramFeeVoucher);
if (ResultStatus > 0)
{
sqlTrans.Commit();
intStatus = 1;
}
}
else
{
intStatus = -3;
strErrorMsg = "Error Occured while marking fee voucher paid";
}
//If Error Field Is Not Empty
if ((!string.IsNullOrEmpty(strErrorMsg)))
{
DALUtility.ErrorLog(strErrorMsg, "BulkFeeInsertionTransaction.cs, InsertStudentMonthlyFeeInsertTransaction, InsertFeeVoucherFeePaidTransaction");
}
}
catch (Exception ex)
{
sqlTrans.Rollback();
DALUtility.ErrorLog(ex.Message, "StudentFeeVoucherTransaction.cs, InsertUnPaidMonthlyFeeVoucherTransaction");
}
finally
{
if ((conn.State == System.Data.ConnectionState.Open))
{
conn.Close();
}
cmd = null;
sqlTrans = null;
}
return(intStatus);
}