Using ADO to access a database in a. NET Framework application

I. Common data Access Components

1. Access to the components used by the SQL Server database system

SqlConnection ..... Connecting to a SQL Server database system

SqlCommand ... submit an Execute SQL command to the SQL Server database system

SqlDataReader ..... Read the data returned by the Execute SQL command

SqlDataAdapter ..... Use this component to populate data from a database into a dataset dataset

Note: Using the above components requires the introduction of the System.Data.SqlClient namespace.

2. Access to the components used by the Access database

OleDbConnection ... Connecting the OLE DB database system

OleDbCommand .... Submit an Execute SQL command to the OLE DB database system

OleDbDataReader ... Read the data returned by the Execute SQL command

OleDbDataAdapter. Use this component to populate data in a database into a dataset dataset

Note: Using the above components requires the introduction of the System.Data.OleDb namespace.

Ii. ways to access the database

Third, how to connect the database

1. Connecting to an instance of SQL Server

[C # Version]

Using System.Data.SqlClient;

......

The meaning of the connection string is: Log on to the computer named "(local)" Database server, the login user name is a, the password is B,

and assume that a database named DBName has been created in SQL Server.

SqlConnection conn = new SqlConnection ("server= (local); Database=dbname; User id=a; password=b; ");

Conn.   Open (); Open a database connection

......

Conn.   Close (); To close a database connection

[VB Version]

Imports System.Data.SqlClient

......

The meaning of the connection string is: Log on to the computer named "(local)" Database server, the login user name is a, the password is B,

' and assume that a database named DBName has been created in SQL Server.

Dim Conn as New SqlConnection ("server= (local); Database=dbname; User id=a; password=b; ")

Conn. Open () ' Opens the database connection

......

Conn. Close () ' Shut Down database connection

2. Connect to an instance of access

[C # Version]

Using System.Data.OleDb;

......

The connection string means: The program accesses the Access database via microsoft.jet.oledb.4.0

And assume that you already created a database in Access 2000 that has a file name of C:\Db1.mdb.

OleDbConnection Conn

= new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0; Data source=c:\db1.mdb; ");

Conn.       Open (); Open a database connection

......

Conn.       Close (); To close a database connection

[VB Version]

Imports System.Data.OleDb

......

' The connection string means: The program accesses the Access database through Microsoft.Jet.OLEDB.4.0,

' and assume that a database has been created in Access 2000 with the file name C:\Db1.mdb.

Dim Conn as New OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\db1.mdb; ")

Conn. Open () ' Opens the database connection

......

Conn. Close () ' Shut Down database connection

Iv. How to access data through DataReader

Here's an example of how to read data from a SQL Server database through DataReader, and if you need access to an Access database, simply SqlConnection, SqlCommand, SqlDataReader changed to OleDbConnection, OleDbCommand, OleDbDataReader respectively. At the same time the database connection string from "server= (local); Database=dbname; User id=a; password=b; " Change to "provider=microsoft.jet.oledb.4.0; Data Source=c:\db1.mdb; " The form.

[C # Version]

Using System.Data.SqlClient;

......

The meaning of the connection string is: Log on to the computer named "(local)" Database server, the login user name is a, the password is B,

and assume that a database named DBName has been created in SQL Server.

SqlConnection conn = new SqlConnection ("server= (local); Database=dbname; User id=a; password=b; ");

Conn.       Open (); Open a database connection

SqlCommand cmd = new SqlCommand (); To create an object cmd that executes a SQL command

Cmd.        Connection = conn; Specifies that CMD will transfer the SQL command over the conn connection

Cmd.commandtext = "SELECT * FROM MyTable"; Defines the SQL command that CMD will execute

SqlDataReader Sqldr; Define DataReader data Reader (object variable Sqldr)

Sqldr = cmd.     ExecuteReader (); Executes the SQL command and executes the returned result to form the DataReader object.

while (Sqldr.read ())//read a row of data

{

Data output with the field name "Column1" in the row data

LISTBOX1.ITEMS.ADD (sqldr["Column1"). ToString ());

}

Sqldr.close ();

Conn.   Close (); To close a database connection

[VB Version]

Imports System.Data.SqlClient

......

The meaning of the connection string is: Log on to the computer named "(local)" Database server, the login user name is a, the password is B,

' and assume that a database named DBName has been created in SQL Server.

Dim Conn as New SqlConnection ("server= (local); Database=dbname; User id=a; password=b; ")

Conn. Open () ' Opens the database connection

Dim cmd as New SqlCommand () ' Creates an object that executes the SQL command cmd

Cmd. Connection = Conn ' Specifies that CMD will transfer the SQL command over the conn connection

Cmd.commandtext = "SELECT * FROM MyTable" ' defines the SQL command that CMD will execute

Dim Sqldr as SqlDataReader ' definition DataReader data reader (object variable Sqldr)

Sqldr = cmd. ExecuteReader () ' Executes the SQL command and executes the returned result to form the DataReader object.

while (Sqldr.read ()) ' reading a row of data

' Data output with the field name ' Column1 ' in the row data

LISTBOX1.ITEMS.ADD (Sqldr ("Column1"))

End while

Sqldr.close ()

Conn. Close () ' Shut Down database connection

V. How to access data through a dataset

Here's an example of how to read the data in a SQL Server database through DataAdapter and copy it into a dataset, and if you need access to an Access database, simply sqlconnection the program source code, SqlCommand and SqlDataAdapter were changed to OleDbConnection, OleDbCommand and OleDbDataAdapter respectively. At the same time the database connection string from "server= (local); Database=dbname; User id=a; password=b; " Change to "provider=microsoft.jet.oledb.4.0; Data Source=c:\db1.mdb; " The form.

[C # Version]

Using System.Data;

Using System.Data.SqlClient;

......

The meaning of the connection string is: Log on to the computer named "(local)" Database server, the login user name is a, the password is B,

and assume that a database named DBName has been created in SQL Server.

SqlConnection conn = new SqlConnection ("server= (local); Database=dbname; User id=a; password=b; ");

Conn.       Open (); Open a database connection

SqlCommand cmd = new SqlCommand (); To create an object cmd that executes a SQL command

Cmd.        Connection = conn; Specifies that CMD will transfer the SQL command over the conn connection

Cmd.commandtext = "SELECT * FROM MyTable"; Defines the SQL command that CMD will execute

SqlDataAdapter SqlDA = new SqlDataAdapter (cmd);//define the data adapter that will execute the cmd command

DataSet ds = new DataSet (); To create a DataSet DataSet object

Sqlda.fill (ds, "Table1");//Execute SQL command via data adapter, execute resulting data table populated to DataSet DS

Conn.   Close (); To close a database connection

for (int i=0; i<ds. tables["Table1"]. Rows.Count; i++)//Feel free to read any line of data

{

Data output with the field name "Column1" in the row data

ListBox1.Items.Add (ds. tables["Table1"]. rows[i]["Column1"]. ToString ());

}

DataGrid1.DataSource = ds. tables["Table1"]; You can also bind the entire data table in DataSet DS to a data display control

In the case of an ASP. NET program, you also need the following line of statements to bind the DataSet DS to the data display space

Datagrid1.databind ();

[VB Version]

Imports System.Data

Imports System.Data.SqlClient

......

The meaning of the connection string is: Log on to the computer named "(local)" Database server, the login user name is a, the password is B,

' and assume that a database named DBName has been created in SQL Server.

Dim Conn as New SqlConnection ("server= (local); Database=dbname; User id=a; password=b; ")

Conn. Open () ' Opens the database connection

Dim cmd as New SqlCommand () ' Creates an object that executes the SQL command cmd

Cmd. Connection = Conn ' Specifies that CMD will transfer the SQL command over the conn connection

Cmd.commandtext = "SELECT * FROM MyTable" ' defines the SQL command that CMD will execute

Dim SqlDA as New SqlDataAdapter (CMD) ' defines the data adapter that will execute the cmd command

Dim DS as New DataSet () ' Create DataSet DataSet object

Sqlda.fill (ds, "Table1") ' Execute SQL command via data adapter, execute resulting data table populated to DataSet DS

Conn. Close () ' Shut Down database connection

Dim I as Integer

For I=0 to DS. Tables ("Table1"). Rows.count-1 ' is free to read any line of data

' Data output with the field name ' Column1 ' in the row data

ListBox1.Items.Add (ds. Tables ("Table1"). Rows (i) ("Column1"). ToString ())

Next

DataGrid1.DataSource = ds. Tables ("Table1") can also bind the entire data table in DataSet DS to the data display control

' If you are an ASP. NET program, you also need the following line of statements to bind the DataSet DS to the data display space

' Datagrid1.databind ()

Vi. How to perform "add", "Modify", "delete" and other SQL commands with no results returned

To perform SQL commands that do not return results, such as add, modify, delete, and so on, simply execute the INSERT, Update, and delete commands from the command object, as shown in this example program. If you need access to a database, simply change the SqlConnection, SqlCommand in the program source code to OleDbConnection, OleDbCommand, respectively. At the same time the database connection string from "server= (local); Database=dbname; User id=a; password=b; " Change to "provider=microsoft.jet.oledb.4.0; Data Source=c:\db1.mdb; " The form.

[C # Version]

Using System.Data.SqlClient;

......

The meaning of the connection string is: Log on to the computer named "(local)" Database server, the login user name is a, the password is B,

and assume that a database named DBName has been created in SQL Server.

SqlConnection conn = new SqlConnection ("server= (local); Database=dbname; User id=a; password=b; ");

Conn.       Open (); Open a database connection

SqlCommand cmd = new SqlCommand (); To create an object cmd that executes a SQL command

Cmd.        Connection = conn; Specifies that CMD will transfer the SQL command over the conn connection

Cmd.commandtext = "Insert into MyTable (column1) VALUES (' Zhang San ')"; "Add Data" SQL command

If you need to execute the "Modify data" SQL command

Cmd.commandtext = "Update mytable Set column1= ' Zhang San ' Where id=1"; To modify a record with ID 1

If you want to execute the delete data SQL command

Cmd.commandtext = "Delete from mytable Where id=1"; Delete a record with an ID of 1

Cmd. ExecuteNonQuery (); Execute SQL command, return value indicates the number of records affected by the operation

Conn.   Close (); To close a database connection

[VB Version]

Imports System.Data.SqlClient

......

The meaning of the connection string is: Log on to the computer named "(local)" Database server, the login user name is a, the password is B,

' and assume that a database named DBName has been created in SQL Server.

Dim Conn as New SqlConnection ("server= (local); Database=dbname; User id=a; password=b; ")

Conn. Open () ' Opens the database connection

Dim cmd as New SqlCommand () ' Creates an object that executes the SQL command cmd

Cmd. Connection = Conn ' Specifies that CMD will transfer the SQL command over the conn connection

Cmd.commandtext = "Insert into MyTable (column1) VALUES (' Zhang San ')" "Add Data" SQL command

' If you want to execute the Modify data ' SQL command

' Cmd.commandtext = ' Update mytable Set column1= ' Zhang San ' Where id=1 ' ' Modify record with ID 1

' If you want to execute the delete data ' SQL command

' Cmd.commandtext = "Delete from mytable Where id=1" ' Delete record with ID 1

Cmd. ExecuteNonQuery () ' Execute SQL command, return value indicates the number of records affected by the operation

Conn. Close () ' Shut Down database connection

Vii. How to synchronize a DataSet dataset to a SQL Server database

SqlDataAdapter in addition to populating the dataset with query data from a SQL Server database, you can also synchronize the dataset dataset to a SQL Server database. Call Sqldataadapter.fill () to query the database to populate the dataset, just set the SelectCommand property to associate SqlDataAdapter with the SqlCommand component that implements the query function If you call Sqldataadapter.update () to perform a synchronous operation, you will need to set the InsertCommand, UpdateCommand, and DeleteCommand properties to insert the SqlDataAdapter separately from the implementation, 3 SqlCommand component associations that modify records, delete records, and this setting can be automatically completed by relying on the SqlCommandBuilder component.

[C # Version]

Using System.Data.SqlClient;

......

The meaning of the connection string is: Log on to the computer named "(local)" Database server, the login user name is a, the password is B,

and assume that a database named DBName has been created in SQL Server.

SqlConnection conn = new SqlConnection ("server= (local); Database=dbname; User id=a; password=b; ");

Conn.       Open (); Open a database connection

SqlCommand cmd = new SqlCommand (); To create an object cmd that executes a SQL command

Cmd.        Connection = conn; Specifies that CMD will transfer the SQL command over the conn connection

Cmd.commandtext = "SELECT * FROM MyTable"; Defines the SQL command that CMD will execute

SqlDataAdapter SqlDA = new SqlDataAdapter (cmd);//define the data adapter that will execute the cmd command

DataSet ds = new DataSet (); To create a DataSet DataSet object

Sqlda.fill (ds, "Table1");//Execute SQL command via data adapter, execute resulting data table populated to DataSet DS

Conn.   Close (); To close a database connection

......

program code for adding, deleting, and changing the DataTable of a DataSet

......

New SqlCommandBuilder (DA); Automatically set InsertCommand, UpdateCommand, DeleteCommand properties

Conn. Open ();

Da. Update (ds, "Table1");

Conn. Close ();

Ds. tables["Table1"]. AcceptChanges ();

If you increment, delete, and change the DataTable of a dataset to discard synchronization to the database, you can execute the following code:

Ds. tables["Table1"]. RejectChanges ();

Using ADO to access a database in a. NET Framework application