Combined query with stored procedure

A combination of queries from its surface meaning we know that by a condition or multiple criteria to query, we first look at the interface of the combination query:

Code:

U-Layer:

Private Sub btnCheck_Click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Btncheck.click Dim Entitywork as new crcmentity.combinationqueryentity Dim bllwork as New CRCMBLL.  Operatorworkrecordbll ' In the case of only one condition. Determine if the condition is entered complete if Cmbrelation1.text = "then Dim arrayctl () as 
            Control ReDim Preserve arrayctl (2) arrayctl (0) = CmbFiles1 arrayctl (1) = CmbOperator1 Arrayctl (2) = TxtCondition1 Isfrmcontrolempty.issomeempty (arrayctl) End If ' in two Conditions are combined to determine whether the condition is entered full if Cmbrelation1.text <> "then Dim arrayctl () as Control Redi M Preserve Arrayctl (6) arrayctl (0) = CmbFiles1 arrayctl (1) = CmbOperator1 Arrayctl (2) = TxtCondition1 Arrayctl (3) = CmbRelation1 Arrayctl (4) = CmbFiles2 Arrayctl (5) = Cmbop
           Erator2 Arrayctl (6) = TxtCondition2 Isfrmcontrolempty.issomeempty (ARRAYCTL) End if ' to determine whether the condition is entered as complete if cmbrelation2.text with three conditions Lt;> "then Isfrmcontrolempty.isallempty (Me) End If ' first row field name entitywork.filed1 = C Mbfiles1.text ' first line operator Entitywork.operater1 = Cmboperator1.text ' first row condition entitywork.condition
        1 = Txtcondition1.text ' first row combination relationship Entitywork.combinationrelation1 = Cmbrelation1.text ' second row field name
        Entitywork.filed2 = Cmbfiles2.text ' second line operator entitywork.operater2 = Cmboperator2.text ' second row condition 
        Entitywork.condition2 = Txtcondition2.text ' second row combination relationship Entitywork.combinationrelation2 = Cmbrelation2.text ' Third row field name Entitywork.filed3 = Cmbfiles3.text ' third line operator Entitywork.operater3 = Cmboperator3.te XT ' third row condition Entitywork.condition3 = Txtcondition3.text Dgvworkrecord.datasource = Bllwork.workrecor D (entitywork) End Sub Private Sub Btnexit_click (ByVal sender as System.Object, ByVal e as System.EventArgs) Handles Btnexit.click Application.exit () End Sub End Class

Call a function in the U layer to determine whether the control is empty:

' ************************************************* ' Author: Tang Huan ' group:  ' Description: Determine if the space on the form interface is empty ' date Created: 2013/2/19 ' version number: V1.00 ' * ' Public Class isfrmcontrolempty ' <summary> ' determines whether all controls in the form are full Department is empty ' </summary> public Shared Function isallempty (ByVal Frm as Form) as Boolean Dim control as Ne W control for every Ctl as control in frm.controls ' variable form all controls If Ctl.gettype () is GetType (TextBox) the N ' Determines whether the control type is textbox if Ctl.Text.Length = 0 Then ' Determines whether the textbox is empty, returns True, otherwise returns false Messa
                    Gebox.show (String.Format ("Information for complete, please fill out the information complete and save!")) Ctl.focus () Return True Exit Function End If ElseIf Ct L.gettype () is GetType (ComboBox) and then ' if space is a combobox, if Ctl.Text.Length = 0 Then ' If the ComboBox is empty, return true otherwise
  Returns false MsgBox (Ctl.Tag.ToString + "cannot be empty!", vbOK, "warm Tips")                  Ctl.focus () Return True Exit Function End If End If Next Return False end Function ' <summary> ' to determine if some of the controls in the form are empty ' & lt;/summary> public Shared Function issomeempty (ByVal arrayctl () as Control) as Boolean Dim Control as New Control for every CTL as control in Arrayctl ' iterates over all elements in the array If ctl. GetType () is GetType (textbox) and then ' if the control type is a TextBox if CTL. text.length = 0 Then ' If the input box is empty, returns True, otherwise false MsgBox (CTL) is returned. Tag.tostring + "cannot be empty!", vbOK, "warm little hint") ctl. Focus () ' input box gets focus Return True Exit Function End If Els EIf ctl. GetType () is GetType (ComboBox) and then ' determines whether the control type is a ComboBox if CTL. text.length = 0 Then ' If the ComboBox is empty, returns True, otherwise false MsgBox (CTL) is returned.
     Tag.tostring + "cannot be empty!", vbOK, "warm Tips")               Ctl.

        Focus () Return True Exit Function End If End Next Return False End Function

