protected void AddParameter(string name, object value, ParameterDirection direction)
{
SqlParameter parameter = new SqlParameter(name, value);
parameter.Direction = direction;
DBParameters.Add(parameter);
}
protected DataSet ExecuteDataSet(string spName)
{
try
{
DataSet recordsDs = new DataSet();
_command = _sqlconnection.CreateCommand();
_command.CommandTimeout = ConnectionTimeout;
_command.CommandText = spName;
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddRange(DBParameters.ToArray());
if (_adapter == null)
{
_adapter = new SqlDataAdapter();
}
_adapter.SelectCommand = _command;
_adapter.Fill(recordsDs);
return(recordsDs);
}
catch (Exception ex)
{
throw ex;
}
}
public List <EmployeeAddress> GetEmployee(int id)
{
DBParameters.Clear();
AddParameter("@empId", id);
DataSet table = ExecuteDataSet("sp_getEmployee");
List <EmployeeAddress> employee = table.Tables[0].AsEnumerable().Select(emp => new EmployeeAddress()
{
EmpId = emp.Field <int>("emp_id"),
Gender = emp.Field <string>("gender"),
EmpName = emp.Field <string>("emp_name"),
Manager = emp.Field <string>("Manager"),
Department = emp.Field <int>("depId"),
Email = emp.Field <string>("Emailid"),
DateOfBirth = emp.Field <DateTime>("DateOfBirth"),
JoinDate = emp.Field <DateTime>("dateofjoin"),
PhoneNumber = emp.Field <Int64>("PhoneNumber").ToString(),
Address = emp.Field <string>("address"),
PinCode = emp.Field <int>("pincode").ToString(),
State = emp.Field <string>("state"),
Country = emp.Field <string>("country")
}).ToList();
return(employee);
}
private static DataTable GetBranchList(Int16 nCourseID)
{
DataTable _dt = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@nCourseID", nCourseID, ParameterDirection.Input, SqlDbType.BigInt);
_DataAccess.Retrive("SP_SVIT_GetBranchDetails", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
}
catch (Exception)
{
if (_DataAccess != null) { _DataAccess.RollBack(); _DataAccess.CloseConnection(false); }
}
finally
{
if (_DBParameters != null) { _DBParameters.Dispose(); }
}
return _dt;
}
protected bool SaveData(string spName)
{
try
{
if (_sqlconnection == null)
{
OpenConnection();
}
_command = _sqlconnection.CreateCommand();
_command.CommandTimeout = ConnectionTimeout;
_command.CommandText = spName;
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddRange(DBParameters.ToArray());
int result = _command.ExecuteNonQuery();
if (result > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <Aircraft> GetAircraftInformation(Int64 nAircraftType_ID, string sTask)
{
DataTable _dt = null;
List <Aircraft> lstAircraft = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@AircraftType_ID", nAircraftType_ID, ParameterDirection.Input, SqlDbType.BigInt);
_DBParameters.Add("@TASK", sTask, ParameterDirection.Input, SqlDbType.VarChar);
_DataAccess.Retrive("SP_GET_AIRCRAFT_DATA", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
if (_dt != null && _dt.Rows.Count > 0)
{
lstAircraft = new List <Aircraft>();
for (int i = 0; i < _dt.Rows.Count; i++)
{
Aircraft oAircraft = new Aircraft();
oAircraft.SRNO = i + 1;
oAircraft.AircraftTypeID = Convert.ToInt64(_dt.Rows[i]["AircraftType_ID"]);
oAircraft.AircraftType = Convert.ToString(_dt.Rows[i]["AircraftType"]);
oAircraft.AircraftCode = Convert.ToString(_dt.Rows[i]["AircraftCode"]);
lstAircraft.Add(oAircraft);
oAircraft.Dispose();
oAircraft = null;
}
//foreach (DataRow dr in _dt.Rows)
//{
// Aircraft oAircraft = new Aircraft();
// oAircraft.AircraftTypeID = Convert.ToInt64(dr["AircraftType_ID"]);
// oAircraft.AircraftType = Convert.ToString(dr["AircraftType"]);
// oAircraft.AircraftCode = Convert.ToString(dr["AircraftCode"]);
// lstAircraft.Add(oAircraft);
// oAircraft.Dispose();
// oAircraft = null;
//}
}
}
catch (Exception)
{
if (_DataAccess != null)
{
_DataAccess.RollBack(); _DataAccess.CloseConnection(false);
}
}
finally
{
if (_DBParameters != null)
{
_DBParameters.Dispose();
}
}
return(lstAircraft);
}
public void Retrive(string StoredProcedureName, DBParameters Parameters, out DataSet _result)
{
//DataSet _result = new DataSet();
int _counter = 0;
SqlCommand _sqlcommand = new SqlCommand();
SqlParameter osqlParameter;
try
{
_sqlcommand.CommandType = CommandType.StoredProcedure;
_sqlcommand.CommandText = StoredProcedureName;
_sqlcommand.Connection = _con;
if (_WithTrn == true)
{
_sqlcommand.Transaction = _Trn;
}
for (_counter = 0; _counter <= Parameters.Count - 1; _counter++)
{
osqlParameter = new SqlParameter();
osqlParameter.ParameterName = Parameters[_counter].ParameterName;
osqlParameter.SqlDbType = Parameters[_counter].DataType;
osqlParameter.Direction = Parameters[_counter].ParameterDirection;
osqlParameter.Value = Parameters[_counter].Value;
if (Parameters[_counter].Size != null)
{
if (Parameters[_counter].Size != 0)
{
osqlParameter.Size = Parameters[_counter].Size;
}
}
_sqlcommand.Parameters.Add(osqlParameter);
osqlParameter = null;
}
SqlDataAdapter _dataAdapter = new SqlDataAdapter(_sqlcommand);
DataSet _resultinternal = new DataSet();
//_dataAdapter.Fill(_result);
_dataAdapter.Fill(_resultinternal);
_dataAdapter.Dispose();
_result = _resultinternal;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (_sqlcommand != null)
{
_sqlcommand.Dispose();
}
}
//return _result;
}
protected void AddParameter(string name, ParameterDirection direction)
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = name;
parameter.Direction = direction;
parameter.Size = -1;
DBParameters.Add(parameter);
}
protected void AddParameter(string name, object value, ParameterDirection direction, byte precision, byte scale)
{
SqlParameter parameter = new SqlParameter(name, value);
parameter.Direction = direction;
parameter.Scale = scale;
parameter.Precision = precision;
DBParameters.Add(parameter);
}
public int Execute(string StoredProcedureName, DBParameters Parameters)
{
int _result = 0;
int _counter = 0;
SqlCommand _sqlcommand = new SqlCommand();
SqlParameter osqlParameter;
try
{
_sqlcommand.CommandType = CommandType.StoredProcedure;
_sqlcommand.CommandText = StoredProcedureName;
_sqlcommand.Connection = _con;
if (_WithTrn == true)
{
_sqlcommand.Transaction = _Trn;
}
for (_counter = 0; _counter <= Parameters.Count - 1; _counter++)
{
osqlParameter = new SqlParameter();
osqlParameter.ParameterName = Parameters[_counter].ParameterName;
osqlParameter.SqlDbType = Parameters[_counter].DataType;
osqlParameter.Direction = Parameters[_counter].ParameterDirection;
osqlParameter.Value = Parameters[_counter].Value;
if (Parameters[_counter].Size != null)
{
if (Parameters[_counter].Size != 0)
{
osqlParameter.Size = Parameters[_counter].Size;
}
}
_sqlcommand.Parameters.Add(osqlParameter);
osqlParameter = null;
}
_result = _sqlcommand.ExecuteNonQuery();
}
catch (Exception ex)
{
if (_WithTrn == true)
{
_Trn.Rollback();
}
throw ex;
}
finally
{
if (_sqlcommand != null)
{
_sqlcommand.Dispose();
}
}
return _result;
}
public int Execute(string StoredProcedureName, DBParameters Parameters)
{
int _result = 0;
int _counter = 0;
SqlCommand _sqlcommand = new SqlCommand();
SqlParameter osqlParameter;
try
{
_sqlcommand.CommandType = CommandType.StoredProcedure;
_sqlcommand.CommandText = StoredProcedureName;
_sqlcommand.Connection = _con;
if (_WithTrn == true)
{
_sqlcommand.Transaction = _Trn;
}
for (_counter = 0; _counter <= Parameters.Count - 1; _counter++)
{
osqlParameter = new SqlParameter();
osqlParameter.ParameterName = Parameters[_counter].ParameterName;
osqlParameter.SqlDbType = Parameters[_counter].DataType;
osqlParameter.Direction = Parameters[_counter].ParameterDirection;
osqlParameter.Value = Parameters[_counter].Value;
if (Parameters[_counter].Size != null)
{
if (Parameters[_counter].Size != 0)
{
osqlParameter.Size = Parameters[_counter].Size;
}
}
_sqlcommand.Parameters.Add(osqlParameter);
osqlParameter = null;
}
_result = _sqlcommand.ExecuteNonQuery();
}
catch (Exception ex)
{
if (_WithTrn == true)
{
_Trn.Rollback();
}
throw ex;
}
finally
{
if (_sqlcommand != null)
{
_sqlcommand.Dispose();
}
}
return(_result);
}
public void CancelLeave(Leave leave)
{
DBParameters.Clear();
AddParameter("@emailId", leave.Email);
AddParameter("@startdate ", leave.FromDate);
AddParameter("@enddate", leave.ToDate);
ExecuteDataSet("sp_CancelLeave");
// ExecuteScalar("sp_CancelLeave");
}
public List <Company> GetCompanyInformation()
{
DataTable _dt = null;
List <Company> lstCompany = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
string sTask = "ONE";
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@Company_ID", 1, ParameterDirection.Input, SqlDbType.BigInt);
_DBParameters.Add("@TASK", sTask, ParameterDirection.Input, SqlDbType.VarChar);
_DataAccess.Retrive("SP_Get_Company_Master_Data", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
if (_dt != null && _dt.Rows.Count > 0)
{
lstCompany = new List <Company>();
foreach (DataRow dr in _dt.Rows)
{
Company oCompany = new Company();
oCompany.Company_ID = Convert.ToInt64(dr["Company_ID"]);
oCompany.Company_Name = Convert.ToString(dr["Company_Name"]);
oCompany.Billing_Address = Convert.ToString(dr["Billing_Address"]);
oCompany.GST_No = Convert.ToString(dr["GST_No"]);
oCompany.PAN_No = Convert.ToString(dr["PAN_No"]);
oCompany.HSN_Code = Convert.ToString(dr["HSN_Code"]);
oCompany.GST_Per = Convert.ToDouble(dr["GST_Per"]);
oCompany.Contact_No = Convert.ToString(dr["Contact_No"]);
oCompany.Email_ID = Convert.ToString(dr["Email_ID"]);
lstCompany.Add(oCompany);
oCompany.Dispose();
oCompany = null;
}
}
}
catch (Exception)
{
if (_DataAccess != null)
{
_DataAccess.RollBack(); _DataAccess.CloseConnection(false);
}
}
finally
{
if (_DBParameters != null)
{
_DBParameters.Dispose();
}
}
return(lstCompany);
}
public List <Department> GetDepartments()
{
DBParameters.Clear();
DataSet deplist = ExecuteDataSet("sp_GetAllDepartments");
List <Department> departments = deplist.Tables[0].AsEnumerable().Select(dep => new Department()
{
DepId = dep.Field <int>("depId"),
DepartmentName = dep.Field <string>("department")
}).ToList();
return(departments);
}
public bool IsValidUser(string sUserName, string sPassword, out User oUser)
{
bool bIsValidUser = false;
DataTable _dt = null;
oUser = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@UserName", sUserName, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@Password", sPassword, ParameterDirection.Input, SqlDbType.VarChar);
_DataAccess.Retrive("SP_USER_LOGIN", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
if (_dt != null && _dt.Rows.Count > 0)
{
if (Convert.ToInt64(_dt.Rows[0][0]) != -1)
{
bIsValidUser = true;
foreach (DataRow dr in _dt.Rows)
{
oUser = new User();
oUser.User_ID = Convert.ToInt64(dr["User_ID"]);
oUser.Role_ID = Convert.ToInt64(dr["Role_ID"]);
oUser.UserName = Convert.ToString(dr["UserName"]);
oUser.Role_Name = Convert.ToString(dr["Role_Name"]);
oUser.FirstName = Convert.ToString(dr["FirstName"]);
oUser.LastName = Convert.ToString(dr["LastName"]);
}
}
}
}
catch (Exception)
{
if (_DataAccess != null)
{
_DataAccess.RollBack(); _DataAccess.CloseConnection(false);
}
}
finally
{
if (_DBParameters != null)
{
_DBParameters.Dispose();
}
}
return(bIsValidUser);
}
public int DeleteUser(Int64 nUserID, string sTask)
{
int nResult = -2;
DataTable _dt = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@TASK", sTask, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@User_ID", nUserID, ParameterDirection.Input, SqlDbType.BigInt);
_DBParameters.Add("@Role_ID", 0, ParameterDirection.Input, SqlDbType.BigInt);
_DBParameters.Add("@UserName", "", ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@Password", "", ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@LastName", "", ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@FirstName", "", ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@MiddelName", "", ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@MobileNo1", "", ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@MobileNo2", "", ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@DOB", DateTime.MinValue.Date, ParameterDirection.Input, SqlDbType.Date);
_DBParameters.Add("@Gender", "", ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@EmailID", "", ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@Address", "", ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@ActivationStatus", "", ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@Created_By_User_ID", 0, ParameterDirection.Input, SqlDbType.BigInt);
_DataAccess.Retrive("SP_Create_Update_User", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
if (_dt != null && _dt.Rows.Count > 0)
{
nResult = Convert.ToInt32(_dt.Rows[0][0]);
}
}
catch (Exception)
{
if (_DataAccess != null)
{
_DataAccess.RollBack(); _DataAccess.CloseConnection(false);
}
}
finally
{
if (_DBParameters != null)
{
_DBParameters.Dispose();
}
}
return(nResult);
}
public List <Role> GetAllRoles(Int64 nRoleID, string sTask)
{
DataTable _dt = null;
List <Role> lstRole = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@Role_ID", nRoleID, ParameterDirection.Input, SqlDbType.BigInt);
_DBParameters.Add("@TASK", sTask, ParameterDirection.Input, SqlDbType.VarChar);
_DataAccess.Retrive("SP_GET_USER_ROLE", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
if (_dt != null && _dt.Rows.Count > 0)
{
lstRole = new List <Role>();
foreach (DataRow dr in _dt.Rows)
{
Role oRole = new Role();
oRole.RoleID = Convert.ToInt64(dr["Role_ID"]);
oRole.RoleName = Convert.ToString(dr["Role_Name"]);
oRole.RoleStatus = Convert.ToString(dr["Satus"]);
lstRole.Add(oRole);
oRole.Dispose();
oRole = null;
}
}
}
catch (Exception)
{
if (_DataAccess != null)
{
_DataAccess.RollBack(); _DataAccess.CloseConnection(false);
}
}
finally
{
if (_DBParameters != null)
{
_DBParameters.Dispose();
}
}
return(lstRole);
}
public void AddEmployee(EmployeeAddress emp)
{
DBParameters.Clear();
AddParameter("@emp_Name", emp.EmpName);
AddParameter("@gender", emp.Gender);
AddParameter("@EmailId", emp.Email);
AddParameter("@DateOfBirth", emp.DateOfBirth);
AddParameter("@DepId", 1);
AddParameter("@Manager", emp.Manager);
AddParameter("@PhoneNumber", Int64.Parse(emp.PhoneNumber));
AddParameter("@pincode", Int32.Parse(emp.PinCode));
AddParameter("@Address", emp.Address);
AddParameter("@state", emp.State);
AddParameter("@country", emp.Country);
ExecuteNonQuery("sp_AddEmployee");
}
public void ApplyLeave(Leave leave)
{
DBParameters.Clear();
AddParameter("@emailId", leave.Email);
AddParameter("@fromDate", leave.FromDate);
AddParameter("@toDate", leave.ToDate);
AddParameter("@dayCount", DayCount(leave.FromDate, leave.ToDate));
AddParameter("@comment", leave.Comment);
AddParameter("@leaveType", leave.LeaveType);
AddParameter("@leaveStatus", "pending");
AddParameter("@StartTime", DateTime.Today.TimeOfDay);
AddParameter("@EndTime", DateTime.Today.TimeOfDay);
ExecuteNonQuery("sp_ApplyLeave");
}
public int InsertUpdateCompnay(Company oCompany, string sTask)
{
int nResult = 0;
DataTable _dt = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@TASK", sTask, ParameterDirection.Input, SqlDbType.VarChar);
//_DBParameters.Add("@Company_ID", oCompany.Company_ID, ParameterDirection.Input, SqlDbType.BigInt);
_DBParameters.Add("@Company_Name", oCompany.Company_Name, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@Billing_Address", oCompany.Billing_Address, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@GST_No", oCompany.GST_No, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@PAN_No", oCompany.PAN_No, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@HSN_Code", oCompany.HSN_Code, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@GST_Per", oCompany.GST_Per, ParameterDirection.Input, SqlDbType.Float);
_DBParameters.Add("@Contact_No", oCompany.Contact_No, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@Email_ID", oCompany.Email_ID, ParameterDirection.Input, SqlDbType.VarChar);
_DataAccess.Retrive("SP__Update_Company_Master", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
if (_dt != null && _dt.Rows.Count > 0)
{
nResult = Convert.ToInt32(_dt.Rows[0][0]);
}
}
catch (Exception)
{
if (_DataAccess != null)
{
_DataAccess.RollBack(); _DataAccess.CloseConnection(false);
}
}
finally
{
if (_DBParameters != null)
{
_DBParameters.Dispose();
}
}
return(nResult);
}
public List <Employee> GetEmployeeData()
{
DBParameters.Clear();
DataSet empList = ExecuteDataSet("sp_GetAllEmlpoyees");
List <Employee> allemp = empList.Tables[0].AsEnumerable().Select(emp => new Employee()
{
EmpId = emp.Field <int>("emp_id"),
EmpName = emp.Field <string>("emp_name"),
Manager = emp.Field <string>("Manager"),
Department = emp.Field <string>("department"),
Email = emp.Field <string>("Emailid"),
DateOfBirth = emp.Field <DateTime>("DateOfBirth"),
JoinDate = emp.Field <DateTime>("dateofjoin"),
PhoneNumber = emp.Field <Int64>("PhoneNumber")
}).ToList();
return(allemp);
}
/// <summary>
/// 构建Page语句
/// </summary>
/// <param name="Tsql">查询表SQL</param>
/// <param name="orderby">排序</param>
/// <param name="output">输出参数</param>
/// <param name="pageIndex">第几页</param>
/// <param name="pageSize">每页多少条</param>
/// <returns>Page语句</returns>
public string CreatePageTSQL(string Tsql, string orderby, out DBParameters output, int pageIndex = 1, int pageSize = 20)
{
string CountSql = $" SELECT @ROWSCOUNT=Count(*) FROM ({Tsql}) countpage";
string PageSql = $@"
SELECT tbpage.* FROM ({Tsql}) tbpage
ORDER BY {orderby}
OFFSET {(pageIndex-1)*pageSize} ROWS
FETCH NEXT {pageSize} ROWS ONLY ; {CountSql}";
output = new DBParameters()
{
ParameterName = "ROWSCOUNT",
Size = 32,
Direction = System.Data.ParameterDirection.Output,
DbType = DbType.Int32
};
return(PageSql.ToString());
}
public List <PublicHolidays> GetPublicHolidays()
{
DBParameters.Clear();
DateTime yearStart = new DateTime(DateTime.Now.Year, 1, 1);
DateTime yearEnd = new DateTime(DateTime.Now.Year, 12, 31);
AddParameter("@yearStart", yearStart);
AddParameter("@yearEnd", yearEnd);
DataSet deplist = ExecuteDataSet("sp_GetPublicHolidays");
List <PublicHolidays> holidayses = deplist.Tables[0].AsEnumerable().Select(h => new PublicHolidays()
{
Date = h.Field <DateTime>("date"),
Name = h.Field <string>("name")
}).OrderBy(h => h.Date).ToList();
return(holidayses);
}
public void EditEmployee(EmployeeAddress emp)
{
DBParameters.Clear();
AddParameter("@emp_id", emp.EmpId);
AddParameter("@gender", emp.Gender);
AddParameter("@emp_Name ", emp.EmpName);
AddParameter("@EmailId ", emp.Email);
AddParameter("@DateOfBirth ", emp.DateOfBirth);
AddParameter("@DepId ", emp.Department);
AddParameter("@Manager ", emp.Manager);
AddParameter("@PhoneNumber ", Int64.Parse(emp.PhoneNumber));
AddParameter("@pincode ", int.Parse(emp.PinCode));
AddParameter("@Address ", emp.Address);
AddParameter("@state ", emp.State);
AddParameter("@country ", emp.Country);
AddParameter("@joindate ", emp.JoinDate);
ExecuteDataSet("sp_EditEmployee");
}
public List <Leave> GetLeaveStatus(string emailId)
{
DBParameters.Clear();
AddParameter("@emailId", emailId);
DataSet deplist = ExecuteDataSet("sp_GetPendingLeaves");
List <Leave> leaves = deplist.Tables[0].AsEnumerable().Select(l => new Leave()
{
Email = l.Field <string>("emailId"),
FromDate = l.Field <DateTime>("fromDate"),
ToDate = l.Field <DateTime>("toDate"),
DayCount = l.Field <int>("dayCount"),
Comment = l.Field <string>("comment"),
LeaveType = l.Field <string>("leaveType"),
LeaveStatus = l.Field <string>("leaveStatus"),
}).ToList();
return(leaves);
}
protected IDataReader ExecuteReader(string spName, int read)
{
try
{
if (_sqlconnection == null)
{
OpenConnection(read);
}
_command = _sqlconnection.CreateCommand();
_command.CommandTimeout = ConnectionTimeout;
_command.CommandText = spName;
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddRange(DBParameters.ToArray());
return(_command.ExecuteReader());
}
catch (Exception ex)
{
throw ex;
}
}
public List <Leave> GetApplyedLeaves()
{
DBParameters.Clear();
AddParameter("@startDate", DateTime.Today.AddDays(-15));
AddParameter("@endDate", DateTime.Today.AddDays(15));
DataSet dataSet = ExecuteDataSet("sp_GetLeaves");
List <Leave> leaves = dataSet.Tables[0].AsEnumerable().Select(l => new Leave()
{
EmpName = l.Field <string>("emp_Name"),
Department = l.Field <string>("Department"),
FromDate = l.Field <DateTime>("fromDate"),
ToDate = l.Field <DateTime>("toDate"),
DayCount = l.Field <int>("dayCount"),
Comment = l.Field <string>("comment"),
LeaveType = l.Field <string>("leaveType"),
LeaveStatus = l.Field <string>("leaveStatus"),
}).ToList();
return(leaves);
}
public int InsertUpdateDeleteAircraft(Aircraft oAircraft, string sTask)
{
int nResult = 0;
DataTable _dt = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@TASK", sTask, ParameterDirection.Input, SqlDbType.VarChar);
//_DBParameters.Add("@Company_ID", oCompany.Company_ID, ParameterDirection.Input, SqlDbType.BigInt);
_DBParameters.Add("@AircraftType_ID", oAircraft.AircraftTypeID, ParameterDirection.Input, SqlDbType.BigInt);
_DBParameters.Add("@AircraftType", oAircraft.AircraftType, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@AircraftCode", oAircraft.AircraftCode, ParameterDirection.Input, SqlDbType.VarChar);
_DataAccess.Retrive("SP_Create_Update_Aircraft_Code_Master", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
if (_dt != null && _dt.Rows.Count > 0)
{
nResult = Convert.ToInt32(_dt.Rows[0][0]);
}
}
catch (Exception)
{
if (_DataAccess != null)
{
_DataAccess.RollBack(); _DataAccess.CloseConnection(false);
}
}
finally
{
if (_DBParameters != null)
{
_DBParameters.Dispose();
}
}
return(nResult);
}
public Int32 ChangePassword(Int64 nUserID, string sCurrentPassword, string sNewPassword)
{
Int32 nResult = 0;
DataTable _dt = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@User_ID", nUserID, ParameterDirection.Input, SqlDbType.BigInt);
_DBParameters.Add("@CurrentPassword", sCurrentPassword, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@NewPassword", sNewPassword, ParameterDirection.Input, SqlDbType.VarChar);
_DataAccess.Retrive("SP_CHANGE_PASSWORD", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
if (_dt != null && _dt.Rows.Count > 0)
{
nResult = Convert.ToInt32(_dt.Rows[0][0]);
}
}
catch (Exception)
{
if (_DataAccess != null)
{
_DataAccess.RollBack(); _DataAccess.CloseConnection(false);
}
}
finally
{
if (_DBParameters != null)
{
_DBParameters.Dispose();
}
}
return(nResult);
}
public int Execute(string StoredProcedureName, DBParameters Parameters, out object ParameterValue1, out object ParameterValue2)
{
int _result = 0;
int _counter = 0;
SqlCommand _sqlcommand = new SqlCommand();
int _outputCounter1 = -1;
int _outputCounter2 = -1;
SqlParameter osqlParameter;
try
{
_sqlcommand.CommandType = CommandType.StoredProcedure;
_sqlcommand.CommandText = StoredProcedureName;
_sqlcommand.Connection = _con;
for (_counter = 0; _counter <= Parameters.Count - 1; _counter++)
{
if (Parameters[_counter].ParameterDirection == ParameterDirection.Output || Parameters[_counter].ParameterDirection == ParameterDirection.InputOutput)
{
if (_outputCounter1 < 0) { _outputCounter1 = _counter; }
else if (_outputCounter2 < 0) { _outputCounter2 = _counter; }
}
osqlParameter = new SqlParameter();
osqlParameter.ParameterName = Parameters[_counter].ParameterName;
osqlParameter.SqlDbType = Parameters[_counter].DataType;
osqlParameter.Direction = Parameters[_counter].ParameterDirection;
osqlParameter.Value = Parameters[_counter].Value;
if (Parameters[_counter].Size != null)
{
if (Parameters[_counter].Size != 0)
{
osqlParameter.Size = Parameters[_counter].Size;
}
}
_sqlcommand.Parameters.Add(osqlParameter);
osqlParameter = null;
}
_result = _sqlcommand.ExecuteNonQuery();
if (_sqlcommand.Parameters[_outputCounter1].Value != null)
{ ParameterValue1 = _sqlcommand.Parameters[_outputCounter1].Value; }
else { ParameterValue1 = 0; }
if (_sqlcommand.Parameters[_outputCounter2].Value != null)
{ ParameterValue2 = _sqlcommand.Parameters[_outputCounter2].Value; }
else
{ ParameterValue2 = 0; }
}
catch (Exception ex)
{
if (_WithTrn == true)
{
_Trn.Rollback();
}
throw ex;
}
finally
{
if (_sqlcommand != null)
{
_sqlcommand.Dispose();
}
}
return _result;
}
protected void AddParameter(string name, object value)
{
DBParameters.Add(new SqlParameter(name, value));
}
public DataTable GetAlumniInfo(string sSearchString, int nSearchCriteria)
{
DataTable _dt = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@sSearchString", sSearchString, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@nSearchCriteria", nSearchCriteria, ParameterDirection.Input, SqlDbType.Int);
_DataAccess.Retrive("SP_SVIT_GetSearchAlumni", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
}
catch (Exception)
{
if (_DataAccess != null) { _DataAccess.RollBack(); _DataAccess.CloseConnection(false); }
}
finally
{
if (_DBParameters != null) { _DBParameters.Dispose(); }
}
return _dt;
}
public DataTable GetImportantLinks()
{
DataTable _dt = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DataAccess.Retrive("SP_SVIT_GetImportantLinks", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
}
catch (Exception)
{
if (_DataAccess != null) { _DataAccess.RollBack(); _DataAccess.CloseConnection(false); }
}
finally
{
if (_DBParameters != null) { _DBParameters.Dispose(); }
}
return _dt;
}
public DataTable GetIntituteDetails(bool bIsShowInAbout)
{
DataTable _dt = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@bIsShowInAbout", bIsShowInAbout, ParameterDirection.Input, SqlDbType.Bit);
_DataAccess.Retrive("SP_SVIT_GetIntituteDetails", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
}
catch (Exception)
{
if (_DataAccess != null) { _DataAccess.RollBack(); _DataAccess.CloseConnection(false); }
}
finally
{
if (_DBParameters != null) { _DBParameters.Dispose(); }
}
return _dt;
}
public DataTable GetLatestNEWS(int nNewsType,Boolean bIsShownOnNEWS=false)
{
DataTable _dt = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@nNewsType", nNewsType, ParameterDirection.Input, SqlDbType.Int);
_DBParameters.Add("@bIsShowOnNEWS", bIsShownOnNEWS, ParameterDirection.Input, SqlDbType.Bit);
_DataAccess.Retrive("SP_SVIT_GetLatestNews", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
}
catch (Exception)
{
if (_DataAccess != null) { _DataAccess.RollBack(); _DataAccess.CloseConnection(false); }
}
finally
{
if (_DBParameters != null) { _DBParameters.Dispose(); }
}
return _dt;
}
public List <BMEData> GetBMEData(DateTime dtFromDate, DateTime dtToDate, string sTask)
{
DataTable _dt = null;
List <BMEData> lstBMEData = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@FROM_DATE_TIME", dtFromDate, ParameterDirection.Input, SqlDbType.DateTime);
_DBParameters.Add("@TO_DATE_TIME", dtToDate, ParameterDirection.Input, SqlDbType.DateTime);
_DBParameters.Add("@TASK", sTask, ParameterDirection.Input, SqlDbType.VarChar);
_DataAccess.Retrive("GET_BME_DATA", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
if (_dt != null && _dt.Rows.Count > 0)
{
lstBMEData = new List <BMEData>();
Int32 count = 0;
foreach (DataRow dr in _dt.Rows)
{
BMEData oBMEData = new BMEData();
oBMEData.SrNo = ++count;
oBMEData.ID = Convert.ToInt64(dr["ID"]);
oBMEData.BME_ID = Convert.ToString(dr["BME ID"]);
oBMEData.METER_TYPE = Convert.ToString("");
oBMEData.METER_SUB_TYPE = Convert.ToString(dr["METER TYPE"]);
oBMEData.METER_LOCATION = Convert.ToString(dr["LOCATION"]);
oBMEData.METER_NUMBER = Convert.ToString(dr["METER NO."]);
oBMEData.READING_DATE_TIME = Convert.ToDateTime(dr["READING TIME"]);
oBMEData.CURRENT_READING = Convert.ToString("");
oBMEData.PREVIOUS_READING = Convert.ToString("");
oBMEData.METER_START_TIME = Convert.ToDateTime(dr["START TIME"]);
oBMEData.METER_END_TIME = Convert.ToDateTime(dr["END TIME"]);
if (Convert.ToString(dr["IS ASSOCIATED"]).ToLower() == "yes")
{
oBMEData.PROCESSING_DONE = true;
}
else
{
oBMEData.PROCESSING_DONE = false;
}
oBMEData.PROCESSING_DATE_TIME = Convert.ToDateTime(DateTime.MinValue);
oBMEData.DATA_SOURCE_TYPE = Convert.ToString(dr["DATA SOURCE"]);
lstBMEData.Add(oBMEData);
oBMEData.Dispose();
oBMEData = null;
}
}
}
catch (Exception)
{
if (_DataAccess != null)
{
_DataAccess.RollBack(); _DataAccess.CloseConnection(false);
}
}
finally
{
if (_DBParameters != null)
{
_DBParameters.Dispose();
}
}
return(lstBMEData);
}
public Int64 InsertUpdateStudentInfo()
{
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
Int64 _result = 0;
object _val = null;
try
{
_DBParameters = new DBParameters();
_DataAccess = new DataAccess();
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@nStudentID", this.nStudentID, ParameterDirection.InputOutput, SqlDbType.BigInt);
_DBParameters.Add("@sStudFirstName", this.sStudFirstName, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@sStudMiddleName", this.sStudMiddleName, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@sStudLastName", this.sStudLastName, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@sStudEmailID", this.sStudEmailID, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@sStudRegistrationID", this.sStudRegistrationID, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@sUserName", this.sStudUserName, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@sPassword", this.sStudPassword, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@sCurrentYear", this.sStudCurrentYear, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@nBranchID", this.nBranchID, ParameterDirection.Input, SqlDbType.BigInt);
_DBParameters.Add("@dtStudDOB", this.dtStudDOB, ParameterDirection.Input, SqlDbType.DateTime);
_DBParameters.Add("@bIsStudentActive", this.bIsStudentActive, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@nEduDtlsID", this.nEduDtlsID, ParameterDirection.Input, SqlDbType.BigInt);
_DBParameters.Add("@sSSCMarks", this.sSSCMarks, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@sHSCMarks", this.sHSCMarks, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@sDiplomaMarks", this.sDiplomaMarks, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@sFirstYearMarks", this.sFirstYearMarks, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@sSecondYearMarks", this.sSecondYearMarks, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@sThirdYearMarks", this.sThirdYearMarks, ParameterDirection.Input, SqlDbType.VarChar);
_DBParameters.Add("@sBEMarks", this.sBEMarks, ParameterDirection.Input, SqlDbType.VarChar);
_DataAccess.Execute("SP_SVIT_InsertUpdateStudent", _DBParameters, out _val);
this.nStudentID = (Int64)_val;
_DataAccess.CloseConnection(false);
_result = this.nStudentID;
}
catch (Exception ex)
{
_DataAccess.CloseConnection(false);
//MessageBox.Show("Exception: " + ex.ToString());
}
finally
{
if (_DBParameters != null)
{
_DBParameters.Dispose();
_DBParameters = null;
}
if (_DataAccess != null)
{
_DataAccess.Dispose();
}
}
return _result;
}
public DataSet GetPaperSeminarBYStaff(Int64 nStaffID = 0)
{
DataSet _ds = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@nStaffID", nStaffID, ParameterDirection.Input, SqlDbType.BigInt);
_DataAccess.Retrive("SP_SVIT_GetPaperAndSeminarByStaff", _DBParameters, out _ds);
_DataAccess.CloseConnection(false);
}
catch (Exception)
{
if (_DataAccess != null) { _DataAccess.RollBack(); _DataAccess.CloseConnection(false); }
}
finally
{
if (_DBParameters != null) { _DBParameters.Dispose(); }
}
return _ds;
}
public void Retrive(string StoredProcedureName, DBParameters Parameters, out DataTable _result)
{
//DataTable _result = new DataTable();
int _counter = 0;
SqlCommand _sqlcommand = new SqlCommand();
SqlParameter osqlParameter;
try
{
_sqlcommand.CommandType = CommandType.StoredProcedure;
_sqlcommand.CommandText = StoredProcedureName;
_sqlcommand.Connection = _con;
if (_WithTrn == true)
{
_sqlcommand.Transaction = _Trn;
}
for (_counter = 0; _counter <= Parameters.Count - 1; _counter++)
{
osqlParameter = new SqlParameter();
osqlParameter.ParameterName = Parameters[_counter].ParameterName;
osqlParameter.SqlDbType = Parameters[_counter].DataType;
osqlParameter.Direction = Parameters[_counter].ParameterDirection;
osqlParameter.Value = Parameters[_counter].Value;
if (Parameters[_counter].Size != null)
{
if (Parameters[_counter].Size != 0)
{
osqlParameter.Size = Parameters[_counter].Size;
}
}
_sqlcommand.Parameters.Add(osqlParameter);
osqlParameter = null;
}
SqlDataAdapter _dataAdapter = new SqlDataAdapter(_sqlcommand);
DataSet _dataset = new DataSet();
DataTable _resultinternal = new DataTable();
_dataAdapter.Fill(_dataset);
if (_dataset.Tables[0] != null)
{
_resultinternal = _dataset.Tables[0];
}
_result = _resultinternal;
_resultinternal.Dispose();
_dataset.Dispose();
_dataAdapter.Dispose();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (_sqlcommand != null)
{
_sqlcommand.Dispose();
}
}
//return _result;
}
public DataTable GetStudentDetailsByUserName(string UserName)
{
DataTable _dt = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@sUserName", UserName, ParameterDirection.Input, SqlDbType.VarChar);
_DataAccess.Retrive("SP_SVIT_GetStudentsLoginDetails", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
}
catch (Exception)
{
if (_DataAccess != null) { _DataAccess.RollBack(); _DataAccess.CloseConnection(false); }
}
finally
{
if (_DBParameters != null) { _DBParameters.Dispose(); }
}
return _dt;
}
public DataTable GetWorkshopDetails(Int64 nBranchID = 0)
{
DataTable _dt = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@nBranchID", nBranchID, ParameterDirection.Input, SqlDbType.BigInt);
_DataAccess.Retrive("SP_SVIT_GetWorkshopSeminar", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
}
catch (Exception)
{
if (_DataAccess != null) { _DataAccess.RollBack(); _DataAccess.CloseConnection(false); }
}
finally
{
if (_DBParameters != null) { _DBParameters.Dispose(); }
}
return _dt;
}
public List <AssociationData> GetAssociationData(DateTime dtFromDate, DateTime dtToDate, string sTask)
{
DataTable _dt = null;
List <AssociationData> lstAssociationData = null;
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
try
{
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@FROM_DATE_TIME", dtFromDate, ParameterDirection.Input, SqlDbType.DateTime);
_DBParameters.Add("@TO_DATE_TIME", dtToDate, ParameterDirection.Input, SqlDbType.DateTime);
//_DBParameters.Add("@TASK", sTask, ParameterDirection.Input, SqlDbType.VarChar);
_DataAccess.Retrive("GET_ASSOCIATION_DATA", _DBParameters, out _dt);
_DataAccess.CloseConnection(false);
if (_dt != null && _dt.Rows.Count > 0)
{
lstAssociationData = new List <AssociationData>();
foreach (DataRow dr in _dt.Rows)
{
AssociationData oAssociationData = new AssociationData();
//ID AIRLN CODE A FLTNO D FLTNO A FLT D FLT D DATE AIRCRFT REG AIRCFT TYPE AIRCFT CODE BAYNO
//BME NO BME TYPE BME ST DT BME END DT CONSUMPTION BILLABLEDATA FGP RATE PAC RATE
//FGP REVENUE PCA REVENUE TOTAL
oAssociationData.CELEBINAS_ID = Convert.ToInt64(dr["ID"]);
oAssociationData.AIRLN_CODE = Convert.ToString(dr["AIRLN CODE"]);
oAssociationData.ARRIVAL_FLIGHT_NO = Convert.ToString(dr["A FLTNO"]);
oAssociationData.FLTNO = Convert.ToString(dr["D FLTNO"]);
oAssociationData.ARRIVAL_FLIGHT_TYPE = Convert.ToString(dr["A FLT"]);
oAssociationData.FLIGHT_TYPE = Convert.ToString(dr["D FLT"]);
oAssociationData.DEP_DATE = Convert.ToDateTime(dr["D DATE"]);
oAssociationData.AIRCRFT_REG = Convert.ToString(dr["AIRCRFT REG"]);
oAssociationData.AIRCFT_TYPE = Convert.ToString(dr["AIRCFT TYPE"]);
oAssociationData.AIRCFT_CODE = Convert.ToString(dr["AIRCFT CODE"]);
oAssociationData.BAYNO = Convert.ToString(dr["BAYNO"]);
oAssociationData.BME_NO = Convert.ToString(dr["BME NO"]);
oAssociationData.BME_TYPE = Convert.ToString(dr["BME TYPE"]);
oAssociationData.BME_ST_DT = Convert.ToDateTime(dr["BME ST DT"]);
oAssociationData.BME_END_DT = Convert.ToDateTime(dr["BME END DT"]);
oAssociationData.CONSUMPTION = Convert.ToDecimal(dr["CONSUMPTION"]);
oAssociationData.BILLABLEDATA = Convert.ToDecimal(dr["BILLABLEDATA"]);
oAssociationData.FGP_RATE_PER_PER_HR = dr["FGP RATE"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["FGP RATE"]);
oAssociationData.PAC_RATE_PER_HR = dr["PAC RATE"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["PAC RATE"]);
oAssociationData.FGP_REVENUE = dr["FGP REVENUE"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["FGP REVENUE"]);
oAssociationData.PCA_REVENUE = dr["PCA REVENUE"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["PCA REVENUE"]);
oAssociationData.TOTAL = dr["TOTAL"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["TOTAL"]);
lstAssociationData.Add(oAssociationData);
oAssociationData.Dispose();
oAssociationData = null;
}
}
}
catch (Exception)
{
if (_DataAccess != null)
{
_DataAccess.RollBack(); _DataAccess.CloseConnection(false);
}
}
finally
{
if (_DBParameters != null)
{
_DBParameters.Dispose();
}
}
return(lstAssociationData);
}
public Int64 InsertUpdateAlumniStudentInfo(DataTable dtFriendsDetails)
{
DBParameters _DBParameters = new DBParameters();
DataAccess _DataAccess = new DataAccess();
Int64 _result = 0;
object _val = null;
try
{
_DBParameters = new DBParameters();
_DataAccess = new DataAccess();
_DataAccess.OpenConnection(false);
_DBParameters.clear();
_DBParameters.Add("@nAlumniID", this.nAlumniID, ParameterDirection.InputOutput, SqlDbType.BigInt);
_DBParameters.Add("@sAlumniName", this.sAlumniName, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@sAdmissionYear", this.sAdmissionYear, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@sPassingYear", this.sPassingYear, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@nBranchID", this.nBranchID, ParameterDirection.Input, SqlDbType.BigInt);
_DBParameters.Add("@sContactNo", this.sContactNo, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@sAlternateContactNo", this.sAlternateContactNo, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@sPersonalEmailID", this.sPersonalEmailID, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@sOfficeEmailID", this.sOfficeEmailID, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@sCorrespondanceAddress", this.sCorrespondanceAddress, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@sPermanentAddress", this.sPermanentAddress, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@sOccupation", this.sOccupation, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@sDesigation", this.sDesigation, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@sCompanyName", this.sCompanyName, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@sCompanyAddress", this.sCompanyAddress, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@sWhatsUpDetails", this.sWhatsUpDetails, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@sFacebookDetails", this.sFacebookDetails, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@sLinkedinDetails", this.sLinkedinDetails, ParameterDirection.Input, SqlDbType.NVarChar);
_DBParameters.Add("@FriendsDetails", dtFriendsDetails, ParameterDirection.Input, SqlDbType.Structured);
_DataAccess.Execute("SP_SVIT_InsertUpdateAlumniDetails", _DBParameters, out _val);
this.nStudentID = (Int64)_val;
_DataAccess.CloseConnection(false);
_result = this.nStudentID;
}
catch (Exception ex)
{
_DataAccess.CloseConnection(false);
//MessageBox.Show("Exception: " + ex.ToString());
}
finally
{
if (_DBParameters != null)
{
_DBParameters.Dispose();
_DBParameters = null;
}
if (_DataAccess != null)
{
_DataAccess.Dispose();
}
}
return _result;
}
public DataTable SQL_PROCEDURE_PARAMS(string DB_Platform, string ConnAuth, string ProcedureName, List<DBParameters> _dbParameters)
{
DBParameters[] dbParameters = new DBParameters[_dbParameters.Count];
List<DBParameters> infoList = new List<DBParameters>();
int iNumber = 0;
switch(DB_Platform)
{
case "Oracle":
case "ORACLE":
foreach (DBParameters i in _dbParameters)
{
dbParameters[iNumber] = new DB_Toolbox.DBParameters();
dbParameters[iNumber].ParamName = i.ParamName;
dbParameters[iNumber].OracleParamDataType = i.OracleParamDataType;
dbParameters[iNumber].ParamDirection = i.ParamDirection;
dbParameters[iNumber].ParamValue = i.ParamValue;
dbParameters[iNumber].ParamSize = i.ParamSize;
dbParameters[iNumber].ParamReturn = i.ParamReturn;
infoList.Add(dbParameters[iNumber]);
iNumber++; //++ Increments by 1.
}
break;
case "Microsoft":
case "MICROSOFT":
foreach (DBParameters i in _dbParameters)
{
dbParameters[iNumber] = new DB_Toolbox.DBParameters();
dbParameters[iNumber].ParamName = i.ParamName;
dbParameters[iNumber].MSSqlParamDataType = i.MSSqlParamDataType;
dbParameters[iNumber].ParamDirection = i.ParamDirection; //Not used by MSSQL
dbParameters[iNumber].ParamValue = i.ParamValue; //Not used by MSSQL
dbParameters[iNumber].ParamSize = i.ParamSize;
dbParameters[iNumber].ParamReturn = i.ParamReturn;
infoList.Add(dbParameters[iNumber]);
iNumber++; //++ Increments by 1.
}
break;
}
return SQL_PROCEDURE(DB_Platform, ConnAuth, ProcedureName, infoList);
}
public DataTable SQL_PROCEDURE_PARAMS(string DB_Platform, string ConnAuth, string ProcedureName, List<DBParameters> _dbParameters)
{
DBParameters[] dbParameters = new DBParameters[_dbParameters.Count];
List<DBParameters> infoList = new List<DBParameters>();
int iNumber = 0;
foreach (DBParameters i in _dbParameters)
{
dbParameters[iNumber] = new DB_Toolbox.DBParameters();
dbParameters[iNumber].ParamName = i.ParamName;
dbParameters[iNumber].ParamDBType = i.ParamDBType;
dbParameters[iNumber].ParamDirection = i.ParamDirection;
dbParameters[iNumber].ParamValue = i.ParamValue;
dbParameters[iNumber].ParamSize = i.ParamSize;
dbParameters[iNumber].ParamReturn = i.ParamReturn;
infoList.Add(dbParameters[iNumber]);
iNumber++; //++ Increments by 1.
}
return SQL_PROCEDURE(DB_Platform, ConnAuth, ProcedureName, infoList);
}
