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);
}
}
