public int InsertCustomer(CustomerModel.Customer customer)
{
string SQL = @"Insert into Sales.Customers values(
@CompanyName,@ContactName,@ContactTitle,@CreationDate,@Address,@City,
@Region,@PostalCode,@Country,@Phone,@Fax)
Select Cast(SCOPE_IDENTITY() as int)";
int id = 0;
using (SqlConnection conn = new SqlConnection(this.GetDBconnectionstring()))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(SQL, conn);
cmd.Parameters.Add(new SqlParameter("@CompanyName", customer.CompanyName));
cmd.Parameters.Add(new SqlParameter("@ContactName", customer.ContactName));
cmd.Parameters.Add(new SqlParameter("@ContactTitle", customer.ContactTitle));
cmd.Parameters.Add(new SqlParameter("@CreationDate", string.Format("{0:yyyy/MM/dd}", customer.CreationDate)));
cmd.Parameters.Add(new SqlParameter("@Address", customer.Address));
cmd.Parameters.Add(new SqlParameter("@City", customer.City));
cmd.Parameters.Add(new SqlParameter("@Region", customer.Region));
cmd.Parameters.Add(new SqlParameter("@PostalCode", customer.PostalCode));
cmd.Parameters.Add(new SqlParameter("@Country", customer.Country));
cmd.Parameters.Add(new SqlParameter("@Phone", customer.Phone));
cmd.Parameters.Add(new SqlParameter("@Fax", customer.Fax));
id = (int)cmd.ExecuteScalar();
conn.Close();
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e);
}
}
return(id);
}
public List <CustomerModel.Customer> GetCustomer(CustomerModel.Customer customer)
{
DataTable dt = new DataTable();
string SQL = @"Select CustomerId,CompanyName,ContactName,Codeval from Sales.Customers a join dbo.CodeTable b on a.ContactTitle=b.CodeId where CustomerID like @CustomerID and
CompanyName like @CompanyName and ContactName like @ContactName and ContactTitle like @ContactTitle and CodeType='TITLE'";
using (SqlConnection conn = new SqlConnection(this.GetDBconnectionstring()))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(SQL, conn);
cmd.Parameters.Add(new SqlParameter("@CustomerID", customer.CustomerID == null? "%%" : "%" + Convert.ToString(customer.CustomerID) + "%"));
cmd.Parameters.Add(new SqlParameter("@CompanyName", customer.CompanyName == null ? "%%" : "%" + Convert.ToString(customer.CompanyName) + "%"));
cmd.Parameters.Add(new SqlParameter("@ContactName", customer.ContactName == null ? "%%" : Convert.ToString(customer.ContactName)));
cmd.Parameters.Add(new SqlParameter("@ContactTitle", customer.ContactTitle == null ? "%%" : Convert.ToString(customer.ContactTitle)));
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(dt);
conn.Close();
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e);
}
}
return(this.MapCustomerDataToList(dt));
}
public ActionResult InsertCustomerAction(CustomerModel.Customer customer)
{
ViewBag.CustomerID = cs.InsertCustomer(customer);
return(Redirect("Index"));
}
public JsonResult SearchResult(CustomerModel.Customer customer)
{
var result = cs.GetCustomer(customer);
return(Json(result, JsonRequestBehavior.AllowGet));
}
