What is ADO and its simple implementation is detailed

First, Introduction

ADO is the data provider for the. NET Framework, which consists of the SqlConnection, SqlCommand, SqlDataAdapter, SqlDataReader, and dataset five object structures, such as

　　The 1.SqlConnection class represents a connection to a SQL Server database

The connection string format generally has the following two forms, the specific parameters can refer to the MSDN

1) Persist Security info=false;integrated security=true;initial catalog=northwind;server= (local)

2) server=.;D Atabase=demodb; User=sa; password=123;

Create a connection as follows

SqlConnection conn = New SqlConnection (connstring);

　　The 2.SqlCommand class represents the database execution command object

1) to set up SQL scripts or stored procedures, time-outs, parameters, and transactions that need to be executed.

2) Create the following method

SqlConnection conn = new SqlConnection();

// mode one

Conn. CreateCommand ();

// Way Two

SqlCommand cmd = new SqlCommand();

Cmd.commandtext = "SELECT * from table" ;

Cmd. Connection = conn;

3) Several main methods

ExecuteNonQuery: Executes the SQL statement on the connection and returns the number of rows affected, mainly by adding, deleting, and changing operations

ExecuteReader: Execute query to return SqlDataReader object

ExecuteScalar: Execute query returns first row and first column in result set

The 3.SqlDataAdapter class is used to populate the dataset and update database data commands and database connections

There are 4 types of constructors for this class:

public SqlDataAdapter ();

public sqlcommand selectcommand);

public string sqlconnection selectconnection);

public SqlDataAdapter (string selectcommandtext, string selectconnectionstring);

The 4.SqlDataReader class provides a data flow in-only read

The 5.DataSet class represents the cache of data in memory

Second, ADO. NET Simple implementation

An example of adding, deleting, changing, and checking is implemented below

public class Easysqlhelper {//web.config To configure//private static string connstring = Configurationmanager.ap        psettings["sqlConnectionString"]; private static string connstring = "server=.;D Atabase=demodb; User=sa;        password=123; "; public static int ExecuteNonQuery (String sql) {using (SqlConnection conn = new SqlConnection (connstring ) {using (SqlCommand cmd = new SqlCommand (SQL, conn)) {if ( Conn. state! = ConnectionState.Open) {Conn.                    Open (); } return cmd.                ExecuteNonQuery (); }}} public static SqlDataReader ExecuteReader (String sql) {SqlConnection con            n = new SqlConnection (connstring);            SqlCommand cmd = new SqlCommand (SQL, conn);            SqlDataReader RDR = null; try {if (conn. State! = ConnectionstaTe. Open) {Conn.                Open (); } rdr = cmd.            ExecuteReader (); } catch (SqlException ex) {conn.                Dispose (); Cmd.                Dispose (); if (RDR! = null) {rdr.                Dispose ();            } throw ex; } finally {cmd.            Dispose ();        } return RDR; The public static DataTable executedatatable (String sql) {using (SqlConnection conn = new Sqlconne                    Ction (connstring)) {using (SqlCommand cmd = new SqlCommand (SQL, conn)) { IF (Conn. state! = ConnectionState.Open) {Conn.                    Open ();                    } SqlDataAdapter ADP = new SqlDataAdapter (cmd);                    DataSet ds = new DataSet (); Adp. Fill (DS);                    Return DS.                Tables[0]; }            }        }    }

Using a using to ensure resource deallocation is used in the above code, and all classes that implement the IDisposable interface can be freed with a using, even if an exception occurs on the method that invokes the object.

Iii. creating instances of data source classes for different providers

The above code is only valid for SQL Server, and if you want to implement a different database, such as Oracle, you need to write another set of code. NET provides the DbProviderFactory class to create different db instances.

At the same time, the above 5 large objects are also replaced with DbConnection, DbCommand, DbDataReader, dbdataadapter to abstract specific SQL Server objects into more specific and database type-independent objects.

<summary>///connection information///</summary> public class ConnectionInfo {private String _connec        tionstring;        private string _providername;            <summary>///connection string///</summary> public string ConnectionString {        get {return _connectionstring;}            }///<summary>///provider's fixed name///</summary> public string ProviderName {        get {return _providername;} } public ConnectionInfo (string connectionString, String providerName) {_connectionstring = Conne            ctionstring;        _providername = ProviderName;        }} public class Mysqlhelper {private static dbproviderfactory dbprovider; private static readonly ConnectionInfo Conninfo = new ConnectionInfo ("server=.;D Atabase=demodb; User=sa;        password=123; "," System.Data.SqlClient "); private static void Getprovider () {DBprovider = Dbproviderfactories.getfactory (conninfo.providername);        } static Mysqlhelper () {getprovider ();            public static int ExecuteNonQuery (String sql, dbparameter[] parameters) {int flag = 0; using (DbConnection conn = Dbprovider.createconnection ()) {Conn.                ConnectionString = conninfo.connectionstring; Conn.                Open (); using (DbCommand cmd = conn.                    CreateCommand ()) {cmd.commandtext = SQL; if (Parameters! = null && parameters. Length > 0) {cmd.                    Parameters.addrange (Parameters); } flag = cmd.                ExecuteNonQuery ();        }} return flag;             } public static void ExecuteReader (String sql, dbparameter[] parameters, action<idatareader> Action) { IDataReader RDR = nUll using (DbConnection conn = Dbprovider.createconnection ()) {Conn.                ConnectionString = conninfo.connectionstring; Conn.                Open (); using (DbCommand cmd = conn.                    CreateCommand ()) {cmd.commandtext = SQL; if (Parameters! = null && parameters. Length > 0) {cmd.                    Parameters.addrange (Parameters); } rdr = cmd.                    ExecuteReader ();                    Action (RDR); Rdr.                Close ();            }}} public static DataTable executedatatable (String sql, dbparameter[] parameters) {            DataTable dt = null; using (DbConnection conn = Dbprovider.createconnection ()) {Conn.                ConnectionString = conninfo.connectionstring; Conn.                Open (); using (DbCommand cmd = conn. CreatecommanD ()) {cmd.commandtext = SQL; if (Parameters! = null && parameters. Length > 0) {cmd.                    Parameters.addrange (Parameters); } IDataReader rdr = cmd.                    ExecuteReader ();                    DT = new DataTable (); Dt.                    Load (RDR); Rdr.                                    Close ();            } return DT; }        }    }

Iv. other third-party frameworks

There are dapper, ibatis.net and so on, you can refer to the study

"Recommended"

1. ASP. NET free Video Tutorials

2. C # uses ADO to update and add data to Excel tables

3. ADO read the implementation Code of Excel ((C #))

4. ADO. NET calling stored procedures

5. ADO Connection vs Database Code