C # connecting SQL Server database (ADO)

ado

Classes that expose data access services to. NET programmers are used to access data sources such as Microsoft SQL Server and XML, and also provide a way to provide consistent access to the data sources exposed by OLE DB and XML.

Two models for accessing data: no connection model (offline) and connection model (online).

No connection downloads data to the client and encapsulates the data in memory on the client, and then accesses the in-memory data (such as a dataset) as if it were accessing a local relational database.

The connection model relies on record-wise access, which requires opening and maintaining a connection to the data source.

Use connection, Command, DataAdapter when online

Use connection, Command, DataAdapter, Dataset when offline

Ado. NET's core objects

· Connection (connection), used to establish a connection to a specific data source

· command to execute a SQL command statement or stored procedure against a data source

· DataReader (data reader) for fetching read-only, forward-only data streams from the data source

· DataAdapter (data adapter) for exchanging data between a data source and a dataset

· DataSet (DataSet), used to process data read from a data source, representing the cache of data in memory

To connect to a database using a Connection object

Main members:

· ConnectionString property connection string that gets or sets the information connected to the database

· The open () method opens the database connection using the property settings specified by connectionstring

· The close () method closes the connection to the database

· StateChange event that is triggered when the connection state changes

Steps to use the Connection object:

⑴ introducing the ADO namespace

⑵ Create a Connection object and set its ConnectionString property

⑶ Opening a connection to the database

⑷ Read and write to a database

⑸ Closing a connection to the database

⑴ introducing the ADO namespace

SQL Server data Provider System.Data.SqlClient

ODBC Data Provider System.Data.Odbc

OLE DB data Provider System.Data.OleDb

Oracle Data Provider System.Data.OracleClient

⑵ Create a Connection object and set its ConnectionString property

If you use SQL Server authentication, the connection string is typically:

Data source= server name; Initial catalog= database name; User id= account; password= password

If you use Windows authentication, the connection string is typically:

Data source= server name; Initial catalog= database name; Integrated SECURITY=SSPI or

Data source= server name; Initial catalog= database name; Trusted Connection=yes

Server name: Refers to the name of the server where the database resides, or it can be written as an IP address, or, in the case of a local server, "." (local) "127.0.0.L" or "Native machine name"

· SqlConnection Connection object name =new SqlConnection ();

The name of the connection object. connectionstring= connection string;

• Connection string variable = connection string;

SqlConnection Connection object name =new SqlConnection (connection string variable);

Cases:

SqlConnection conn=New  sqlcounnection (); Conn. ConnectionString= "Data source=(local); Initial  Catalog=studentrecord;integrated  security= SSPI "; or strconn=" Data source=.; I Nitial Catalog=studentrecord; trusted_connection=yes "; SqlConnection conn=new SqlConnection (strconn);

The name of the connection object. State:

Broken connection to the data source is broken

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

Conn. State==connectionstate.open

Open and close a database connection

After setting the ConnectionString property.

The name of the connection object. Open ();

The name of the connection object. Close ();

The name of the connection object. Dispose (); This shutdown can no longer be opened with open

StateChange Events

Occurs when the connection state changes. The handler for the event receives a parameter of type Statechangeeventargs

There are two properties: CurrentState: The new state used to get the connection

Originalstate: Used to get the original state of the connection

Execute SQL statement using Command object

Sends SQL commands to the database. If the command is retrieved, the data retrieved from the database can be placed in the DataAdapter or DataReader object.

Command key members:

Connection property: Gets or sets the connection object used by the Command object

CommandType property: Name of the StoredProcedure stored procedure

TableDirect: Name of the table

Text:sql text command

CommandText property: Gets or sets the SQL command to execute against the database

ExecuteNonQuery () Method: Executes the SQL command (Insert, Delete, Update) that does not return rows, and returns the number of rows affected.

ExecuteReader () Method: Executes the SELECT command and returns a generated DataReader object

ExecuteScalar () Method: Executes the SELECT command and returns the first column {single value} of the first row in the result set of the query, ignores other columns or rows, and returns a null reference if the result is empty, typically used to count records, sums, averages

The Command object uses the general steps:

• Create a Command object and set its Connection property

