public Holiday GetData(int id) { SQLConnector.OpenConnection(); SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.CommandText = string.Format("SELECT * FROM Holiday WHERE ID ={0}", id); command.CommandType = System.Data.CommandType.Text; SqlDataReader reader = command.ExecuteReader(); Holiday item = new Holiday(); if (reader.Read()) { item.IsNew = false; item.ID = Convert.ToInt32(reader["ID"]); item.Code = (reader["Code"].ToString()); item.Description = (reader["Description"].ToString()); item.FromDate = Convert.ToDateTime(reader["FromDate"]); item.ToDate = Convert.ToDateTime(reader["ToDate"]); } reader.Close(); reader.Dispose(); reader = null; SQLConnector.CloseConnection(); return(item); }
public Invoice GetData(int id) { SQLConnector.OpenConnection(); SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.CommandText = string.Format("SELECT * FROM [Invoice] WHERE ID ={0}", id); command.CommandType = System.Data.CommandType.Text; SqlDataReader reader = command.ExecuteReader(); Invoice item = new Invoice(); if (reader.Read()) { item.IsNew = false; item.ID = Convert.ToInt32(reader["ID"]); item.UserName = (reader["UserName"].ToString()); item.InvoiceReference = (reader["InvoiceReference"]).ToString(); item.InvoiceValue = Convert.ToInt32(reader["InvoiceValue"]); item.POReference = (reader["POReference"].ToString()); item.InvoiceDate = Convert.ToDateTime(reader["InvoiceDate"]); item.OmniflowReference = (reader["OmniflowReference"]).ToString(); item.ReceiveDate = Convert.ToDateTime(reader["ReceiveDate"]); item.PaymentdueDate = Convert.ToDateTime(reader["PaymentdueDate"]); item.Status = (reader["Status"]).ToString(); } reader.Close(); reader.Dispose(); reader = null; SQLConnector.CloseConnection(); return(item); }
public void Delete(int id) { if (Convert.ToInt32(id) <= 0) { throw new Exception("ID is required."); } try { SQLConnector.OpenConnection(); SQLConnector.BeginTransaction(); string sql = string.Format("DELETE FROM Vendor WHERE ID ={0}", id); SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.Transaction = SQLConnector.Transaction; command.CommandText = sql; command.CommandType = System.Data.CommandType.Text; command.ExecuteNonQuery(); SQLConnector.CommitTransaction(); SQLConnector.CloseConnection(); } catch (Exception exp) { SQLConnector.RollbackTransaction(); throw exp; } }
public static int GenerateMaxNumber(string tableName, string columnName, string whereClause) { string sql = string.Format("SELECT MAX({0}) FROM {1} {2}", columnName, tableName, whereClause); SQLConnector.OpenConnection(); SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.Transaction = SQLConnector.Transaction; command.CommandText = sql; command.CommandType = System.Data.CommandType.Text; object maxID = command.ExecuteScalar(); if (maxID == DBNull.Value) { maxID = 1; } else { maxID = Convert.ToInt32(maxID) + 1; } //SQLConnector.CloseConnection(); return(Convert.ToInt32(maxID)); }
public static List <Document> GetAllData(string sortColumn, string sortDirection) { SQLConnector.OpenConnection(); SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.CommandText = string.Format("SELECT * FROM Document ORDER BY {0} {1}", sortColumn, sortDirection); command.CommandType = System.Data.CommandType.Text; SqlDataReader reader = command.ExecuteReader(); List <Document> items = new List <Document>(); while (reader.Read()) { Document item = new Document(); item.IsNew = false; item.ID = Convert.ToInt32(reader["ID"]); item.Description = (reader["Description"].ToString()); item.IsMendatory = Convert.ToBoolean(reader["IsMendatory"]); items.Add(item); } reader.Close(); reader.Dispose(); reader = null; SQLConnector.CloseConnection(); return(items); }
public static List <Holiday> GetAllData(string sortColumn, string sortDirection) { SQLConnector.OpenConnection(); SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.CommandText = string.Format("SELECT * FROM Holiday ORDER BY {0} {1}", sortColumn, sortDirection); command.CommandType = System.Data.CommandType.Text; SqlDataReader reader = command.ExecuteReader(); List <Holiday> items = new List <Holiday>(); while (reader.Read()) { Holiday item = new Holiday(); item.IsNew = false; item.ID = Convert.ToInt32(reader["ID"]); item.Code = (reader["Code"].ToString()); item.Description = (reader["Description"].ToString()); item.FromDate = Convert.ToDateTime(reader["FromDate"]); item.ToDate = Convert.ToDateTime(reader["ToDate"]); items.Add(item); } reader.Close(); reader.Dispose(); reader = null; SQLConnector.CloseConnection(); return(items); }
public Document GetData(int id) { SQLConnector.OpenConnection(); SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.CommandText = string.Format("SELECT * FROM Document WHERE ID ={0}", id); command.CommandType = System.Data.CommandType.Text; SqlDataReader reader = command.ExecuteReader(); Document item = new Document(); if (reader.Read()) { item.IsNew = false; item.ID = Convert.ToInt32(reader["ID"]); item.Description = (reader["Description"].ToString()); item.IsMendatory = Convert.ToBoolean(reader["IsMendatory"]); } reader.Close(); reader.Dispose(); reader = null; SQLConnector.CloseConnection(); return(item); }
public static List <User> GetAllData(string sortColumn, string sortDirection) { SQLConnector.OpenConnection(); SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.CommandText = string.Format("SELECT * FROM [User] ORDER BY {0} {1}", sortColumn, sortDirection); command.CommandType = System.Data.CommandType.Text; SqlDataReader reader = command.ExecuteReader(); List <User> items = new List <User>(); while (reader.Read()) { User item = new User(); item.IsNew = false; item.ID = Convert.ToInt32(reader["ID"]); item.UserName = Convert.ToString(reader["UserName"]); item.UserID = Convert.ToString(reader["UserID"]); item.Password = (reader["Password"].ToString()); item.IsAdmin = Convert.ToBoolean(reader["IsAdmin"]); item.IsOperator = Convert.ToBoolean(reader["IsOperator"]); items.Add(item); } reader.Close(); reader.Dispose(); reader = null; SQLConnector.CloseConnection(); return(items); }
public User GetData(string userID) { SQLConnector.OpenConnection(); SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.CommandText = string.Format("SELECT * FROM [User] WHERE UserID ='{0}'", userID); command.CommandType = System.Data.CommandType.Text; SqlDataReader reader = command.ExecuteReader(); User item = new User(); if (reader.Read()) { item.IsNew = false; item = new User(); item.ID = Convert.ToInt32(reader["ID"]); item.UserName = Convert.ToString(reader["UserName"]); item.UserID = Convert.ToString(reader["UserID"]); item.Password = (reader["Password"].ToString()); item.IsAdmin = Convert.ToBoolean(reader["IsAdmin"]); item.IsOperator = Convert.ToBoolean(reader["IsOperator"]); } reader.Close(); reader.Dispose(); reader = null; SQLConnector.CloseConnection(); return(item); }
public void Save() { if (this.Code.Length == 0) { throw new Exception("Code is required"); } if (Global.IsExists("Vendor", "ID", "Code", this.ID, this.Code)) { throw new Exception("Your entered Code is already exists here. Please enter your valid Code."); } if (_email.Length == 0) { throw new Exception("Email is required"); } if (_userName.Length == 0) { throw new Exception("UserName is required"); } try { SQLConnector.OpenConnection(); SQLConnector.BeginTransaction(); string sql = string.Empty; if (_isNew) { _id = Global.GenerateMaxNumber("Vendor", "ID"); sql = string.Format("INSERT INTO Vendor(ID,UserName,Code,Address,Email,ContactNo,BankName,AccountNo,PaymentTerm,EntryUserId,UpdateUserId,UpdateDate,isActive) VALUES({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8},'{9}','{10}','{11}',{12})", _id, _userName, _code, _address, _email, _contactNo, _bankName, _accountNo, _paymentTerm, _entryuserId, _updateuserId, _updateDate, Convert.ToInt32(_isActive)); } else { sql = string.Format("UPDATE Vendor SET UserName = '******', Code='{1}',Address = '{2}', Email= '{3}', ContactNo= '{4}',BankName = '{5}',AccountNo = '{6}',PaymentTerm = {7},EntryUserId = '{8}',UpdateUserId = '{9}',UpdateDate = '{10}',IsActive = {11} WHERE ID = {12} ", _userName, _code, _address, _email, _contactNo, _bankName, _accountNo, _paymentTerm, _entryuserId, _updateuserId, _updateDate, Convert.ToInt32(_isActive), _id); } SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.Transaction = SQLConnector.Transaction; command.CommandText = sql; command.CommandType = System.Data.CommandType.Text; command.ExecuteNonQuery(); SQLConnector.CommitTransaction(); SQLConnector.CloseConnection(); } catch (Exception exp) { SQLConnector.RollbackTransaction(); throw exp; } }
public void Save() { if (this.UserName.Length == 0) { throw new Exception("UserName is required"); } if (Global.IsExists("Report", "ID", "UserName", this.ID, this.UserName)) { throw new Exception("Your entered UserName is already exists here. Please enter your valid UserName."); } if (_invoiceReference.Length == 0) { throw new Exception("Invoice Reference is required"); } if (_omniflowReference.Length == 0) { throw new Exception("Omniflow Reference is required"); } try { SQLConnector.OpenConnection(); SQLConnector.BeginTransaction(); string sql = string.Empty; if (_isNew) { _id = Global.GenerateMaxNumber("Report", "ID"); sql = string.Format("INSERT INTO Report(ID,Code,UserName,InvoiceReference,InvoiceDate,POReference,InvoiceValue,OmniflowReference,ReceiveDate,PaymentTerm,PaymentdueDate,PaymentDate,PaidAmount,TaxDeduction,VatDeduction,Status) VALUES({0},'{1}','{2}',{3},'{4}','{5}',{6},{7},'{8}',{9},'{10}','{11}','{12}','{13}','{14}','{15}')", _id, _code, _userName, _invoiceReference, _invoiceDate, _poReference, _invoiceValue, _omniflowReference, _receiveDate, _paymentTerm, _paymentdueDate, _paymentDate, _paidAmount, _taxDeduction, _vatDeduction, _status); } else { sql = string.Format("UPDATE Report SET Code ='{0}',UserName='******',InvoiceReference = '{2}',InvoiceDate = '{3}',POReference = '{4}',InvoiceValue = {5},OmniflowReference = '{6}',ReceiveDate = '{7}',PaymentTerm = {8},PaymentdueDate = '{9}',PaymentDate = '{10}',PaidAmount = '{11}',TaxDeduction = '{12}',VatDeduction = '{13}' ,Status = '{14}' WHERE ID = {15} ", _code, _userName, _invoiceReference, _invoiceDate, _poReference, _invoiceValue, _omniflowReference, _receiveDate, _paymentTerm, _paymentdueDate, _paymentDate, _paidAmount, _taxDeduction, _vatDeduction, _status, _id); } SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.Transaction = SQLConnector.Transaction; command.CommandText = sql; command.CommandType = System.Data.CommandType.Text; command.ExecuteNonQuery(); SQLConnector.CommitTransaction(); SQLConnector.CloseConnection(); } catch (Exception exp) { SQLConnector.RollbackTransaction(); throw exp; } }
public static List <Report> GetAllData(string status, string sortColumn, string sortDirection) { string whereClause = string.Empty; if (status.Length > 0) { whereClause = string.Format("WHERE Status = '{0}'", status); } SQLConnector.OpenConnection(); SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.CommandText = string.Format("SELECT * FROM Report {0} ORDER BY {1} {2}", whereClause, sortColumn, sortDirection); command.CommandType = System.Data.CommandType.Text; SqlDataReader reader = command.ExecuteReader(); List <Report> items = new List <Report>(); while (reader.Read()) { Report item = new Report(); item.IsNew = false; item.ID = Convert.ToInt32(reader["ID"]); item.Code = (reader["Code"].ToString()); item.UserName = (reader["UserName"].ToString()); item.InvoiceReference = (reader["InvoiceReference"].ToString()); item.InvoiceDate = Convert.ToDateTime(reader["InvoiceDate"]); item.POReference = (reader["POReference"].ToString()); item.InvoiceValue = Convert.ToInt32(reader["InvoiceValue"]); item.OmniflowReference = (reader["OmniflowReference"]).ToString(); item.ReceiveDate = Convert.ToDateTime(reader["ReceiveDate"]); item.PaymentTerm = Convert.ToInt32(reader["PaymentTerm"]); item.PaymentdueDate = Convert.ToDateTime(reader["PaymentdueDate"]); item.PaymentDate = Convert.ToDateTime(reader["PaymentDate"]); item.PaidAmount = (reader["PaidAmount "].ToString()); item.TaxDeduction = (reader["TaxDeduction"].ToString()); item.VatDeduction = (reader["VatDeduction"].ToString()); item.Status = (reader["Status"]).ToString(); items.Add(item); } reader.Close(); reader.Dispose(); reader = null; SQLConnector.CloseConnection(); return(items); }
public void Save() { if (this.UserName.Length == 0) { throw new Exception("User Name is required"); } if (Global.IsExists("[User]", "ID", "UserName", this.ID, this.UserName)) { throw new Exception("Your entered User ID or Password is wrong. Please enter your valid User ID or Password."); } if (_password.Length == 0) { throw new Exception("Password is required"); } try { SQLConnector.OpenConnection(); SQLConnector.BeginTransaction(); string sql = string.Empty; if (_isNew) { _id = Global.GenerateMaxNumber("[User]", "ID"); sql = string.Format("INSERT INTO [User](ID,UserName,UserID,Password,IsAdmin,IsOperator) VALUES({0},'{1}','{2}','{3}',{4},{5})", _id, _userName, _userID, _password, Convert.ToInt32(_isAdmin), Convert.ToInt32(_isOperator)); } else { sql = string.Format("UPDATE [User] SET UserName = '******', UserID = '{1}', Password = '******', IsAdmin = {3}, IsOperator = {4} WHERE ID = {5} ", _userName, _userID, _password, Convert.ToInt32(_isAdmin), Convert.ToInt32(_isOperator), _id); } SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.Transaction = SQLConnector.Transaction; command.CommandText = sql; command.CommandType = System.Data.CommandType.Text; command.ExecuteNonQuery(); SQLConnector.CommitTransaction(); SQLConnector.CloseConnection(); } catch (Exception exp) { SQLConnector.RollbackTransaction(); throw exp; } }
public void Save() { if (this.Code.Length == 0) { throw new Exception("Code is required"); } if (Global.IsExists("Holiday", "ID", "Code", this.ID, this.Code)) { throw new Exception("Your entered Code is already exists here. Please enter your valid Code."); } if (_description.Length == 0) { throw new Exception("Description is required"); } try { SQLConnector.OpenConnection(); SQLConnector.BeginTransaction(); string sql = string.Empty; if (_isNew) { _id = Global.GenerateMaxNumber("Holiday", "ID"); sql = string.Format("INSERT INTO Holiday(ID,Code,Description,FromDate,ToDate) VALUES({0},'{1}','{2}','{3}','{4}')", _id, _code, _description, _FromDate, _ToDate); } else { sql = string.Format("UPDATE Holiday SET Code = '{0}', Description = '{1}', FromDate = '{2}', ToDate = '{3}' WHERE ID = {4} ", _code, _description, _FromDate, _ToDate, _id); } SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.Transaction = SQLConnector.Transaction; command.CommandText = sql; command.CommandType = System.Data.CommandType.Text; command.ExecuteNonQuery(); SQLConnector.CommitTransaction(); SQLConnector.CloseConnection(); } catch (Exception exp) { SQLConnector.RollbackTransaction(); throw exp; } }
public void Save() { if (this.Description.Length == 0) { throw new Exception("Description is required"); } if (Global.IsExists("Document", "ID", "Description", this.ID, this.Description)) { throw new Exception("Your entered Description is already exists here. Please enter your valid Description."); } try { SQLConnector.OpenConnection(); SQLConnector.BeginTransaction(); string sql = string.Empty; if (_isNew) { _id = Global.GenerateMaxNumber("Document", "ID"); sql = string.Format("INSERT INTO Document(ID,Description,isMendatory) VALUES({0},'{1}',{2})", _id, _description, Convert.ToInt32(_isMendatory)); } else { sql = string.Format("UPDATE Document SET Description = '{0}', IsMendatory = {1} WHERE ID = {2} ", _description, Convert.ToInt32(_isMendatory), _id); } SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.Transaction = SQLConnector.Transaction; command.CommandText = sql; command.CommandType = System.Data.CommandType.Text; command.ExecuteNonQuery(); SQLConnector.CommitTransaction(); SQLConnector.CloseConnection(); } catch (Exception exp) { SQLConnector.RollbackTransaction(); throw exp; } }
public static List <Vendor> GetAllData(string sortColumn, string sortDirection) { SQLConnector.OpenConnection(); SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.CommandText = string.Format("SELECT * FROM Vendor ORDER BY {0} {1}", sortColumn, sortDirection); command.CommandType = System.Data.CommandType.Text; SqlDataReader reader = command.ExecuteReader(); List <Vendor> items = new List <Vendor>(); while (reader.Read()) { Vendor item = new Vendor(); item.IsNew = false; item.ID = Convert.ToInt32(reader["ID"]); item.UserName = (reader["UserName"].ToString()); item.Code = (reader["Code"].ToString()); item.Email = (reader["Email"]).ToString(); item.Address = (reader["Address"]).ToString(); item.ContactNo = (reader["ContactNo"]).ToString(); item.BankName = (reader["BankName"]).ToString(); item.AccountNo = (reader["AccountNo"]).ToString(); item.PaymentTerm = Convert.ToInt32(reader["PaymentTerm"]); item.IsActive = Convert.ToBoolean(reader["IsActive"]); item.EntryuserId = (reader["EntryUserID"].ToString()); item.UpdateuserId = (reader["UpdateUserID"].ToString()); item.UpdateDate = Convert.ToDateTime(reader["UpdateDate"]); items.Add(item); } reader.Close(); reader.Dispose(); reader = null; SQLConnector.CloseConnection(); return(items); }
public static bool IsExists(string tableName, string idColumnName, string duplicateColumnName, int idColumnValue, string duplicateColumnValue) { string sql = string.Format("SELECT COUNT({0}) FROM {1} WHERE {0} <> {2} AND {3} = '{4}'", idColumnName, tableName, idColumnValue, duplicateColumnName, duplicateColumnValue); SQLConnector.OpenConnection(); SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.Transaction = SQLConnector.Transaction; command.CommandText = sql; command.CommandType = System.Data.CommandType.Text; object duplicateValue = command.ExecuteScalar(); if (Convert.ToInt32(duplicateValue) == 0) { return(false); } else { SQLConnector.CloseConnection(); return(true); } }
public Vendor GetData(int id) { SQLConnector.OpenConnection(); SqlCommand command = new SqlCommand(); command.Connection = SQLConnector.Connection; command.CommandText = string.Format("SELECT * FROM Vendor WHERE ID ={0}", id); command.CommandType = System.Data.CommandType.Text; SqlDataReader reader = command.ExecuteReader(); Vendor item = new Vendor(); if (reader.Read()) { item.IsNew = false; item.ID = Convert.ToInt32(reader["ID"]); item.UserName = (reader["UserName"].ToString()); item.Code = (reader["Code"].ToString()); item.Email = (reader["Email"]).ToString(); item.Address = (reader["Address"]).ToString(); item.ContactNo = (reader["ContactNo"]).ToString(); item.BankName = (reader["BankName"]).ToString(); item.AccountNo = (reader["AccountNo"]).ToString(); item.PaymentTerm = Convert.ToInt32(reader["PaymentTerm"]); item.IsActive = Convert.ToBoolean(reader["IsActive"]); item.EntryuserId = (reader["EntryUserID"].ToString()); item.UpdateuserId = (reader["UpdateUserID"].ToString()); item.UpdateDate = Convert.ToDateTime(reader["UpdateDate"]); } reader.Close(); reader.Dispose(); reader = null; SQLConnector.CloseConnection(); return(item); }