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