static CustomersTbl MoveReaderDataToCustomersTblData(OleDbDataReader pDataReader)
{
CustomersTbl _CustomersTblData = new CustomersTbl();
_CustomersTblData.CustomerID = Convert.ToInt64(pDataReader["CustomerID"]);
_CustomersTblData.CompanyName = (pDataReader["CompanyName"] == DBNull.Value) ? "" : pDataReader["CompanyName"].ToString();
_CustomersTblData.ContactTitle = (pDataReader["ContactTitle"] == DBNull.Value) ? "" : pDataReader["ContactTitle"].ToString();
_CustomersTblData.ContactFirstName = (pDataReader["ContactFirstName"] == DBNull.Value) ? "" : pDataReader["ContactFirstName"].ToString();
_CustomersTblData.ContactLastName = (pDataReader["ContactLastName"] == DBNull.Value) ? "" : pDataReader["ContactLastName"].ToString();
_CustomersTblData.ContactAltFirstName = (pDataReader["ContactAltFirstName"] == DBNull.Value) ? "" : pDataReader["ContactAltFirstName"].ToString();
_CustomersTblData.ContactAltLastName = (pDataReader["ContactAltLastName"] == DBNull.Value) ? "" : pDataReader["ContactAltLastName"].ToString();
_CustomersTblData.Department = (pDataReader["Department"] == DBNull.Value) ? "" : pDataReader["Department"].ToString();
_CustomersTblData.BillingAddress = (pDataReader["BillingAddress"] == DBNull.Value) ? "" : pDataReader["BillingAddress"].ToString();
_CustomersTblData.City = (pDataReader["City"] == DBNull.Value) ? 0 : Convert.ToInt32(pDataReader["City"]);
_CustomersTblData.Province = (pDataReader["Province"] == DBNull.Value) ? "" : pDataReader["Province"].ToString();
_CustomersTblData.PostalCode = (pDataReader["PostalCode"] == DBNull.Value) ? "" : pDataReader["PostalCode"].ToString();
_CustomersTblData.Region = (pDataReader["Region"] == DBNull.Value) ? "" : pDataReader["Region"].ToString();
_CustomersTblData.PhoneNumber = (pDataReader["PhoneNumber"] == DBNull.Value) ? "" : pDataReader["PhoneNumber"].ToString();
_CustomersTblData.Extension = (pDataReader["Extension"] == DBNull.Value) ? "" : pDataReader["Extension"].ToString();
_CustomersTblData.FaxNumber = (pDataReader["FaxNumber"] == DBNull.Value) ? "" : pDataReader["FaxNumber"].ToString();
_CustomersTblData.CellNumber = (pDataReader["CellNumber"] == DBNull.Value) ? "" : pDataReader["CellNumber"].ToString();
_CustomersTblData.EmailAddress = (pDataReader["EmailAddress"] == DBNull.Value) ? "" : pDataReader["EmailAddress"].ToString();
_CustomersTblData.AltEmailAddress = (pDataReader["AltEmailAddress"] == DBNull.Value) ? "" : pDataReader["AltEmailAddress"].ToString();
_CustomersTblData.ContractNo = (pDataReader["ContractNo"] == DBNull.Value) ? "" : pDataReader["ContractNo"].ToString();
_CustomersTblData.CustomerTypeID = (pDataReader["CustomerTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(pDataReader["CustomerTypeID"]);
_CustomersTblData.EquipType = (pDataReader["EquipType"] == DBNull.Value) ? 0 : Convert.ToInt32(pDataReader["EquipType"]);
_CustomersTblData.CoffeePreference = (pDataReader["CoffeePreference"] == DBNull.Value) ? 0 : Convert.ToInt32(pDataReader["CoffeePreference"]);
_CustomersTblData.PriPrefQty = (pDataReader["PriPrefQty"] == DBNull.Value) ? 1 : Convert.ToDouble(pDataReader["PriPrefQty"]);
_CustomersTblData.PrefPrepTypeID = (pDataReader["PrefPrepTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(pDataReader["PrefPrepTypeID"]);
_CustomersTblData.PrefPackagingID = (pDataReader["PrefPackagingID"] == DBNull.Value) ? 0 : Convert.ToInt32(pDataReader["PrefPackagingID"]);
_CustomersTblData.SecondaryPreference = (pDataReader["SecondaryPreference"] == DBNull.Value) ? 0 : Convert.ToInt32(pDataReader["SecondaryPreference"]);
_CustomersTblData.SecPrefQty = (pDataReader["SecPrefQty"] == DBNull.Value) ? 1 : Convert.ToDouble(pDataReader["SecPrefQty"]);
_CustomersTblData.TypicallySecToo = (pDataReader["TypicallySecToo"] == DBNull.Value) ? false : Convert.ToBoolean(pDataReader["TypicallySecToo"]);
_CustomersTblData.PreferedAgent = (pDataReader["PreferedAgent"] == DBNull.Value) ? 0 : Convert.ToInt32(pDataReader["PreferedAgent"]);
_CustomersTblData.SalesAgentID = (pDataReader["SalesAgentID"] == DBNull.Value) ? 0 : Convert.ToInt32(pDataReader["SalesAgentID"]);
_CustomersTblData.MachineSN = (pDataReader["MachineSN"] == DBNull.Value) ? "" : pDataReader["MachineSN"].ToString();
_CustomersTblData.UsesFilter = (pDataReader["UsesFilter"] == DBNull.Value) ? false : Convert.ToBoolean(pDataReader["UsesFilter"]);
_CustomersTblData.autofulfill = (pDataReader["autofulfill"] == DBNull.Value) ? false : Convert.ToBoolean(pDataReader["autofulfill"]);
_CustomersTblData.enabled = (pDataReader["enabled"] == DBNull.Value) ? false : Convert.ToBoolean(pDataReader["enabled"]);
_CustomersTblData.PredictionDisabled = (pDataReader["PredictionDisabled"] == DBNull.Value) ? false : Convert.ToBoolean(pDataReader["PredictionDisabled"]);
_CustomersTblData.AlwaysSendChkUp = (pDataReader["AlwaysSendChkUp"] == DBNull.Value) ? false : Convert.ToBoolean(pDataReader["AlwaysSendChkUp"]);
_CustomersTblData.NormallyResponds = (pDataReader["NormallyResponds"] == DBNull.Value) ? false : Convert.ToBoolean(pDataReader["NormallyResponds"]);
_CustomersTblData.ReminderCount = (pDataReader["ReminderCount"] == DBNull.Value) ? 0 : Convert.ToInt32(pDataReader["ReminderCount"]);
_CustomersTblData.Notes = (pDataReader["Notes"] == DBNull.Value) ? "" : pDataReader["Notes"].ToString();
return _CustomersTblData;
}
public string UpdateCustomer(CustomersTbl ThisCustomerTblData, long CustomerIDToUpdate)
{
string errString = "";
string _connectionStr = ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
string _sqlCmd = CONST_SQL_CUSTOMERS_UPDATE;
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
#region Parameters
// Add data sent
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.CompanyName } );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.ContactTitle } );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.ContactFirstName} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.ContactLastName} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.ContactAltFirstName} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.ContactAltLastName} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.Department} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.BillingAddress} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.City} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.Province} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.PostalCode} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.Region } );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.PhoneNumber} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.Extension} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.FaxNumber} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.CellNumber} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.EmailAddress} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.AltEmailAddress} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.ContractNo} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.CustomerTypeID} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.EquipType} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.CoffeePreference} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.PriPrefQty} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.PrefPrepTypeID} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.PrefPackagingID} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.SecondaryPreference} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.SecPrefQty} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.TypicallySecToo} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.PreferedAgent} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.SalesAgentID} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.MachineSN} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.UsesFilter} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.autofulfill} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.enabled} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.PredictionDisabled} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.AlwaysSendChkUp} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.NormallyResponds} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.ReminderCount} );
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.Notes } );
// " WHERE CustomersTbl.CustomerID = ?)";
_cmd.Parameters.Add(new OleDbParameter { Value = CustomerIDToUpdate });
/// last paramatere Cust ID
///// others here
#endregion
try
{
_conn.Open();
if (_cmd.ExecuteNonQuery() > 0)
errString = "";
else
errString += " - error ";
}
catch (OleDbException oleErr)
{
// Handle exception.
errString += " ERROR: " + oleErr.Message;
}
finally
{
_conn.Close();
}
return errString;
}
}
public bool InsertCustomer(CustomersTbl ThisCustomerTblData, ref string pErrorStr)
{
bool _RecsInserted = false;
pErrorStr = "";
string _connectionStr = ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
string _sqlCmd = CONST_SQL_CUSTOMERS_INSERT;
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
#region Parameters
// first summary data
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.CompanyName }); // Line 1.1
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.ContactTitle }); // Line 1.2
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.ContactFirstName }); // Line 1.3
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.ContactLastName }); // Line 1.4
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.ContactAltFirstName }); // Line 1.5
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.ContactAltLastName }); // Line 2.1
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.Department }); // Line 2.2
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.BillingAddress }); // Line 2.3
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.City }); // Line 2.4
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.Province }); // Line 2.5
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.PostalCode });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.Region });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.PhoneNumber });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.Extension });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.FaxNumber }); // End line 3
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.CellNumber });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.EmailAddress });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.AltEmailAddress });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.ContractNo });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.CustomerTypeID }); // End Line 4
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.EquipType });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.CoffeePreference });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.PriPrefQty });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.PrefPrepTypeID });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.PrefPackagingID}); // 5
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.SecondaryPreference });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.SecPrefQty });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.TypicallySecToo });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.PreferedAgent });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.SalesAgentID }); // 6
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.MachineSN });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.UsesFilter });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.autofulfill });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.enabled });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.PredictionDisabled }); // 7
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.AlwaysSendChkUp });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.NormallyResponds });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.ReminderCount });
_cmd.Parameters.Add(new OleDbParameter { Value = ThisCustomerTblData.Notes });
#endregion
try
{
_conn.Open();
int _numInsert = _cmd.ExecuteNonQuery();
_RecsInserted = (_numInsert > 0);
}
catch (OleDbException oleErr)
{
// Handle exception.
pErrorStr = oleErr.Message;
_RecsInserted = false;
}
finally
{
_conn.Close();
}
return _RecsInserted;
}
}