Sqlconnection, sqldataadapter, sqlcommand, sqlparameter

Sqlconnection, sqldataadapter, sqlcommand, and sqlparameter are often used in the charging system of the VB.net data center. These classes are both sqlclient classes and sqlclient classes are located in system. in the data namespace, this namespace is in ADO. net can also be considered as the core part. Next I will introduce these common classes separately.

 

1. sqlconnection class: provides a connection to the SQL Server database.

When constructing a sqlconnection object, you can specify a connection string as a parameter. The connection string contains all the information required to open the database connection. The following describes how to use the sqlconnection class to initialize a connection object that can connect to the SQL Server database. This object uses the following connection strings: computer name: chenjinrong, Database Name: mychargetwo, the username and password of SQL Server.

DimsqlConnectStr As SqlConnection = New SqlConnection("server=chenjinrong;database=MychargeTwo;uid=sa;pwd=123456")

Once a connection object is initialized during normal usage, you can call the sqlconnection object method, for example:

Sqlconnectstr. open () 'Open the database

Sqlconnectstr. Close () 'Close the database

 

2. sqlcommand class: Execute an SQL command for data storage

This command usually refers to adding, deleting, modifying, and querying commands. The executed query can contain parameters or not. Although there are many constructors in the sqlcommand class, however, the simplest method is not including parameters. The following describes how to initialize a sqlcommand object.

 

      Dim cmdd As SqlCommand =NewSqlCommand ()

 

After initialization, you can set the required attributes and methods to execute the task. The following describes the attributes that must be set for sqlcommand before executing the query.

 

1. Connection attributes

This property is set as a sqlconnection object. To make the Command run successfully, you must open the connection when executing the command.

 

Cmdd. Connection = sqlconnection1

 

2. commandtext attributes

This attribute specifies the SQL statement or stored procedure to be executed

For example:

Dim sqlconnectstr as sqlconnection = new sqlconnection ("Server = chenjinrong; database = mychargetwo; uid = sa; Pwd = 123456") dim cmdd assqlcommand = new sqlcommand () cmdd. connection = sqlconnection1dim sql2 as string = "insert into stubasicinfo (cardno, stuno, stuname, stusex, system, status, grade, classs, balance, explain, username, regday, regtime, ischeckout) values (@ txtcardno, @ txtstuno, @ txtstuname, @ cmbstusex, @ txtsystem, @ cmbstatus, @ txtgrade, @ txtclass, @ txtbalanced, @ txtexplain, @ username, @ day, @ time, @ ischeckout) "'@ variable indicates a placeholder in an SQL statement. You need to fill in cmdd with parameters. commandtext = sql2

3. Parameters attributes

This attribute is used to access the parameter set of the sqlcommand object. Once this set is accessed, you can use its attributes and methods to create one or more parameters in the set.

 

When we introduce the commandtext attribute, for example, there are many variables prefixed with @, which are placeholders in SQL statements and parameters need to be created, these parameters insert values to placeholders when executing SQL statements.

 

For example:

Dim sqlconnectstr as sqlconnection = new sqlconnection ("Server = chenjinrong; database = mychargetwo; uid = sa; Pwd = 123456") dim cmdd assqlcommand = new sqlcommand () cmdd. connection = sqlconnection1dim sql2 as string = "insert into stubasicinfo (cardno, stuno, stuname, stusex, system, status, grade, classs, balance, explain, username, regday, regtime, ischeckout) values (@ txtcardno, @ txtstuno, @ txtstuname, @ cmbstusex, @ txtsystem, @ cmbstatus, @ txtgrade, @ txtclass, @ txtbalanced, @ txtexplain, @ username, @ day, @ time, @ ischeckout) "'@ variable indicates a placeholder in an SQL statement. You need to fill in cmdd with parameters. commandtext = sql2

'Below is the placeholder filled with the above. Create parameters. When executing the SQL statement, these parameters are inserted with the placeholder value cmdd. parameters. add (newsqlparameter ("@ txtcardno", stuinfor. cardno) cmdd. parameters. add (newsqlparameter ("@ txtstuno", stuinfor. stuno) cmdd. parameters. add (newsqlparameter ("@ txtstuname", stuinfor. stuname) cmdd. parameters. add (newsqlparameter ("@ cmbstusex", stuinfor. stusex) cmdd. parameters. add (newsqlparameter ("@ txtsystem", stuinfor. system) cmdd. parameters. add (newsqlparameter ("@ cmbstatus", stuinfor. status) cmdd. parameters. add (newsqlparameter ("@ txtgrade", stuinfor. grade) cmdd. parameters. add (newsqlparameter ("@ txtclass", stuinfor. classs) cmdd. parameters. add (newsqlparameter ("@ txtbalanced", stuinfor. balance) cmdd. parameters. add (newsqlparameter ("@ txtexplain", stuinfor.) cmdd. parameters. add (newsqlparameter ("@ username", stuinfor. username) cmdd. parameters. add (newsqlparameter ("@ Day", stuinfor. regday) cmdd. parameters. add (newsqlparameter ("@ time", stuinfor. regtime) cmdd. parameters. add (newsqlparameter ("@ ischeckout", stuinfor. ischeckout ))