• Set CommandType and CommandText properties

• Call the response method to execute the SQL command

• Appropriate processing based on return results

Creating and using SqlCommand objects

• The first type

SqlCommand Command object name =new SqlCommand ();

The name of the Command object. connection= Connection object name;

The name of the Command object. Commandtype=commandtype. Enumeration members;

The name of the Command object. commandtext= command text;

The method returns a value variable = Command object name. Execute .... （）；

• The second type

SqlCommand Command object name =new SqlCommand (command text, connection object name);

The name of the Command object. Commandtype=commandtype. Enumerating objects;

The method returns a value variable = Command object name. Execute .... （）；

Cases:

SqlCommand  comm=new SqlCommand ("select from Studentinfo", conn);/                 / Conn is a previously set SqlConnection object int icount= comm. ExecuteScalar (); MessageBox.Show (common in the Studentinfo table "+icount.tostring () +" records "); Note: ExecuteScalar () is typically used with aggregate functions

Reading data using the DataReader object

Dataread reads the online data access method of the forward-only stream of a row, the Dataread data is a read-only, scroll-only stream that is returned by the database, so it is ideal for applications that need to read only once.

Main members:

FieldCount property: Gets the number of columns in the current row, the default value is-1, if it is not placed in a valid recordset,

Then the 0

HasRows property: Gets a value that indicates whether the DataReader object contains one or more rows

IsClosed property: Gets a value that indicates whether the DataReader object is closed

RecordsAffected property: Gets the number of rows that are changed, inserted, or deleted by the execution SQL statement;

The row is a SELECT statement with a return value of-1

Close () method: Closes the DataReader object and should call the Close method every time it is exhausted

GetName (int index) method: Gets the name of the specified column; parameter i is a 0-based column ordinal

GetOrdinal (String name) method: Gets the column ordinal in the case of the name of the given column; The parameter name is the column name

GetValue (int i) method: Gets the value of the specified column in its native format, which is the object type, and the parameter I is a 0-based column ordinal

Nextreault () Method: When the result of a batch SQL statement is read, the data reader advances to the next

Result set, the return value is Boolean, true if more than one result set exists

Read () Method: Advances the data to the next record; The return value is Boolean and true if there is a record

Example: SqlDataReader reader=//SqlConnection object is set and open, Comm is a previously set SqlCommand object      while(reader. Read ())          {// read a row of data}

Data adapter: DataAdapter Object

Plays a bridge between the dataset and the data source.

Property:

SelectCommand: Sends a query SQL statement to the database.

DeleteCommand: Sends a DELETE SQL statement to the database.

InsertCommand: Sends an INSERT SQL statement to the database.

UpdateCommand: Sends an UPDATE SQL statement to the database.

The Main method:

Fill method: Used primarily to populate dataset datasets

public int Fill (DataSet dataset,string sctable);

DataSet: Datasets populated by records and schemas

Srctable name used for table mappings and source tables

Return value: Number of rows that have been successfully added or refreshed in the dataset

Update method: Updating the database

Populating a DataSet DataSet

SqlConnection conn;conn=new SqlConnection ("server=.; database=db_14;uid=sa;pwd="); SqlCommand cmd=new SqlCommand ("Select * from Tb_command", conn); SqlDataAdapter SDA=new  SqlDataAdapter (); SDA. SelectCommand=cmd;dataset ds=new  DataSet (); SDA. Fill (ds, "Tb_xx");d atagridview1.datasource=ds. table[0];

To update the data source:

DataTable dt=ds. tables["Tb_xx"];SDA. FillSchema (dt,schematype.mapped);D Atarow dr=dt. Rows.find (txtno.text);d r["name"]=TxtName.Text.Trim ();d r["gender"]=TxtSex.Text.Trim ();d r["age"] =TxtAge.Text.Trim ();d r["Bonus"]=txtjj. Text.trim (); SqlCommandBuilder Cmdbudider=new  SqlCommandBuilder (SDA);  Sda. Update (DT); 

Datasets: DataSet objects

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

Copy Dataset:dateset Ds1=ds. Copy ();

Datagridview2.datasource=ds1. TABLE[0];

C # connecting SQL Server database (ADO)