vb.net+ three-storey computer room charge system combination Query

The relationship combination query has been used for 4 days. Every day in pain, racked his brains, a code to stare big eyes bullish long time, sometimes. Because two sentences are reversed. Is nothing; sometimes, because table can be converted to an entity type. Trapped himself. Think is half a day. The situation is constant. Read a lot of brother Siang the code, they share their own code. In order to give everyone a little something, give yourself to share with everyone. I hope you can give us some advice.

steps:

(1), first establish the entity, the entity is used to store variables.

(2), build b layer, B layer in addition to call D layer function, at the same time, it also has the ability to convert Chinese characters to fields in SQL.

(3), build D layer. I call the view in the D layer, and I'm supposed to separate the student watch from the card table (this matter), assuming you can only query a single table. You can use the query table directly, assuming that the data is not just on a single table. To try out the view. It's very easy.

(4), the establishment of U-layer, the U-layer is mainly to check whether the format is correct (mainly there is no empty, in the code has specific instructions.) See the gaze in the code is clear).

three-storey design:

U-Layer Design:

Why do I write the U-layer first? Should I write the entity (E) layer first? In fact, I was the first to write the U-layer. As I do not know, I have used those entities. It includes not only the fields of the tables in the database, but also some other parameters.

U-layer Interface design:

U-Layer Code:

Imports System.Data.SqlClientImports System.Windows.FormsPublic Class frmoperstuinfo Private Sub Btnqurry_click ( Sender as Object, e as EventArgs) Handles Btnqurry.click '--------------------------------------------------------- -------' first row query cannot be empty.        If the party's first act is empty, the conditions behind it cannot be content. ' When the first combo box is not empty, the second row cannot be empty. At the same time, assuming that the first combo box is empty, the second combo box cannot have the contents ' when the second combo box is not empty. The third line cannot be empty.
        '-----------------------------------------------------------------Dim querystudent as New Entity.querystud Ententity ' This entity is used to pass the entity's Dim QUERYSTUDENTBLL as New BLL. Qurrystudentbll Dim ControlArray (2) as Control Dim table As System.Data.DataTable ' receives the entity, but is not using the entity type. The DataTable is returned, because Datagirdview will be more comfortable. Dim sqlstring As String SQL query statement Dim relation1 As String Dim rel            Ation2 as String Try controlarray (0) = Combofield1 ' First row controlarray (1) = Combochar1 ControlArray (2) = TextBox1 Querystudent.field1 = Querystudentbll.changefield (combofield1. Text) SqlString = "SELECT * from V_querystudent WHERE" & Querystudent.field1 & Combochar1.text & "                 ' & TextBox1.Text & ' ' "When there is only the first line there is something if Commonfunction.issomeemptytext (controlarray) Then ' input is empty Exit Sub End If if Comborela1.Text.Trim <> "then ' first combinationbox is not empty when ControlArray (0) = Combofield2 ' second line ControlArray (1) = Combochar2 contr Olarray (2) = TextBox2 Querystudent.field2 = Querystudentbll.changefield (combofield2.text) re Lation1 = Querystudentbll.changerelation (comborela1.text) SqlString = SqlString & "" & Relation1 & Amp "& Querystudent.field2 & Combochar2.text & TextBox2.Text ' When there is only the first line there is something if commonfunction . Issomeemptytext (ControlArray) then Exit Sub End if If Comborela2.Text.Tri M <> "then" the second combo box is not empty.                    The third line must not be empty controlarray (0) = Combofield3 ' second row controlarray (1) = Combochar3 ControlArray (2) = TextBox3 querystudent.field3 = Querystudentbll.changefield (combofield3.text ) Relation2 = querystudentbll.changerelation (comborela2.text) Sqlstring = SqlString & "" & Relation2 & "& querystudent.field3 & Combochar3.text & TextBox3.Text                    ' When there's only one thing in the first line, if Commonfunction.issomeemptytext (ControlArray) then Exit Sub End If End If Else comborela2.text = "" ' When the first combo box is empty. The second combo box must be an empty End If querystudent.field1 = combofield1.            Text ' querystudent.field2 in entity = combofield2.text querystudent.field3 = Combofield3.text Querystudent.char1 = Combochar1.text Querystudent.char2 = Combochar2.text Querystudent.char3 =            Combochar3.text Querystudent.relation1 = Comborela1.text Querystudent.relation2 = Comborela2.text Querystudent.content1 = TextBox1.Text Querystudent.content2 = TextBox2.Text querystudent.co     Ntent3 = TextBox3.Text querystudent.sqlstring = SqlString ' string is also passed to it       Table = querystudentbll.querystudent (querystudent) ' Datagridview1.datasource = Table ' Binding for the table type that receives the return value Catch ex as Exception MsgBox (ex. Message) End Try End Sub

Public functions:

