protected virtual iCustomer GetCustomerFromReader(IDataReader reader, bool singleRecord)
{
if (singleRecord)
{
reader.Read();
}
iCustomer customer = new iCustomer(
Convert.ToInt32(reader["id"]),
reader["customertype"].ToString(),
reader["email"].ToString(),
reader["password"].ToString(),
reader["company"].ToString(),
reader["firstname"].ToString(),
reader["lastname"].ToString(),
Convert.ToInt32(reader["streetno"]),
reader["streetnosuffix"].ToString(),
reader["address1"].ToString(),
reader["address2"].ToString(),
reader["pobox"].ToString(),
reader["city"].ToString(),
reader["province"].ToString(),
reader["zip"].ToString(),
reader["country"].ToString(),
reader["phone"].ToString(),
reader["cellphone"].ToString(),
reader["usertype"].ToString(),
Convert.ToBoolean(reader["active"]),
Convert.ToDateTime(reader["dateadded"])
);
return(customer);
}
public override bool Update(iCustomer c)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
string q = string.Empty;
q = @"
UPDATE [dbo].[customers]
SET [company] = @pCompany
,[firstname] = @pfname
,[lastname] = @plname
,[streetno] = @pstrno
,[streetnosuffix] = @pstnsfx
,[address1] = @padrs1
,[address2] = @padrs2
,[pobox] = @ppobox
,[city] = @pcity
,[province] = @pstate
,[zip] = @pzip
,[country] = @pcountry
,[phone] = @pphone
,[cellphone] = @pcell
WHERE [id] = @pid";
SqlCommand cmd = new SqlCommand(q, conn);
cmd.CommandText = q;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@pid", SqlDbType.Int).Value = c.Id;
cmd.Parameters.Add("@pCompany", SqlDbType.VarChar).Value = c.Company;
cmd.Parameters.Add("@pfname", SqlDbType.VarChar).Value = c.FirstName;
cmd.Parameters.Add("@plname", SqlDbType.VarChar).Value = c.LastName;
cmd.Parameters.Add("@pstrno", SqlDbType.Int).Value = c.StreetNo;
cmd.Parameters.Add("@pstnsfx", SqlDbType.VarChar).Value = c.StreetNoSuffix;
cmd.Parameters.Add("@padrs1", SqlDbType.VarChar).Value = c.Address1;
cmd.Parameters.Add("@padrs2", SqlDbType.VarChar).Value = c.Address2;
cmd.Parameters.Add("@ppobox", SqlDbType.VarChar).Value = c.POBox;
cmd.Parameters.Add("@pcity", SqlDbType.VarChar).Value = c.City;
cmd.Parameters.Add("@pstate", SqlDbType.VarChar).Value = c.State;
cmd.Parameters.Add("@pzip", SqlDbType.VarChar).Value = c.ZIP;
cmd.Parameters.Add("@pcountry", SqlDbType.VarChar).Value = c.Country;
cmd.Parameters.Add("@pphone", SqlDbType.VarChar).Value = c.Phone;
cmd.Parameters.Add("@pcell", SqlDbType.VarChar).Value = c.CellPhone;
try
{
conn.Open();
int rc = ExecuteNonQuery(cmd);
return(rc == 1);
}
catch (SqlException ex)
{
ErrorHandler.writeSQLExceptionToLogFile(ex);
return(false);
}
finally
{
conn.Close();
}
}
}
public abstract bool Update(iCustomer c);
public abstract int Insert(iCustomer c);
public override int Insert(iCustomer c)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
string q = string.Empty;
q = @"
INSERT INTO [dbo].[customers]
([customertype]
,[email]
,[password]
,[company]
,[firstname]
,[lastname]
,[streetno]
,[streetnosuffix]
,[address1]
,[address2]
,[pobox]
,[city]
,[province]
,[zip]
,[country]
,[phone]
,[cellphone]
,[usertype]
,[active]
)
VALUES (
@pCustomerType,
@pEmail,
@pPwd,
@pComp,
@pFname,
@pLname,
@pStrNo,
@pStrSfx,
@pAddress1,
@pAddress2,
@pPOBOX,
@pCity,
@pState,
@pZIP,
@pCountry,
@pPhone,
@pCell,
@pUType,
@pActive
);
SELECT SCOPE_IDENTITY()";
SqlCommand cmd = new SqlCommand(q, conn);
cmd.CommandText = q;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@pCustomerType", SqlDbType.VarChar).Value = c.CustomerType;
cmd.Parameters.Add("@pEmail", SqlDbType.VarChar).Value = c.Email;
cmd.Parameters.Add("@pPwd", SqlDbType.VarChar).Value = c.Password;
cmd.Parameters.Add("@pComp", SqlDbType.VarChar).Value = c.Company;
cmd.Parameters.Add("@pFname", SqlDbType.VarChar).Value = c.FirstName;
cmd.Parameters.Add("@pLname", SqlDbType.VarChar).Value = c.LastName;
cmd.Parameters.Add("@pStrNo", SqlDbType.Int).Value = c.StreetNo;
cmd.Parameters.Add("@pStrSfx", SqlDbType.VarChar).Value = c.StreetNoSuffix;
cmd.Parameters.Add("@pAddress1", SqlDbType.VarChar).Value = c.Address1;
cmd.Parameters.Add("@pAddress2", SqlDbType.VarChar).Value = c.Address2;
cmd.Parameters.Add("@pPOBOX", SqlDbType.VarChar).Value = c.POBox;
cmd.Parameters.Add("@pCity", SqlDbType.VarChar).Value = c.City;
cmd.Parameters.Add("@pState", SqlDbType.VarChar).Value = c.State;
cmd.Parameters.Add("@pZIP", SqlDbType.VarChar).Value = c.ZIP;
cmd.Parameters.Add("@pCountry", SqlDbType.VarChar).Value = c.Country;
cmd.Parameters.Add("@pPhone", SqlDbType.VarChar).Value = c.Phone;
cmd.Parameters.Add("@pCell", SqlDbType.VarChar).Value = c.CellPhone;
cmd.Parameters.Add("@pUType", SqlDbType.VarChar).Value = c.UserType;
cmd.Parameters.Add("@pActive", SqlDbType.Bit).Value = c.Active;
try
{
conn.Open();
int rc = Convert.ToInt32(ExecuteScalar(cmd));
return(rc);
}
catch (SqlException ex)
{
ErrorHandler.writeSQLExceptionToLogFile(ex);
return(0);
}
finally
{
conn.Close();
}
}
}