ASP. NET

* Connection object usage: SqlConnection, SqlCommand, and SqlDataAdapter
* Data Access Method

1. Get data:

// Reference the two namespaces

Using System. Data. SqlClient;
Using System. Data;

// Initialize the connection object
SqlConnection conn = new SqlConnection ();
Conn. ConnectionString = "User ID = sa; Initial Catalog = DataBaseName; Data Source = (local); Password = 111111 ";

// Open the connection
If (conn. State = ConnectionState. Closed)
{
Conn. Open ();
}

// Initialization command
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd. CommandType = CommandType. Text;
Cmd. CommandText = "SQL statement ";

// This operation is used to insert, update, and delete data. The number of affected rows is returned.
Int I = cmd. ExecuteNonQuery ();

If (I> 0) {MessageBox. Show ("operation successful ");}
// The operation that is used to query the maximum value and so on. Only one data record is returned, and the data in the first column in the first row is returned.

Object obj = cmd. ExecuteScalar ();

// If you want to obtain a data set, we often use a data adapter. $ fire $ network $ defense # collection
DataTable dt = new DataTable ();
SqlDataAdapter adapter = new SqlDataAdapter ();
Adapter. SelectCommand = cmd;
Adapter. Fill (dt );

2. Bind data to the Data Control

String str = "Data Source =.; Initial Catalog = GridView; User ID = sa; Password = 111111 ";

String SQL = "select * from UserName ";
SqlConnection conn = new SqlConnection (str );
// Conn. Open (); Use SqlDataAdapter (data adapter) without writing

// SqlCommand comm = new SqlCommand (SQL, conn );
// SqlDataAdapter dr = new SqlDataAdapter (comm );

SqlDataAdapter dr = new SqlDataAdapter (SQL, conn); // the above two sentences can be merged into this one

DataSet ds = new DataSet (); // create a DataSet;

Dr. Fill (ds); // Fill in strong datasets $ fire $ network $ defense # collection # Set

This. GridView1.DataSource = ds;
This. GridView1.DataBind (); // bind the data source to the control,
// Conn. Close (); Close the database connection
If (conn. State = ConnectionState. Open) // judge the database connection status and whether to connect
{
Conn. Close ();
}

3. Use SqlDataReader:

To create a SqlDataReader, you must call the ExecuteReader method of the SqlCommand object instead of using the constructor directly.

String str = "Data Source =.; Initial Catalog = GridView; User ID = sa; Password = 111111 ";
String SQL = "select * from UserName ";
SqlConnection conn = new SqlConnection (str );
Conn. Open ();
SqlCommand comm = new SqlCommand (SQL, conn );
DataSet ds = new DataSet ();
SqlDataReader dr = comm. ExecuteReader ();

If (dr. Read ())
{
// The following two types of data can be obtained:
// This. TextBox1.Text = dr. GetString (1 );
// This. TextBox2.Text = dr. GetInt32 (3). ToString ();

This. TextBox1.Text = dr. GetString (dr. GetOrdinal ("Name "));
This. TextBox2.Text = dr. GetInt32 (dr. GetOrdinal ("Age"). ToString ();
}

// Cyclic output

While (dr. Read ())
{
Response. Write (dr ["Name"]);
Response. Write (dr ["Age"]);
Response. Write ("<br/> ");
}
Dr. Close ();
If (conn. State = ConnectionState. Open)
{
Conn. Close ();
}

SqlDataReader: provides a method to read rows from the SQL Server database only into the stream.