Public Class commonfunction Public Shared Function isallemptytext (ByVal frm as Form) as Boolean Dim control as N EW control for each control in frm. The controls ' traverse the entire control in the window ' MsgBox (frm. Controls.Count) If TypeOf control is a textbox then ' infer that the controls are not text boxes. Text.trim = "Then ' infers whether the text box contents are empty MsgBox (control. Tag.tostring + "cannot be empty. ", vbOKOnly," warm tip ") control. Focus () Return True Exit Function End If ElseIf TypeOf con Trol is a ComboBox then ' inference control is not a combo box if controls. Text.trim = "Then MsgBox" (Control. Tag.tostring + "cannot be empty! ", vbOKOnly," warm Tips ") Return True Exit Function End If End If Next return False End Function ' infers whether the Text property of the control in the control array is empty, and returns true if NULL when the public Shared Function Issomeemptytext (ByVal Arraycontrol () as ControL) as Boolean Dim control as New control for every control in Arraycontrol ' iterates over all elements of the array If TypeOf Control is a textbox then ' infer if the controls are not text boxes. Text.trim = "Then ' infers whether the text box content is empty ' MsgBox (control. Tag.tostring + "cannot be empty. ", vbOKOnly," the Warm Hint ") MsgBox (control. Tag.tostring + "cannot be empty! ", vbOKOnly," warm tip ") control. Focus () Return True Exit Function End If ElseIf TypeOf con Trol is a ComboBox then ' inference control is not a combo box if controls. Text.trim = "Then ' MsgBox (control. Tag.tostring + "cannot be empty! ", vbOKOnly," the Warm Hint ") MsgBox (" Can't be empty!
", vbOKOnly," warm Tips ") Return True Exit Function End If End If Next Return False End functionend Class

Above is a window,is used to invoke layer B,The following is a class. is used to infer the format (whether there is a control that should not be empty) in layman's terms.

e-Layer Code:

Public Class QURRYSTUDENTBLL Public Function changeobject (ByVal qurrystudent as entity.querystudententity) as Entity.qu Erystudententity qurrystudent.field1 = Changefield (qurrystudent.field1) qurrystudent.field2 = ChangeField (qu RRYSTUDENT.FIELD2) qurrystudent.field3 = Changefield (qurrystudent.field3) ' Here char is not required to convert Qurrystude Nt.        Relation1 = Changerelation (qurrystudent.relation1) Qurrystudent.relation2 = changerelation (qurrystudent.relation2)        Return qurrystudent End function Public function Changefield (ByVal Strfield As String) As String transform field            Dim Strfields as String = "" Select Case Strfield Case "card number" Strfields = "Cardno"            Case "study number" Strfields = "Studentno" case "name" Strfields = "Studentname" Case "gender" strfields = "Sex" case "system" Strfields = "Department" Cas               E "Grade" Strfields = "Grade" Case "class" Strfields = "class" End Select Return Strfields End function Public Function changerelation (ByVal strrelation As String) As String ' conversion combination Dim strrelations as St                Ring = "Select Case strrelation case" with "strrelations =" and "Case" or "        Strrelations = "or" case "" strrelations = "" Can also give it a null value in the End Select  Return strrelations End function Public Function querystudent (ByVal QUERYSTUDENTBLL as Entity.querystudententity) As System.Data.DataTable Dim Querystudentdal as New DAL. Studentdal Dim querystudents as New System.Data.DataTable querystudents = querystudentdal.querystudent (Query STUDENTBLL) Return querystudents End functionend Class

With the U-layer, you will be able to know the characters in the B layer that need to be converted, and the functions that call the D layer.

    Function querystudent (ByVal queryStudent1 as entity.querystudententity) as System.Data.DataTable        Dim Conn AS SqlConnection = New SqlConnection (sqlutil.connstring ())                       ' defines the connection to open the database, there is also a way to write        Dim SQL as String        sql = querystudent1.sqlstring        Dim cmd as SqlCommand = New SqlCommand (SQL, conn)                              ' Define database command        ' cmd. CommandText = SQL        ' stored procedure        ' cmd.  CommandType = CommandType.Text        Dim myadapter As SqlDataAdapter = New SqlDataAdapter (cmd)        Dim table As DataTable = New DataTable ()  ' to return a DataTable type of        Try            Conn. Open ()            myadapter.fill (table)   ' Fill is the view that is used to adapter the query to, put into the table inside the        Catch ex as Exception            MsgBox (ex . Message)        end Try        Return table    End functionend Class

Good. When we get here, the whole thing is finished.

Note:

(1), DataTable This conversion to entity type? A: There is no need to assign a value, really very troublesome. Directly this will enable the//table = Querystudentbll.querystudent (querystudent) ' to receive the return value of the table type//.

(2), suppose I used in SQL two table query, is not to build two entities, two B-level AH? A: No, Method 1: Set up an entity to put the fields used in the two tables inside. Method 2: Create a view that is directly adjustable.

vb.net+ three-storey computer room charge system combination Query