public IList<Customer> GetAllCustomers() {
IList<Customer> cusList = new List<Customer>();
using (DbConnection conn = new SqlConnection(ConnectionStr))
{
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT [CustomerId]
,[FirstName]
,[LastName]
,[Company]
,[Address]
,[City]
,[State]
,[Country]
,[PostalCode]
,[Phone]
,[Fax]
,[Email]
,[SupportRepId]
FROM [dbo].[Customer]";
conn.Open();
using (DbDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Customer c = new Customer()
{
CustomerId = rdr.GetInt32(0),
FirstName = rdr.GetString(1),
LastName = rdr.GetString(2),
Company = rdr.IsDBNull(3) ? null : rdr.GetString(3),
Address = rdr.IsDBNull(4) ? null : rdr.GetString(4),
City = rdr.IsDBNull(5) ? null : rdr.GetString(5),
State = rdr.IsDBNull(6) ? null : rdr.GetString(6),
Country = rdr.IsDBNull(7) ? null : rdr.GetString(7),
PostalCode = rdr.IsDBNull(8) ? null : rdr.GetString(8),
Phone = rdr.IsDBNull(9) ? null : rdr.GetString(9),
Fax = rdr.IsDBNull(10) ? null : rdr.GetString(10),
Email = rdr.GetString(11),
SupportRepId =rdr.GetInt32(12)
};
cusList.Add(c);
}
}
}
}
return cusList;
}
public void CreateCustomer(Customer cus)
{
using (DbConnection conn = new SqlConnection(ConnectionStr))
{
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO [dbo].[Customer]
([FirstName]
,[LastName]
,[Company]
,[Address]
,[City]
,[State]
,[Country]
,[PostalCode]
,[Phone]
,[Fax]
,[Email]
,[SupportRepId])
VALUES(
@FirstName,
@LastName,
@Company,
@Address,
@City,
@State,
@Country,
@PostalCode,
@Phone,
@Fax,
@Email,
@SupportRepId)";
DbParameter pLn = cmd.CreateParameter();
pLn.DbType = System.Data.DbType.String;
pLn.ParameterName = "@LastName";
pLn.Value = cus.LastName;
cmd.Parameters.Add(pLn);
DbParameter pFn = cmd.CreateParameter();
pFn.DbType = System.Data.DbType.String;
pFn.ParameterName = "@FirstName";
pFn.Value = cus.FirstName;
cmd.Parameters.Add(pFn);
DbParameter pCompany = cmd.CreateParameter();
pCompany.DbType = System.Data.DbType.String;
pCompany.ParameterName = "@Company";
pCompany.Value = cus.Company;
cmd.Parameters.Add(pCompany);
DbParameter pAddress = cmd.CreateParameter();
pAddress.DbType = System.Data.DbType.String;
pAddress.ParameterName = "@Address";
pAddress.Value = cus.Address;
cmd.Parameters.Add(pAddress);
DbParameter pCity = cmd.CreateParameter();
pCity.DbType = System.Data.DbType.String;
pCity.ParameterName = "@City";
pCity.Value = cus.City;
cmd.Parameters.Add(pCity);
DbParameter pState = cmd.CreateParameter();
pState.DbType = System.Data.DbType.String;
pState.ParameterName = "@State";
pState.Value = cus.State;
cmd.Parameters.Add(pState);
DbParameter pCountry = cmd.CreateParameter();
pCountry.DbType = System.Data.DbType.String;
pCountry.ParameterName = "@Country";
pCountry.Value = cus.Country;
cmd.Parameters.Add(pCountry);
DbParameter pPostalCode = cmd.CreateParameter();
pPostalCode.DbType = System.Data.DbType.String;
pPostalCode.ParameterName = "@PostalCode";
pPostalCode.Value = cus.PostalCode;
cmd.Parameters.Add(pPostalCode);
DbParameter pPhone = cmd.CreateParameter();
pPhone.DbType = System.Data.DbType.String;
pPhone.ParameterName = "@Phone";
pPhone.Value = cus.Phone;
cmd.Parameters.Add(pPhone);
DbParameter pFax = cmd.CreateParameter();
pFax.DbType = System.Data.DbType.String;
pFax.ParameterName = "@Fax";
pFax.Value = cus.Fax;
cmd.Parameters.Add(pFax);
DbParameter pEmail = cmd.CreateParameter();
pEmail.DbType = System.Data.DbType.String;
pEmail.ParameterName = "@Email";
pEmail.Value = cus.Email;
cmd.Parameters.Add(pEmail);
DbParameter pSupportRepId = cmd.CreateParameter();
pSupportRepId.DbType = System.Data.DbType.Int32;
pSupportRepId.ParameterName = "@SupportRepId";
pSupportRepId.Value = cus.SupportRepId;
cmd.Parameters.Add(pSupportRepId);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
public Customer GetCustomerById(int id) {
Customer cus = new Customer();
using (DbConnection conn = new SqlConnection(ConnectionStr))
{
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT [CustomerId]
,[FirstName]
,[LastName]
,[Company]
,[Address]
,[City]
,[State]
,[Country]
,[PostalCode]
,[Phone]
,[Fax]
,[Email]
,[SupportRepId]
FROM [dbo].[Customer]
WHERE [CustomerId]=@Id";
DbParameter pCusId = cmd.CreateParameter();
pCusId.DbType = System.Data.DbType.Int32;
pCusId.ParameterName = "@Id";
pCusId.Value = id;
cmd.Parameters.Add(pCusId);
conn.Open();
using (DbDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Customer c = new Customer()
{
CustomerId = rdr.GetInt32(0),
FirstName = rdr.GetString(1),
LastName = rdr.GetString(2),
Company = rdr.IsDBNull(3) ? null : rdr.GetString(3),
Address = rdr.IsDBNull(4) ? null : rdr.GetString(4),
City = rdr.IsDBNull(5) ? null : rdr.GetString(5),
State = rdr.IsDBNull(6) ? null : rdr.GetString(6),
Country = rdr.IsDBNull(7) ? null : rdr.GetString(7),
PostalCode = rdr.IsDBNull(8) ? null : rdr.GetString(8),
Phone = rdr.IsDBNull(9) ? null : rdr.GetString(9),
Fax = rdr.IsDBNull(10) ? null : rdr.GetString(10),
Email = rdr.GetString(11),
SupportRepId = rdr.GetInt32(12)
};
cus= c;
}
}
}
}
return cus;
}
public ActionResult Create(Customer cus)
{
CustomerRepository cusRepo = new CustomerRepository();
cusRepo.CreateCustomer(cus);
return RedirectToAction("Index");
}