What is ado.net and its simple implementation details

1. Introduction

Ado.net is the data provider of .net framework. It mainly consists of five objects: SqlConnection, SqlCommand, SqlDataAdapter, SqlDataReader and DataSet. The structure is as follows

　1.SqlConnection class represents a connection to a sql server database

The connection string format generally has the following two forms, specifically Parameters can refer to msdn

          1) Persist Security Info=False;Integrated Security=true;Initial Catalog=Northwind;server=(local)

            2) Server=.;Database=demodb;User =sa;Password=123;

Create a connection as follows

SqlConnection conn = new SqlConnection(connString);

　2.SqlCommand class represents the database execution command Object

1) Used to set the SQL script or stored procedure, timeout, parameters and transactions that need to be executed.

  2) The creation method is as follows

        SqlConnection   conn =   new   SqlConnection();

##                                                                                                                                                                                                           CreateCommand(); ##             

//

Method 2

SqlCommand cmd = new

SqlCommand

(); cmd.CommandText = "select * from table" ;##     cmd.Connection = conn;

3) Several main methods ExecuteNonQuery: Execute sql statements on the connection and return the number of affected rows, mainly performing add, delete and modify operations

ExecuteReader: Execute the query and return the SqlDataReader object

ExecuteScalar: Execute the query and return the first row and column of the result set

3.SqlDataAdapterClass is used to fill DataSet and update database data commands and database connections

This class has 4 constructors as follows

public SqlDataAdapter();

##        public SqlDataAdapter(SqlCommand selectCommand);

             public SqlDataAdapter(string selectCommandText, SqlConnection selectConnection);

##       

public SqlDataAdapter(string selectCommandText, string selectConnectionString);

4.SqlDataReader

class provides a data stream-only way to read

5.DataSet

class represents the cache of data in memory

2. Simple implementation of ADO.NET

The following implements an add, delete, modify, Check the example

public class EasySqlHelper
    {
        //web.config来配置
        //private static string connString = ConfigurationManager.AppSettings["SqlConnectionString"];
        private static string connString = "Server=.;Database=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 conn = 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;
        }

        public static DataTable ExecuteDataTable(string sql)
        {
            using (SqlConnection conn = new SqlConnection(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];
                }
            }
        }
    }

The above code uses using to ensure resource release. All classes that implement the IDisposable interface can be released using using, even if it occurs when calling the object's method. Exceptions are also released.

3. Create data source class instances of different providers

The above code is only valid for sql server. If you want to implement different databases such as Oracle, you need to write another one. Set of codes, .Net provides the DbProviderFactory class to create different database instances.

At the same time, the above five major objects should also be replaced with DbConnection, DbCommand, DbDataReader, and DbDataAdapter to abstract the specific sql server objects into more specific objects that have nothing to do with the database type.

//// <summary>
    /// 连接信息
    /// </summary>
    public class ConnectionInfo
    {
        private string _connectionString;
        private string _providerName;

        /// <summary>
        /// 连接字符串
        /// </summary>
        public string ConnectionString
        {
            get { return _connectionString; }
        }

        /// <summary>
        /// 提供程序的固定名称
        /// </summary>
        public string ProviderName
        {
            get { return _providerName; }
        }

        public ConnectionInfo(string connectionString, string providerName)
        {
            _connectionString = connectionString;
            _providerName = providerName;
        }
    }

    public class MySqlHelper
    {
        private static DbProviderFactory dbProvider;

        private static readonly ConnectionInfo connInfo = new ConnectionInfo("Server=.;Database=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;
            }
        }
    }

4. Other third-party frameworks

There are Dapper, IBatis.Net, etc., you can refer to them for reference

【Related Recommendations】

1.

ASP.NET Free Video Tutorial

2.

C#Use Ado.Net to update and add data to Excel tables Method

3.

ADO.NET reads the EXCEL implementation code ((c#))

4.

ADO.NET calls the stored procedure

5.

ado.net connection vs database code

The above is the detailed content of What is ado.net and its simple implementation details. For more information, please follow other related articles on the PHP Chinese website!