B Layer:

 ' <summary> ' query work record ' </summary> public Function Workrecord (ByVal workhistory as Comb Inationqueryentity) as DataTable Dim entitywork As New crcmentity.combinationqueryentity Dim dalwork As Ne W Crcmdal. Operatorworkrecorddal ' first row field name entitywork.filed1 = workhistory.filed1 ' first line operator Entitywork.op
        Erater1 = Workhistory.operater1 ' first row condition Entitywork.condition1 = Workhistory.condition1 ' first row combination relationship Entitywork.combinationrelation1 = Workhistory.combinationrelation1 ' second row field name Entitywork.filed2 = WorkHi Story.  Filed2 ' second line operator entitywork.operater2 = Workhistory.operater2 ' second row condition Entitywork.condition2 =
        Workhistory.condition2 ' second row combination relationship Entitywork.combinationrelation2 = Workhistory.combinationrelation2 ' Third row field name Entitywork.filed3 = Workhistory.filed3 ' third line operator Entitywork.operater3 = Workhistory.operat
    Er3    ' third row condition Entitywork.condition3 = workhistory.condition3 Dim table as DataTable ' defines a dataset table = Dalwork.selectoperatorworkrecord (entitywork) Return table End Function

Layer D:

"<summary>" Search by condition lookup operator work record ' </summary> public Function selectoperatorworkrecord (ByVal Wo
        Rkrecord as Combinationqueryentity) as DataTable Dim sqlcmdstr as String = "Proc_cr_select_opertorworkrecord"  Dim paras as SqlParameter () = {new SqlParameter ("@Field1", workrecord.filed1), new SqlParameter ("@Operation1", Workrecord.operater1), New SqlParameter ("@Condition1", Wo Rkrecord.condition1), New SqlParameter ("@Combinationrelations1", Workrecord.combinatio
                                    NRelation1), New SqlParameter ("@Field2", Workrecord.filed2), New SqlParameter ("@Operation2", Workrecord.operater2), New SqlParameter ("@c Ondition2 ", Workrecord.condition2), New SqlParameter (" @Combinationrelations2 ", Workrec Ord. CombinationRelation2), New SqlParameter ("@Field3", Workrecord.filed3), New SqlParameter ("@Operation3", Workrecord.operater3), New SqlParameter ("@Condition3" , Workrecord.condition3)} Dim table as DataTable ' defines a dataset table = Sqlh Elp. Sqlhelper.selectinfo (Sqlcmdstr, CommandType.StoredProcedure, paras) Return table End Function

Stored procedures:

