Ado. NET Technology

Connection object

1. Connect to the database

Determine the connection status of a database by SqlConnection the State property of the object:

public override ConnectionState state{get;}

Property Value: ConnectionState Enumeration

Enumeration value Description

Broken connection to the data source is interrupted

Closed connection is off

Connecting connection object is connecting to the data source

Executing the Connection object is executing the command

Fetching the Connection object is retrieving data

Open Connection is active

Try

{

String constr = "server=.; Database=hmliang;uid=sa;pwd= ";

SqlConnection conn = new SqlConnection (CONSTR);

Conn. Open ();

IF (Conn. state = = ConnectionState.Open)

{

}

Conn. Close ();

Conn. Dispose ();

}

catch{}

2. Close the connection

The Close method is used to close a connection.

The Dispose method not only closes the connection, but also cleans up the resources that the connection occupies.

Note: When writing an application, when the database operation is complete, close the connection to the database in a timely manner to prevent the database from being consumed when other operations are performed on the database.

Command object

1.3 Important attributes:

a). The connection property is used to set the SqlConnection used by SqlCommand

b). The CommandText property is used to set the SQL statement or stored procedure to be executed against the data source

c). The CommandType property is used to set the type of the specified CommandText.

CommandType Enumeration Members:

StoredProcedure: Name of the stored procedure

TableDirect: Name of the table

Text:sql text command

SqlCommand cmd = new SqlCommand ();

Cmd. Connection = conn;

Cmd.commandtext = "Select COUNT (*) from A";

Cmd.commandtype = CommandType.Text;

int i = Convert.ToInt32 (cmd. ExecuteScalar ());

2. Execute SQL statements

1). ExecuteNonQuery method

Executes the SQL statement and returns the number of rows affected.

public override int ExecuteNonQuery ();

2). ExecuteReader method

Executes the SQL statement and generates an instance of the SqlDataReader object that contains the data.

Public SqlDataReader ExecuteReader ()

SqlDataReader SDR = cmd. ExecuteReader ();

while (SDR. Read ())

{

SDR[1]. ToString ();

}

Sdr.close ();

3). ExecuteScalar method

Executes the SQL statement, returning the first column of the first row in the result set

public override Object ExecuteScalar ()

DataReader Object

1. Determine if there is a value in the query result

public override bool hasrows{get;};

2. Reading data

public override bool Read ()

Pulbic override bool Close ()

DataAdapter Object

1. Overview of objects

The DataAdapter object is a data adapter object and a bridge between the dataset and the data source.

The DataAdapter object provides 4 properties that enable interoperability with data sources:

a). SelectCommand property: Send query SQL statement to database

b). DeleteCommand property: Sending a Delete SQL statement to the database

c). InsertCommand property: Sending an INSERT SQL statement to the database

D). UpdateCommand property: Sending an update SQL statement to the database

2. Populating the DataSet DataSet

public int Fill (DataSet dataset,string srctable)

DataSet: A DataSet to populate with records and schemas (if necessary)

Srctable: Name of the source table used for table mappings

Return value: The number of rows that have been successfully added or refreshed in the dataset, excluding rows affected by statements that do not return rows.

SqlDataAdapter SDA = new Sqldataadaper ();

Sda. SelectCommand = cmd;

DataSet ds = new DataSet ();

Sda. Fill (DS, "CS");

3. Updating the data source

public int Update (DataTable DataTable)

DataTable: DataTable for updating data sources

Return value: Number of rows successfully updated in the dataset

Create a DataTable

DataTable dt = ds. Tables["CS"];

Loading the table structure into the Tb_command table

Sda. FillSchema (DT, schematype.mapped);

Create a DataRow

DataRow dr = dt. Rows.find (Txtno.text);

Set a value in a DataRow

dr["name"] = TxtName.Text.Trim ();

Instantiate a SqlCommandBuilder

SqlCommandBuilder cmdbuilder = new SqlCommandBuilder (SDA);

Call its Update method to update the DataTable to the database

Sda. Update (DT);

Note: The Fill method can be used more than once on a DataTable object. If the primary key exists, the pass is merged with the existing matching row, and if the primary key does not exist, the incoming row is appended to the DataTable.

DataSet object

1. Overview

The DataSet object is like a small database stored in memory. It can contain data tables, columns, rows, views, constraints, and relationships. Typically, data from a dataset is derived from a database or XML, and in order to get data from a database, you need to use a data adapter (DataAdapter) to query the data from the database.

2. Merging DataSet contents

public void Merge (

DataSet DataSet,

BOOL PreserveChanges,

MissingSchemaAction MissingSchemaAction)

DataSet: Its data and schema will be merged into the dataset

PreserveChanges: True to preserve changes in the current dataset; False otherwise

Missingschemaaction:missingschemaaction One of the enumeration values

MissingSchemaAction enumeration members and descriptions

Enumeration member Description

Add the required columns to complete the schema

AddWithKey adding the required columns and primary key information to complete the schema

Error generates InvalidOperationException if the specified column mappings are missing

Ignore Ignore extra columns

Note: You cannot merge a DataSet object when it is null.

DS1. Merge (Ds,true,missingschemaaction.addwithkey);

2. Copy the DataSet contents

Public DataSet Copy ()

"C # from Beginner to proficient" after reading summary

Ado. NET Technology