4. executenonquery Method

This method is used to execute SQL statements.

The complete code for inserting data into the stubasicinfo table is as follows:

Dim sqlconnectstr as sqlconnection = new sqlconnection ("Server = chenjinrong; database = mychargetwo; uid = sa; Pwd = 123456") dim cmdd as sqlcommand = new sqlcommand () cmdd. connection = sqlconnection1dim sql2 as string = "insert into stubasicinfo (cardno, stuno, stuname, stusex, system, status, grade, classs, balance, explain, username, regday, regtime, ischeckout) values (@ txtcardno, @ txtstuno, @ txtstuname, @ cmbstusex, @ txtsystem, @ cmbstatus, @ txtgrade, @ txtclass, @ txtbalanced, @ txtexplain, @ username, @ day, @ time, @ ischeckout) "'@ variable indicates a placeholder in an SQL statement. You need to fill in cmdd with parameters. commandtext = sql2' is filled with the placeholder above. parameters are created. When SQL statements are executed, these parameters are inserted with the placeholder value cmdd. parameters. add (New sqlparameter ("@ txtcardno", stuinfor. cardno) cmdd. parameters. add (New sqlparameter ("@ txtstuno", stuinfor. stuno) cmdd. parameters. add (New sqlparameter ("@ txtstuname", stuinfor. stuname) cmdd. parameters. add (New sqlparameter ("@ cmbstusex", stuinfor. stusex) cmdd. parameters. add (New sqlparameter ("@ txtsystem", stuinfor. system) cmdd. parameters. add (New sqlparameter ("@ cmbstatus", stuinfor. status) cmdd. parameters. add (New sqlparameter ("@ txtgrade", stuinfor. grade) cmdd. parameters. add (New sqlparameter ("@ txtclass", stuinfor. classs) cmdd. parameters. add (New sqlparameter ("@ txtbalanced", stuinfor. balance) cmdd. parameters. add (New sqlparameter ("@ txtexplain", stuinfor.) cmdd. parameters. add (New sqlparameter ("@ username", stuinfor. username) cmdd. parameters. add (New sqlparameter ("@ Day", stuinfor. regday) cmdd. parameters. add (New sqlparameter ("@ time", stuinfor. regtime) cmdd. parameters. add (New sqlparameter ("@ ischeckout", stuinfor. ischeckout) sqlconnection1.open () 'Open the connection cmdd. executenonquery () 'executes the SQL statement and inserts data into the database sqlconnection1.close ()

3. sqldataadapter class

Like the sqlcommand class, the sqldataadapter class also has its corresponding attributes and methods.

 

1. selectcommand attributes

This attribute is used to fill data in the SQL Server database into dataset. to read data from the data storage, you must first set the selectcommand attribute of the sqldataadapter class. This attribute is a selectcommand object that specifies the data to be selected and how to select the data. Therefore, selectcommand also has its own attributes.

• Connection: sets the sqlconnection object used to access data storage.
• Commandtext: Specifies the SQL statement or stored procedure name used to select data.

The following code sets these attributes:

Dim data as new sqldataadapter () 'defines a sqldataadapter variable data. selectcommand = new sqlcommand () data. selectcommand. connection = sqlconnection1 'sets the connection attribute to a valid connection object data. selectcommand. commandtext = "select cardno, status from stubasicinfo order by stuinfo. cardno, stuinfo. status"

 

2. Fill Method

This method can be used to fill in the DataSet object with the data retrieved from the data storage by the sqldataadapter object using its selectcommand

 

Dim data as new sqldataadapter () 'defines a sqldataadapter variable dim DAS as dataset = new dataset () data. selectcommand = new sqlcommand () data. selectcommand. connection = sqlconnection1 'sets the connection attribute to a valid connection object data. selectcommand. commandtext = "select cardno, status from stubasicinfo order by stuinfo. cardno, stuinfo. status "data. fill (Das, "stubasicinfo ")

The above is my induction of this part of my knowledge, and I hope to help my friends who are learning this part of knowledge!