Common SQL statement parameterization + display query results

Set of common SQL statements parameterization:

In different SQL statements, parameterization methods are different, but usually Placeholders are used. Then, parameters are added as follows using command objects. The list of commonly used parameterization methods is as follows:

1. parameterization of select statements: I am afraid that query statements are the most widely used database applications. Their parameterization methods are common.

Strsql = "select * From Table1 where name = @ name"
Cmd = new sqlcommand (strsql, Conn)
Cmd. Parameters. addwithvalue ("rechargeteacher", sturechargerecord. reacherteacher)

Conn is a sqlconnection object used to connect to the database. If multiple parameters are the same.

 

2. parameterization of the inset statement: the insert statement is used to add a new record to the database. Its parameterization method is as follows:

Strsql = "insert into stucardenroll (stucardno) values (@ stucardno )"
Cmd = new sqlcommand (strsql, Conn)
Cmd. Parameters. Add ("@ stucardno", sqldbtype. nvarchar, 60). value = stuenrollrecord. stucardno

 

3. parameterization of the update statement to update the value of a field in the database:

Strsql = "Update userlogonrecord set logofftime = @ logofftime <a href =" mailto: stucardno = @ "">"

Cmd = new sqlcommand (strsql, Conn)
Cmd. Parameters. addwithvalue ("logoffdate", strtodaydate)

(The parameter abc should be the value of an attribute of the object class. Because the object class is not listed here, it should be replaced by the string ABC, in addition, the SQL statement is used to update the value of the logofftime field of all records in the table to the given value, if you only want to update the field value of a record, you must provide the where condition after the statement ).

 

There are two methods to display parameterized query data:

I believe that anyone with database experience should know that we generally use the sqlcommand object and sqldatareader to query and determine whether there are query records, to display the query results on the interface, the sqldataadapter and datatable objects are usually used, (I generally use the sqlcommand object for parameterized query. It is not clear how to use the dataadapter object for parameterized query. I am very grateful if I can tell you ), however, if I want to use parameterized queries and display the query results on the form, the following are two small and medium methods:

1. Use the load () method of datatable:

In fact, we can use the sqlcommand object + sqldatareader object + datatable object for data display. we use the sqlcommand object for parameterized query, then assign the query result to the sqldatareader object, and finally put the records in sqlreader into the datatable object using the load () method of the able object, return a datatable object.

2. Use the selectcommand method of datatable

This method is simpler than the first one. It directly uses the able object + sqlcommand object to complete the query result into the datatable.

Cmd = new sqlcommand (SQL statement, database connection Statement)

Datatable. selectcommand = cmd, and then return the datatable object.

Using this method, you can also display parameterized query results.