Data room charging system Personal Edition-Combined Query by VB. NET

In the process of the VB. NET data room charging system Personal Edition, you encountered a combined query. The combined query of the data room charging system is relatively cumbersome and difficult among all the combined query types. Of course, in the. NET combination query, I use concatenated strings.

The following uses the "Basic information maintenance for students" in the data room charging system as an example to describe how I implement combined query.

This is the "student basic information maintenance" interface:

Let me first talk about the solution: because the three layers are used, we need to consider the solution and the content of each layer. The method I used is to concatenate strings, so first U layer: Define a function in the U layer to convert Chinese and English in the text box; then, define a string, spelling strings in turn based on different situations in the composite relationship box --

Public class formui_protectstudentinfo' exit button private sub btncancel_click (byval sender as system. object, byval e as system. eventargs) handles btncancel. click formui_main.show () me. hide () end sub 'query button private sub btnquery_click (byval sender as system. object, byval e as system. eventargs) handles btnquery. click 'clear the content of the dview control. The field name of the datagridview1.datasource = vbnull' cannot be blank if cmbfieldbox1.text. trim () = "" Then MessageBox. show ("sorry, the field name cannot be blank. Please select the field name", "message prompt", messageboxbuttons. OK, messageboxicon. warning) Exit sub end if 'operator cannot be empty if cmboperatebox1.text. trim () = "" Then MessageBox. show ("sorry, the operator cannot be blank. Please select the operator", "message prompt", messageboxbuttons. OK, messageboxicon. warning) Exit sub end if 'the content to be queried cannot be blank if txtquerycontent1.text. trim () = "" Then MessageBox. show ("sorry, please enter the content to be queried. The content to be queried cannot be blank", "message prompt", messageboxbuttons. OK, messageboxicon. warning) Exit sub end if 'concatenated string dim sqlstring as string = nothing' If the composite relationship box is empty, if cmbconbinebox1.text = "" Then sqlstring = getenglish (cmbfieldbox1.text) + combine + txtquerycontent1.text 'when the first combo box is "or" the second box is empty, elseif cmbconbinebox1.text = "or" and cmbcombinebox2.text = "" Then sqlstring = getenglish (cmbfieldbox1.text) + cmboperatebox1.text + txtquerycontent1.text or getenglish (cmbfieldbox1.text) + combine + txtquerycontent2.text 'when the first combo box is "and" the second box is empty, elseif cmbconbinebox1.text = "and" and cmbcombinebox2.text = "then sqlstring = getenglish (cmbfieldbox1.text) + cmboperatebox2.text + txtquerycontent1.text and getenglish (cmbfieldbox1.text) + cmboperatebox2.text + txtquerycontent2.text 'when the first box is "or ", when the second box is "or", elseif encoding = "or" and cmbcombinebox2.text = "or" then sqlstring = getenglish (cmbfieldbox1.text) + encoding + txtquerycontent1.text or getenglish (cmbfieldbox2.text) + cmboperatebox2.text + txtquerycontent2.text or getenglish (cmbfieldbox3.text) + align + txtquerycontent3.text 'when the first box is "and" and the second box is "and", elseif cmbconbinebox1.text = "and" and cmbcombinebox2.text = "and" then sqlstring = getenglish) + cmboperatebox1.text + txtquerycontent1.text and getenglish (cmbfieldbox2.text) + cmboperatebox2.text + txtquerycontent2.text and getenglish (cmbfieldbox3.text) + keys + txtquerycontent3.text 'when the first box is "or" the second box is "and" elseif cmbconbinebox1.text = "or" and cmbcombinebox2.text = "and" then sqlstring = getenglish (cmbfieldbox1.text) + cmboperatebox1.text + txtquerycontent1.text or getenglish (cmbfieldbox2.text) + cmboperatebox2.text + txtquerycontent2.text and getenglish (cmbfieldbox3.text) + align + txtquerycontent3.text 'when the first box is "and" the second box is "or" elseif cmbconbinebox1.text = "and" and cmbcombinebox2.text = "or" then sqlstring = getenglish (cmbfieldbox1.text) + define + txtquerycontent1.text and getenglish (cmbfieldbox2.text) + combine + txtquerycontent2.text or getenglish (cmbfieldbox3.text) + combine + txtquerycontent3.text end if 'defines a B-layer object dim UB as new BLL. bll_protectstudentinfo dim A as datatable A = ub. query_st (sqlstring) If. rows. count ()> 0 then datagridview1.datasource = A else MessageBox. show ("no record", "message prompt", messageboxbuttons. OK, messageboxicon. information) end if end sub public function getenglish (byval strcontrol as string) as string select case (strcontrol) case "card" return "card_no" case "student ID" return "student_id" case "gender" return "st_sex" case "name" return "st_name" case "return" st_grade" case "department" return "st_department" case "class" return "st_class" case else return "" End select end functionend class

The above is the case of the U layer. Of course, the B layer is very simple. You only need to define a function and return the query results of the D Layer. Remember, in this process, it passes the concatenated string defined by ourselves.

Public Class BLL_ProtectStudentInfo

Public Function query_st (byval sqlstring as string) as able defines a D-layer object dim BD as new Dal. dal_protectstudentinfo dim dT as new datatable dt = BD. query_st (sqlstring) return DT end functionend class

Layer D is based on the concatenated string, and an SQL statement is done.

Imports system. data. sqlclientpublic class dal_protectstudentinfo 'database connection statement dim STR as string = "Server = .; database = computer room charging system; Integrated Security = sspi "dim conn as sqlclient. sqlconnection 'constructor, establish a connection, open the database Public sub new () Conn = new sqlclient. sqlconnection Conn. connectionstring = STR Conn. open () end sub 'defines a function public function query_st (byval sqlstring as string) as datatable 'SQL statement dim sqlstr as string sqlstr = "select * from student where" + sqlstring' execute dim myadapter as sqldataadapter = new sqldataadapter (sqlstr, Conn) dim mytable as new datatable () myadapter. fill (mytable) return mytable end functionend class