Shulou(Shulou.com)06/02 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.