Ado. Net basic database programming

. NET data provider object:

1. connection object
2. Command object
3. datareader object
4. dataadapter object

A general data object can store a local copy of the information in the database, so that the information can be processed when the database connection is disconnected. You can read rows in any order, search, sort, and filter rows in a flexible manner, or even change these rows, and then synchronize the changes to the database, it mainly includes the following objects.

1. DataSet object
2. datatable object
3. datarow object
4. datacolumn object

 

Use the system. Data namespace:

The first step in the development of the ADO. Net Program is to reference the system. Data namespace, which contains all the ADO. Net classes.

Using system. Data;

1. SQL Server. NET data provider
Using system. Data. sqlclient;
2. ole db. NET data provider
Using system. Data. oledb;
3. Oracle Data Provider
Using system. Data. oracleclient;
4. ODBC. NET data provider
Using system. Data. ODBC;

General steps for connecting to and using a database:

1. Create a database, such as access and SQL Server 2000.
2. Use the connection object to connect to the database
3. Use the command object to execute SQL commands on the data source and return data
4. Use datareader and dataset objects to read and process data from data sources
5. Disconnect the database

Example 1: The t_klconfig information is displayed by connecting to the database.

Private void simplebutton#click (Object sender, eventargs e) {// string to connect to the SQL Server database: String connectionstring = @ "Server = localhost; database = an_advert; uid = sa; pwd = 123 "; dataset myst; // defines the DataSet object sqldataadapter myda; // defines the data adapter // creates a sqlconnection object, connect to the SQL server's an_advert database sqlconnection mysqlconnection = new sqlconnection (connectionstring); // create the sqlcommand object sqlcommand mysqlcommand = mysqlconnection. createcommand (); string commandstring = @ "select * From t_klconfig"; mysqlcommand. commandtext = commandstring; // use the open () method of the connection object to open mysqlconnection. open (); myda = new sqldataadapter (mysqlcommand); // create a data adapter object myst = new dataset (); // create a DataSet object myda. fill (myst, "t_klconfig"); // fill in the dataset sqlcommandbuilder buildet = new sqlcommandbuilder (myda); gridcontrol1.datasource = Myst. tables ["t_klconfig"]; // sets the data source // closes the database connection mysqlconnection. close ();}

Connection string: String strcon = @ "Data Source = localhost (server); initial catalog = shopping (database); Integrated Security = true"; // database connection string

Ado. Net basic database programming:

Common Database Programming includes connecting to the database, inserting new data, deleting data, and modifying data, that is, executing the insert, delete, and update statements in SQL syntax.

Use the executenonquery () method of the command object to insert data, and use the SQL insert statement to set the specific new record content to be inserted.

1. The following program inserts a new record in the student table of the northwind database.

Protected void add () {// string connecting to the SQL Server2000 Database string connectionstring = @ "Server = localhost \ HMX; database = mydata; uid = sa; Pwd = "; // create a sqlconnection object and connect to the mydata database sqlconnection mysqlconnection = new sqlconnection (connectionstring) of SQL Server2000; // create the sqlcommand object sqlcommand mysqlcommand = mysqlconnection. createcommand (); // create an SQL insert statement. In the student table, enter a new record string insertstring = "insert into student (studentno, studentname, sex) values ('123 ', 'wang 2', 'male') "; mysqlcommand. commandtext = insertstring; // use the open () method of the connection object to open mysqlconnection. open (); // use the sqlcommand object to insert a new record, mysqlcommand. executenonquery (); // close the database connection mysqlconnection. close ();}

2. modify a record in the student table of the northwind database.

Protected void modify () {// connection string connectionstring = @ "Server = localhost \ HMX; database = northwind; uid = sa; Pwd = "; // create a sqlconnection object and connect to sqlconnection mysqlconnection = new sqlconnection (connectionstring) of the northwind database that comes with SQL Server2000; // create the sqlcommand object sqlcommand mysqlcommand = mysqlconnection. createcommand (); // create an SQL update statement. In the student table, modify the specified record string modifystring = "Update student set sex = 'femal' where studentname = 'zhao yi '"; mysqlcommand. commandtext = modifystring; // use the open () method of the connection object to open mysqlconnection. open (); // use the sqlcommand object to modify the specified mysqlcommand record. executenonquery (); // close the database connection mysqlconnection. close ();}

3. delete a record in the student table of the northwind database.

Protected void deleteone () {// string connectionstring = @ "Server = localhost \ HMX; database = northwind; uid = sa; Pwd = "; // create a sqlconnection object and connect to sqlconnection mysqlconnection = new sqlconnection (connectionstring) of the northwind database that comes with SQL Server2000; // create the sqlcommand object sqlcommand mysqlcommand = mysqlconnection. createcommand (); // create the SQL Delete statement, and delete the specified record string deletestring = "delet E from student where studentname = 'zhao yi' "; mysqlcommand. commandtext = deletestring; // use the open () method of the connection object to open mysqlconnection. open (); // use the sqlcommand object to delete the IF (mysqlcommand. executenonquery ()> 0) {// console. write ("congratulations, the task of deleting the specified record from student is completed! "); // Return the number of affected records} else {// console. Write (" no matching records ");} // close the database connection mysqlconnection. Close ();}