public string Update(CustomersAccInfoTbl pCustomersAccInfoTbl, long pOrignal_CustomersAccInfoID)
{
string _result = string.Empty;
TrackerDb _TDB = new TrackerDb();
if (pCustomersAccInfoTbl.CustomerID == 0)
{
_result = "error: CustomerID cannot be 0";
}
else
{
#region UpdateParameters
if (pOrignal_CustomersAccInfoID > 0)
{
_TDB.AddWhereParams(pOrignal_CustomersAccInfoID, DbType.Int64); // check this line it assumes id field is int32
}
else
{
_TDB.AddWhereParams(pCustomersAccInfoTbl.CustomersAccInfoID, DbType.Int64, "@CustomersAccInfoID");
}
_TDB.AddParams(pCustomersAccInfoTbl.CustomerID, DbType.Int64, "@CustomerID");
_TDB.AddParams(pCustomersAccInfoTbl.RequiresPurchOrder, DbType.Int32, "@RequiresPurchOrder");
_TDB.AddParams(pCustomersAccInfoTbl.CustomerVATNo, DbType.String, "@CustomerVATNo");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr1, DbType.String, "@BillAddr1");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr2, DbType.String, "@BillAddr2");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr3, DbType.String, "@BillAddr3");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr4, DbType.String, "@BillAddr4");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr5, DbType.String, "@BillAddr5");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr1, DbType.String, "@ShipAddr1");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr2, DbType.String, "@ShipAddr2");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr3, DbType.String, "@ShipAddr3");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr4, DbType.String, "@ShipAddr4");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr5, DbType.String, "@ShipAddr5");
_TDB.AddParams(pCustomersAccInfoTbl.AccEmail, DbType.String, "@AccEmail");
_TDB.AddParams(pCustomersAccInfoTbl.AltAccEmail, DbType.String, "@AltAccEmail");
_TDB.AddParams(pCustomersAccInfoTbl.PaymentTermID, DbType.Int32, "@PaymentTermID");
_TDB.AddParams(pCustomersAccInfoTbl.Limit, DbType.Double, "@Limit");
_TDB.AddParams(pCustomersAccInfoTbl.FullCoName, DbType.String, "@FullCoName");
_TDB.AddParams(pCustomersAccInfoTbl.AccFirstName, DbType.String, "@AccFirstName");
_TDB.AddParams(pCustomersAccInfoTbl.AccLastName, DbType.String, "@AccLastName");
_TDB.AddParams(pCustomersAccInfoTbl.AltAccFirstName, DbType.String, "@AltAccFirstName");
_TDB.AddParams(pCustomersAccInfoTbl.AltAccLastName, DbType.String, "@AltAccLastName");
_TDB.AddParams(pCustomersAccInfoTbl.PriceLevelID, DbType.Int32, "@PriceLevelID");
_TDB.AddParams(pCustomersAccInfoTbl.InvoiceTypeID, DbType.Int32, "@InvoiceTypeID");
_TDB.AddParams(pCustomersAccInfoTbl.RegNo, DbType.String, "@RegNo");
_TDB.AddParams(pCustomersAccInfoTbl.BankAccNo, DbType.String, "@BankAccNo");
_TDB.AddParams(pCustomersAccInfoTbl.BankBranch, DbType.String, "@BankBranch");
_TDB.AddParams(pCustomersAccInfoTbl.Enabled, DbType.Boolean, "@Enabled");
_TDB.AddParams(pCustomersAccInfoTbl.Notes, DbType.String, "@Notes");
#endregion
// Now we have the parameters excute the SQL
_result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE);
_TDB.Close();
}
return(_result);
}
public List <CustomersAccInfoTbl> GetAll(string SortBy)
{
List <CustomersAccInfoTbl> _DataItems = new List <CustomersAccInfoTbl>();
TrackerDb _TDB = new TrackerDb();
string _sqlCmd = CONST_SQL_SELECT;
if (!String.IsNullOrEmpty(SortBy))
{
_sqlCmd += " ORDER BY " + SortBy; // Add order by string
}
// params would go here if need
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
CustomersAccInfoTbl _DataItem = new CustomersAccInfoTbl();
#region StoreThisDataItem
_DataItem.CustomersAccInfoID = (_DataReader["CustomersAccInfoID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomersAccInfoID"]);
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerID"]);
_DataItem.RequiresPurchOrder = (_DataReader["RequiresPurchOrder"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["RequiresPurchOrder"]);
_DataItem.CustomerVATNo = (_DataReader["CustomerVATNo"] == DBNull.Value) ? string.Empty : _DataReader["CustomerVATNo"].ToString();
_DataItem.BillAddr1 = (_DataReader["BillAddr1"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr1"].ToString();
_DataItem.BillAddr2 = (_DataReader["BillAddr2"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr2"].ToString();
_DataItem.BillAddr3 = (_DataReader["BillAddr3"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr3"].ToString();
_DataItem.BillAddr4 = (_DataReader["BillAddr4"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr4"].ToString();
_DataItem.BillAddr5 = (_DataReader["BillAddr5"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr5"].ToString();
_DataItem.ShipAddr1 = (_DataReader["ShipAddr1"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr1"].ToString();
_DataItem.ShipAddr2 = (_DataReader["ShipAddr2"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr2"].ToString();
_DataItem.ShipAddr3 = (_DataReader["ShipAddr3"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr3"].ToString();
_DataItem.ShipAddr4 = (_DataReader["ShipAddr4"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr4"].ToString();
_DataItem.ShipAddr5 = (_DataReader["ShipAddr5"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr5"].ToString();
_DataItem.AccEmail = (_DataReader["AccEmail"] == DBNull.Value) ? string.Empty : _DataReader["AccEmail"].ToString();
_DataItem.AltAccEmail = (_DataReader["AltAccEmail"] == DBNull.Value) ? string.Empty : _DataReader["AltAccEmail"].ToString();
_DataItem.PaymentTermID = (_DataReader["PaymentTermID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PaymentTermID"]);
_DataItem.Limit = (_DataReader["Limit"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["Limit"]);
_DataItem.FullCoName = (_DataReader["FullCoName"] == DBNull.Value) ? string.Empty : _DataReader["FullCoName"].ToString();
_DataItem.AccFirstName = (_DataReader["AccFirstName"] == DBNull.Value) ? string.Empty : _DataReader["AccFirstName"].ToString();
_DataItem.AccLastName = (_DataReader["AccLastName"] == DBNull.Value) ? string.Empty : _DataReader["AccLastName"].ToString();
_DataItem.AltAccFirstName = (_DataReader["AltAccFirstName"] == DBNull.Value) ? string.Empty : _DataReader["AltAccFirstName"].ToString();
_DataItem.AltAccLastName = (_DataReader["AltAccLastName"] == DBNull.Value) ? string.Empty : _DataReader["AltAccLastName"].ToString();
_DataItem.PriceLevelID = (_DataReader["PriceLevelID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PriceLevelID"]);
_DataItem.InvoiceTypeID = (_DataReader["InvoiceTypeID"] == DBNull.Value) ? InvoiceTypeTbl.CONST_DEFAULT_INVOICETYPEID : Convert.ToInt32(_DataReader["InvoiceTypeID"]);
_DataItem.RegNo = (_DataReader["RegNo"] == DBNull.Value) ? string.Empty : _DataReader["RegNo"].ToString();
_DataItem.BankAccNo = (_DataReader["BankAccNo"] == DBNull.Value) ? string.Empty : _DataReader["BankAccNo"].ToString();
_DataItem.BankBranch = (_DataReader["BankBranch"] == DBNull.Value) ? string.Empty : _DataReader["BankBranch"].ToString();
_DataItem.Enabled = (_DataReader["Enabled"] == DBNull.Value) ? true : Convert.ToBoolean(_DataReader["Enabled"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
#endregion
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TDB.Close();
return(_DataItems);
}
public CustomersAccInfoTbl GetByCustomerID(long pCustomerID)
{
CustomersAccInfoTbl _DataItem = new CustomersAccInfoTbl();
_DataItem.CustomerID = pCustomerID; // assing it to non zero so we can do error checking
string _sql = CONST_SQL_SELECTBYCUSTID;
TrackerDb _TDB = new TrackerDb();
// params would go here if need
_TDB.AddWhereParams(pCustomerID, DbType.Int64, "@CustomerID");
// _sql = _sql.Replace("?", pCustomerID.ToString());
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sql);
if (_DataReader != null)
{
if (_DataReader.Read())
{
#region StoreThisDataItem
_DataItem.CustomersAccInfoID = (_DataReader["CustomersAccInfoID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomersAccInfoID"]);
_DataItem.RequiresPurchOrder = (_DataReader["RequiresPurchOrder"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["RequiresPurchOrder"]);
_DataItem.CustomerVATNo = (_DataReader["CustomerVATNo"] == DBNull.Value) ? string.Empty : _DataReader["CustomerVATNo"].ToString();
_DataItem.BillAddr1 = (_DataReader["BillAddr1"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr1"].ToString();
_DataItem.BillAddr2 = (_DataReader["BillAddr2"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr2"].ToString();
_DataItem.BillAddr3 = (_DataReader["BillAddr3"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr3"].ToString();
_DataItem.BillAddr4 = (_DataReader["BillAddr4"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr4"].ToString();
_DataItem.BillAddr5 = (_DataReader["BillAddr5"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr5"].ToString();
_DataItem.ShipAddr1 = (_DataReader["ShipAddr1"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr1"].ToString();
_DataItem.ShipAddr2 = (_DataReader["ShipAddr2"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr2"].ToString();
_DataItem.ShipAddr3 = (_DataReader["ShipAddr3"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr3"].ToString();
_DataItem.ShipAddr4 = (_DataReader["ShipAddr4"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr4"].ToString();
_DataItem.ShipAddr5 = (_DataReader["ShipAddr5"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr5"].ToString();
_DataItem.AccEmail = (_DataReader["AccEmail"] == DBNull.Value) ? string.Empty : _DataReader["AccEmail"].ToString();
_DataItem.AltAccEmail = (_DataReader["AltAccEmail"] == DBNull.Value) ? string.Empty : _DataReader["AltAccEmail"].ToString();
_DataItem.PaymentTermID = (_DataReader["PaymentTermID"] == DBNull.Value) ? _DataItem.PaymentTermID : Convert.ToInt32(_DataReader["PaymentTermID"]);
_DataItem.Limit = (_DataReader["Limit"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["Limit"]);
_DataItem.FullCoName = (_DataReader["FullCoName"] == DBNull.Value) ? string.Empty : _DataReader["FullCoName"].ToString();
_DataItem.AccFirstName = (_DataReader["AccFirstName"] == DBNull.Value) ? string.Empty : _DataReader["AccFirstName"].ToString();
_DataItem.AccLastName = (_DataReader["AccLastName"] == DBNull.Value) ? string.Empty : _DataReader["AccLastName"].ToString();
_DataItem.AltAccFirstName = (_DataReader["AltAccFirstName"] == DBNull.Value) ? string.Empty : _DataReader["AltAccFirstName"].ToString();
_DataItem.AltAccLastName = (_DataReader["AltAccLastName"] == DBNull.Value) ? string.Empty : _DataReader["AltAccLastName"].ToString();
_DataItem.PriceLevelID = (_DataReader["PriceLevelID"] == DBNull.Value) ? _DataItem.PriceLevelID : Convert.ToInt32(_DataReader["PriceLevelID"]);
_DataItem.InvoiceTypeID = (_DataReader["InvoiceTypeID"] == DBNull.Value) ? _DataItem.InvoiceTypeID : Convert.ToInt32(_DataReader["InvoiceTypeID"]);
_DataItem.RegNo = (_DataReader["RegNo"] == DBNull.Value) ? string.Empty : _DataReader["RegNo"].ToString();
_DataItem.BankAccNo = (_DataReader["BankAccNo"] == DBNull.Value) ? string.Empty : _DataReader["BankAccNo"].ToString();
_DataItem.BankBranch = (_DataReader["BankBranch"] == DBNull.Value) ? string.Empty : _DataReader["BankBranch"].ToString();
_DataItem.Enabled = (_DataReader["Enabled"] == DBNull.Value) ? true : Convert.ToBoolean(_DataReader["Enabled"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
#endregion
}
_DataReader.Close();
}
_TDB.Close();
return(_DataItem);
}
public string Insert(CustomersAccInfoTbl pCustomersAccInfoTbl)
{
string _result = string.Empty;
if (pCustomersAccInfoTbl.CustomerID == 0)
{
_result = "error: customer number cannot be 0";
}
else
{
TrackerDb _TDB = new TrackerDb();
#region InsertParameters
_TDB.AddParams(pCustomersAccInfoTbl.CustomerID, DbType.Int64, "@CustomerID");
_TDB.AddParams(pCustomersAccInfoTbl.RequiresPurchOrder, DbType.Int32, "@RequiresPurchOrder");
_TDB.AddParams(pCustomersAccInfoTbl.CustomerVATNo, DbType.String, "@CustomerVATNo");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr1, DbType.String, "@BillAddr1");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr2, DbType.String, "@BillAddr2");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr3, DbType.String, "@BillAddr3");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr4, DbType.String, "@BillAddr4");
_TDB.AddParams(pCustomersAccInfoTbl.BillAddr5, DbType.String, "@BillAddr5");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr1, DbType.String, "@ShipAddr1");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr2, DbType.String, "@ShipAddr2");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr3, DbType.String, "@ShipAddr3");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr4, DbType.String, "@ShipAddr4");
_TDB.AddParams(pCustomersAccInfoTbl.ShipAddr5, DbType.String, "@ShipAddr5");
_TDB.AddParams(pCustomersAccInfoTbl.AccEmail, DbType.String, "@AccEmail");
_TDB.AddParams(pCustomersAccInfoTbl.AltAccEmail, DbType.String, "@AltAccEmail");
_TDB.AddParams(pCustomersAccInfoTbl.PaymentTermID, DbType.Int32, "@PaymentTermID");
_TDB.AddParams(pCustomersAccInfoTbl.Limit, DbType.Currency, "@Limit");
_TDB.AddParams(pCustomersAccInfoTbl.FullCoName, DbType.String, "@FullCoName");
_TDB.AddParams(pCustomersAccInfoTbl.AccFirstName, DbType.String, "@AccFirstName");
_TDB.AddParams(pCustomersAccInfoTbl.AccLastName, DbType.String, "@AccLastName");
_TDB.AddParams(pCustomersAccInfoTbl.AltAccFirstName, DbType.String, "@AltAccFirstName");
_TDB.AddParams(pCustomersAccInfoTbl.AltAccLastName, DbType.String, "@AltAccLastName");
_TDB.AddParams(pCustomersAccInfoTbl.PriceLevelID, DbType.Int32, "@PriceLevelID");
_TDB.AddParams(pCustomersAccInfoTbl.InvoiceTypeID, DbType.Int32, "@InvoiceTypeID");
_TDB.AddParams(pCustomersAccInfoTbl.RegNo, DbType.String, "@RegNo");
_TDB.AddParams(pCustomersAccInfoTbl.BankAccNo, DbType.String, "@BankAccNo");
_TDB.AddParams(pCustomersAccInfoTbl.BankBranch, DbType.String, "@BankBranch");
_TDB.AddParams(pCustomersAccInfoTbl.Enabled, DbType.Boolean, "@Enabled");
_TDB.AddParams(pCustomersAccInfoTbl.Notes, DbType.String, "@Notes");
#endregion
// Now we have the parameters excute the SQL
_result = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT);
_TDB.Close();
}
return(_result);
}
public string Delete(CustomersAccInfoTbl pCustomersAccInfoTbl)
{
return(Delete(pCustomersAccInfoTbl.CustomersAccInfoID));
}
public string Update(CustomersAccInfoTbl pCustomersAccInfoTbl)
{
return(Update(pCustomersAccInfoTbl, 0));
}
