How to complete a small-scale engineering quotation database system

Today, the editor will share with you the relevant knowledge points about how to complete a small project quotation database system. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article, let's take a look at it.

The function is very simple, complete the basic addition, deletion, modification and search.

Build the three-tier structure of the project

Interface design and beautification

Design and implementation of hierarchical Code

1. Model layer: because there is a primary foreign key relationship in the table design, a foreign key object is added when the table is mapped into an object to save the relevant data of the foreign key table.

/ / Foreign key entity private Product _ product; private Project _ project; public Project _ Project {get {return _ project;} set {_ project = value;}} public Product _ Product {get {return _ product } set {_ product = value;}}

two。 Data access layer: transfer between layers that encapsulate data into Model objects.

Personally, I don't like passing DataSet datasets between the three tiers. My understanding is to try not to have DataSet in the BLL layer, so I encapsulate all the acquired data into an IEnumerable collection in the DAL layer and return it.

Protected IEnumerable ToModelsByFK (SqlDataReader reader) {var list = new List (); while (reader.Read ()) {list.Add (ToModelByFK (reader));} return list;} public ProjectItem ToModelByFK (SqlDataReader reader) {ProjectItem projectItem = new ProjectItem () ProjectItem._Product = new Product (); projectItem._Project = new Project (); projectItem.ID = (int) ToModelValue (reader, "ID"); projectItem._Product.ID = (int) ToModelValue (reader, "ProductID"); projectItem._Project.ID = (int) ToModelValue (reader, "projectID") ProjectItem._Product.Manufacturer = ToModelValue (reader, "Manufacturer"). ToString (); projectItem._Product.Parameters = ToModelValue (reader, "Parameters"). ToString (); projectItem._Product.Price = decimal.Parse (ToModelValue (reader, "Price"). ToString (); projectItem._Product.ProductName = ToModelValue (reader, "ProductName"). ToString () ProjectItem._Product.Specification = ToModelValue (reader, "Specification"). ToString (); projectItem._Product.Unit = ToModelValue (reader, "Unit"). ToString (); projectItem.Count = (int) ToModelValue (reader, "Count"); projectItem.TotalMoney = (decimal) ToModelValue (reader, "TotalMoney"); projectItem.UnitPrice = (decimal) ToModelValue (reader, "UnitPrice") Return projectItem;} protected object ToModelValue (SqlDataReader reader,string columnName) {if (reader.IsDBNull (reader.GetOrdinal (columnName) {return null;} else {return reader [columnName];}}

Use the GetProjectByCondition method to return an IEnumerable collection encapsulated as an object

Public IEnumerable GetProjectByCondition (string projectName, string customerName, string contract, string tel) {StringBuilder sqlWhere = new StringBuilder ("select * from Project where 1x1"); List listParameters = new List (); if (! string.IsNullOrWhiteSpace (projectName)) {sqlWhere.AppendLine ("and projectName like @ projectName") ListParameters.Add (new SqlParameter ("projectName", "%" + projectName + "%");} if (! string.IsNullOrWhiteSpace (contract)) {sqlWhere.AppendLine ("and Contact like @ Contact"); listParameters.Add (new SqlParameter ("Contact", "%" + contract + "%")) } if (! string.IsNullOrWhiteSpace (customerName)) {sqlWhere.AppendLine ("and customer like @ customer"); listParameters.Add (new SqlParameter ("customer", "%" + customerName + "%")) } if (! string.IsNullOrWhiteSpace (tel)) {sqlWhere.AppendLine ("and tel like @ tel"); listParameters.Add (new SqlParameter ("tel", "%" + tel + "%")) } using (SqlDataReader reader = SqlHelper.ExecuteDataReader (sqlWhere.ToString (), listParameters.ToArray () {return ToModels (reader);}}

The UpdatePassChecked method accepts an object and gets the data through its properties.

Public int UpdatePassChecked (ProjectItem projectItem) {string sql = "UPDATE Project" + "SET" + "ProjectName = @ ProjectName" + ", Customer = @ Customer" + "Contact = @ Contact" + ", Tel = @ Tel" + " DeliveryPlace = @ DeliveryPlace "+", DeliveryTime = @ DeliveryTime "+", TransportCosts = @ TransportCosts "+", PaymentTerm = @ PaymentTerm "+", Bak = @ Bak "+" WHERE ID = @ ID " SqlParameter [] para = new SqlParameter [] {new SqlParameter ("@ ID", projectItem._Project.ID), new SqlParameter ("@ ProjectName", ToDBValue (projectItem._Project.ProjectName)), new SqlParameter ("@ Customer", ToDBValue (projectItem._Project.Customer)), new SqlParameter ("@ Contact") ToDBValue (projectItem._Project.Contact), new SqlParameter ("@ Tel", ToDBValue (projectItem._Project.Tel)), new SqlParameter ("@ DeliveryPlace", ToDBValue (projectItem._Project.DeliveryPlace)), new SqlParameter ("@ DeliveryTime", ToDBValue (projectItem._Project.DeliveryTime)), new SqlParameter ("@ TransportCosts") ToDBValue (projectItem._Project.TransportCosts)), new SqlParameter ("@ PaymentTerm", ToDBValue (projectItem._Project.PaymentTerm)), new SqlParameter ("@ Bak", ToDBValue (projectItem._Project.Bak))} Return SqlHelper.ExecuteNonQuery (sql, para);}

The Add method passes in an object that has encapsulated the data, and then returns a new object (including the newly created generated ID)

Public Product Add (Product product) {string sql = "INSERT INTO Product (ProductName, Specification, Manufacturer, Parameters, Price, Unit) output inserted.ID VALUES (@ ProductName, @ Specification, @ Manufacturer, @ Parameters, @ Price, @ Unit)" SqlParameter [] para = new SqlParameter [] {new SqlParameter ("@ ProductName", ToDBValue (product.ProductName)), new SqlParameter ("@ Specification", ToDBValue (product.Specification)), new SqlParameter ("@ Manufacturer", ToDBValue (product.Manufacturer)) New SqlParameter (@ Parameters ", ToDBValue (product.Parameters)), new SqlParameter (" @ Price ", ToDBValue (product.Price)), new SqlParameter (" @ Unit ", ToDBValue (product.Unit)),} Int newId = int) SqlHelper.ExecuteScalar (sql, para); return GetByID (newId);}

3. Business logic layer: the business logic layer is the core of the project, and the business logic code is usually implemented here.

Simple data validation:

Public bool UpdateThroughChecked (ProjectItem projectItem) {if (string.IsNullOrEmpty (projectItem._Project.ProjectName)) {throw new Exception ("Project name cannot be empty") } / / the customer name can be empty, but the contact cannot be empty, so if (string.IsNullOrEmpty (projectItem._Project.Contact)) {throw new Exception ("contact name cannot be empty") should be designed in the database. } if (string.IsNullOrEmpty (projectItem._Project.Tel)) {throw new Exception ("contact number cannot be empty");} if (string.IsNullOrEmpty (projectItem._Project.DeliveryPlace)) {throw new Exception ("place of delivery cannot be empty") } if (string.IsNullOrEmpty (projectItem._Project.DeliveryTime)) {throw new Exception ("delivery time cannot be empty");} if (projectItem._Project.TransportCosts 0;}

Calculate the total amount:

Public decimal GetProductTotalMoney (int projectID) {var list = new DAL.ProjectItemService () .GetProductTotalMoney (projectID); decimal totalMoney = 0.00M; foreach (var model in list) {totalMoney+=model.TotalMoney.Value;} return totalMoney;}

Get the data set according to the query criteria:

/ get the data set according to the query conditions / public IEnumerable GetProductsByCondition (string condition) {return new DAL.ProductService () .GetProductsByCondition (condition);}

Get a record according to ProjectItemID (encapsulated as an object)

Public Model.ProjectItem GetOneProjectItemByID (int ProjectItemID) {return new DAL.ProjectItemService () .GetOneProjectItemByID (ProjectItemID);}

Add a record:

/ add a new record according to the condition / public Model.Project AddPassCheckd (Model.Project modelProject) {if (string.IsNullOrEmpty (modelProject.ProjectName)) {throw new Exception ("Project name cannot be empty") } if (string.IsNullOrEmpty (modelProject.Customer)) {throw new Exception ("customer name cannot be empty");} if (string.IsNullOrEmpty (modelProject.Tel)) {throw new Exception ("contact number cannot be empty") } if (modelProject.TransportCosts 0 & & isSucceed; return isSucceed;}

4. Presentation layer (UI)

A simple form is nested within a form:

Public void LoadProjectListForm () {projectListForm= null; if (projectListForm==null) {projectListForm= new ProjectListForm ();} / remove the border projectListForm.FormBorderStyle = FormBorderStyle.None; projectListForm.TopLevel = whether the false;// form is a top-level form projectListForm.Dock = DockStyle.Fill / / fill projectListForm.Show (); / / do not write and cannot see panelContainer.Controls.Clear (); / / clear panelContainer.Controls.Add (projectListForm);}

Bind data:

My original idea was to bind the properties of an object directly in DataGridView, using the same as GridView, but I couldn't bind it all the time. What's wrong with that?

So we can only curve to save the country:

Void LoadData () {var list = new BLL.ProjectItemManager () .GetAllProducts (projectID); / / dataGridViewProjectItems.DataSource = list;// cannot add Row dataGridViewProjectItems.Rows.Clear () to a control when it is bound; foreach (var model in list) {int I = dataGridViewProjectItems.Rows.Add () DataGridViewProjectItems.Rows.cells ["idColumn"]. Value = model.ID; dataGridViewProjectItems.Rows[ I] .cells ["specificationColumn"]. Value = model._Product.Specification; dataGridViewProjectItems.Rows[ I] .cells ["productNameColumn"] .Value = model._Product.ProductName; dataGridViewProjectItems.Rows[ I] .cells ["manufacturerColumn"]. Value = model._Product.Manufacturer DataGridViewProjectItems.Rows.cells ["parametersColumn"]. Value = model._Product.Parameters; dataGridViewProjectItems.Rows[ I] .cells ["productIDColumn"]. Value = model._Product.ID; dataGridViewProjectItems.Rows[ I] .cells ["totalMoneyColumn"] .Value = model.TotalMoney; dataGridViewProjectItems.Rows[ I] .cells ["countColumn"]. Value = model.Count DataGridViewProjectItems.Rows[ I] .cells ["unitPriceColumn"]. Value = model.UnitPrice; dataGridViewProjectItems.Rows[ I] .cells ["projectIDColumn"]. Value = model._Project.ID;} these are all the contents of the article "how to complete a small project quotation database system". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.