public static InstallmentFee GetInstallmentFeeInfo(string studentId)
{
string connectionString = DataManager.OraConnString();
SqlConnection sqlCon = new SqlConnection(connectionString);
try
{
sqlCon.Open();
string selectQuery = @"SELECT [Serial],[StudentId],[TotalAmt],convert(varchar,PayDate,103) [PayDate],[MonthInterval],[InsQty],[AdmissionFee] FROM [InstallmentMst] WHERE [StudentId]='" + studentId + "'";
SqlCommand cmd = new SqlCommand(selectQuery, sqlCon);
SqlDataReader reader = cmd.ExecuteReader();
InstallmentFee aInstallmentFeeObj = new InstallmentFee();
if (reader.HasRows)
{
while (reader.Read())
{
aInstallmentFeeObj.Id = reader[0].ToString();
aInstallmentFeeObj.StudentId = reader[1].ToString();
aInstallmentFeeObj.TotalAmt = reader[2].ToString();
aInstallmentFeeObj.PayDate = reader[3].ToString();
aInstallmentFeeObj.MonthInterval = reader[4].ToString();
aInstallmentFeeObj.InsQty = reader[5].ToString();
aInstallmentFeeObj.AdmissionFee = reader[6].ToString();
}
}
return(aInstallmentFeeObj);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (sqlCon.State == ConnectionState.Open)
{
sqlCon.Close();
}
}
}
public void CreateInstallment(DataTable dt1, InstallmentFee Ins)
{
try
{
connection.Open();
transaction = connection.BeginTransaction();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.Transaction = transaction;
SqlCommand command1 = new SqlCommand();
command1.Connection = connection;
command1.Transaction = transaction;
command1.CommandText = @"SELECT COUNT(*) FROM [InstallmentMst] where [StudentId]='" + Ins.StudentId + "'";
int COUNT = Convert.ToInt32(command1.ExecuteScalar());
if (COUNT > 0)
{
command.CommandText = @"UPDATE [InstallmentMst]
SET [Serial] ='" + Ins.Id + "' ,[StudentId] = '" + Ins.StudentId + "' ,[TotalAmt] = '" + Ins.TotalAmt + "' ,[PayDate] = convert(date,'" + Ins.PayDate + "',103),[MonthInterval] = '" + Ins.MonthInterval + "' ,[InsQty] ='" + Ins.InsQty + "' ,[AdmissionFee] = '" + Ins.AdmissionFee + "' ,[UpdateDate] =GETDATE(),[UpdateUser] = '" + Ins.Loginby + "' WHERE [StudentId] = '" + Ins.StudentId + "'";
command.ExecuteNonQuery();
command.CommandText = @"SELECT ID FROM [InstallmentMst] where StudentId='" + Ins.StudentId + "'";
int ID = Convert.ToInt32(command.ExecuteScalar());
command1.CommandText = @"DELETE FROM [InstallmentDtl] WHERE MstId='" + ID + "'";
command1.ExecuteNonQuery();
foreach (DataRow dr in dt1.Rows)
{
command1.CommandText = @"INSERT INTO [InstallmentDtl]
(InstallmentId,[MstId] ,[InstallAmt] ,[InstallDate])
VALUES ('" + dr["Installment_Serial"].ToString() + "','" + ID + "','" + Convert.ToDouble(dr["Installment_Amount"].ToString()) + "',convert(date,'" + dr["Installment_Date"].ToString() + "',103))";
command1.ExecuteNonQuery();
}
}
else
{
command1.CommandText = @"INSERT INTO [InstallmentMst]
(Serial,[StudentId],[TotalAmt],[PayDate],[MonthInterval],[InsQty],[AdmissionFee],[EntryUser],[EntryDate])
VALUES ((SELECT isnull(max([Serial]),0) FROM [InstallmentMst])+1,'" + Ins.StudentId + "','" + Ins.TotalAmt + "',convert(date,'" + Ins.PayDate + "',103),'" + Ins.MonthInterval + "','" + Ins.InsQty + "','" + Ins.AdmissionFee + "','" + Ins.Loginby + "',GETDATE())";
command1.ExecuteNonQuery();
command.CommandText = @"SELECT [Id] FROM [InstallmentMst] order by [Id] desc";
int MstID = Convert.ToInt32(command.ExecuteScalar());
foreach (DataRow dr in dt1.Rows)
{
command1.CommandText = @"INSERT INTO [InstallmentDtl]
(InstallmentId,[MstId] ,[InstallAmt] ,[InstallDate])
VALUES ('" + dr["Installment_Serial"].ToString() + "','" + MstID + "','" + Convert.ToDouble(dr["Installment_Amount"].ToString()) + "',convert(date,'" + dr["Installment_Date"].ToString() + "',103))";
command1.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw new Exception(ex.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