--=============================================--Author: Tang Huan--Create date:2013 February 15--Description: query operator work record-= = =========================================== ALTER PROCEDURE [dbo]. [Proc_cr_select_opertorworkrecord]--First row @Field1 varchar (40),--Field name @Operation1 varchar (TEN),--operator @Condition1 Char (50)  ,--conditions @Combinationrelations1 char (10),----the second row @Field2 varchar (40),--the field name @Operation2 varchar (TEN),--operator @Condition2 Char,--condition @Combinationrelations2 char (10),---composite relationship--third row @Field3 varchar (40),--field name @Operation3 varchar,--operator @c Ondition3 Char (50)--conditional as DECLARE @Sql varchar (DECLARE) @Sqlstr1 varchar (20)--the field name in the first row corresponds to the Declare @Sqlstr2 var of the field name in the table CHAR (20)--field name in the second row in the table field name declare @Sqlstr3 varchar (20)--the field name in the third row corresponds to the field name in the table declare @SqlRelations1 varchar (20)--Indicates the first row The combined relationship of declare @SqlRelations2 varchar (20)--representing the combined relationship in the second row BEGIN--Indicates that the field name in the first row represents the IF (@Field1 = ' teacher ') set @Sqlstr1 = ' Us Ername ' if (@Field1 = ' Login Date ') Set @Sqlstr1 = ' usedate ' if (@Field1 = ' login time ') set @SqlstR1 = ' Usetime ' if (@Field1 = ' logoff date ') Set @Sqlstr1 = ' nousedate ' if (@Field1 = ' logoff time ') set @Sqlstr1 = ' Nousetime ' if (@Field1 = ' Machine name ') Set @Sqlstr1 = ' Roomno '--Indicates that the field name in the second row is represented by the field name in the table for IF (@Field2 = ' teacher ') set @Sqlstr2 = ' UserName ' if (@Field2 = ' login date ') se T @Sqlstr2 = ' usedate ' if (@Field2 = ' login time ') set @Sqlstr2 = ' Usetime ' if (@Field2 = ' logoff date ') Set @Sqlstr2 = ' nousedate ' if (@Fie Ld2 = ' logoff time ') set @Sqlstr2 = ' Nousetime ' if (@Field2 = ' machine name ') Set @Sqlstr2 = ' Roomno '--Indicates that the field name in the third row is represented by the field name in the table if (@Field3 = ' teacher ') Set @Sqlstr3 = ' UserName ' if (@Field3 = ' Login Date ') Set @Sqlstr3 = ' usedate ' if (@Field3 = ' login time ') set @Sqlstr3 = ' Usetime ' if (@Field3 = ' logoff date ') Set @Sqlstr3 = ' nousedate ' if (@Field3 = ' logoff time ') set @Sqlstr3 = ' Nousetime ' if (@Field3 = ' machine name ') set @Sqlstr 3 = ' Roomno '--the first combined relationship if (@Combinationrelations1 = ') set @SqlRelations1 =null if (@Combinationrelations1 = ' and ') set @SqlRe Lations1= ' and ' if (@Combinationrelations1 = ' or ') set @SqlRelations1 = ' or '--the second combined relationship if (@Combinationrelations2 = ") Set @Sq Lrelations2=null if (@ComBinationrelations2 = ' and ') set @SqlRelations2 = ' and ' if (@Combinationrelations2 = ' or ') set @SqlRelations2 = ' or ' Set @Sql = '   Select UserName as user name, usedate as login date, usetime as login time, nousedate as exit date, nousetime as exit time, Roomno as machine name from Workrecord where ' + @Sqlstr1 + + @Operation1 +char () + @Condition1 +char (@SqlRelations1 is not null) set @Sql = @Sql + @SqlRelat Ions1 +char (+) + @Sqlstr2 + @Operation2 +char (All) + @Condition2 +char (All) if (@SqlRelations2 is not null) set @Sql = @Sql + @Sql Relations2 +char (+) + @Sqlstr3 + @Operation3 +char (All) + @Condition3 +char (All) execute (@Sql) END

SQLHELLP:

' <summary> '
    query with Parameters ' '
    </summary> public
    Shared Function Selectinfo (ByVal CommandText as String, ByVal CommandType as CommandType, ByVal Commandparameters as SqlParameter ()) as DataTable
        Dim SQLCN as New Sql Connection ' defines the database connection object
        SQLCN = GetConnect ()
        SQLCN. Open () ' Opening database

        Dim sqlcmd As SqlCommand  ' definition Command object
        sqlcmd = New SqlCommand (commandtext, SQLCN)

        Dim Infoadaptor as SqlDataAdapter ' definition adapter object
        Dim infodataset as new DataSet  ' defines a dataset

        Infoadaptor = new SqlDataAdapter (sqlcmd)
        Sqlcmd.commandtype = CommandType

        sqlcmd. Parameters.addrange (commandparameters) ' Add parameter
        infoadaptor.fill (infodataset) ' Fill adapter
        Return Infodataset.tables (0)  ' Returns the table of the dataset, starting with index SQLCN from the first one
        . Close ()
    End Function

Summarize:

1. The process of writing and using the stored procedures, at the beginning of learning data, just know that there are stored procedures and transactions, but never their own real hands to

Write, just start the time, feel very difficult, difficult mainly is difficult in do not know where to start, just began to write simple stored procedures can also, when writing a combination

Querying this piece of stored procedures is full of errors, and do not know where the error occurred, on the internet to find a lot about the contents of the stored procedures,

Tinkering for a few days, finally made out, that moment feels really is particularly good.

2.Sqlhelp writing and use, sqlhelp know it is in the learning of UML, but it is very curious, but not how to study, this time

Write the code of the computer room charge system, use the data connection in the D layer, repeatedly write the same statement, feel special trouble, began to try to write

Sqlhelp, found on the Internet to write information, I looked at is also a daze, it seems to see, it is not as good as I write it, finished, feeling but is

So, Sqlhelp and VB version of the module almost, I am not unfamiliar with it.

3. For the three-tier application, the computer room charge system is made with three layers, in fact, with a few layers to do and important, it is important to understand the layered thinking, like some

The judgment does not have to be placed on the B-layer, or on the U-layer. In the computer room book fee system, the three layer used more smoothly.

4. Sometimes it is difficult to feel very difficult, are themselves in the scare themselves, encountered again difficult problems, in front of us there is a giant-Baidu, to believe in themselves.