30、ADO.NET、事務、DataSet

2019-11-14 15:50:29

字體：大中小

來源：轉載

供稿：網友

ADO.NET

ADO.NET是一組用于和數據源進行交互的面向對象類庫。通常數據源是數據庫，但也可以是文本文件、Excel表格、xml文件。

說白了就是使用.net操作數據庫的一套類庫。

ADO.NET 命名空間

System.Data; //描述數據的命名空間

System.Data.SqlClient; //針對SQL Server的命名空間

System.Data.OleDB; //access用這個

System.Data.Odbc;

System.Data.OracleClinet; //Oracle用這個

ADO.NET 四大類庫

Connection //用來和數據庫建立連接。

Command //執行查詢、修改、刪除等命令。

　　ExecuteNonQuery() //執行非查詢語句，返回受影響行數如果執行非增、刪、改操作返回-1。

　　ExecuteScalar() //返回第一行第一列結果返回Object值

　　ExecuteReader() //讀取多行數據

DataReader //讀取一串數據，從DataReader返回的數據都是快速的只是向前的數據流。

DataAdapter //讀取后緩存下來，相當于離線數據庫，包含對連接對象以及當對數據庫進行讀取或者寫入的時候自動的打開或者關閉連接的引用

OledbConnection、OdbcConnection、SqlConnection 用來鏈接不同的數據庫，換個前綴即可。

Connection 類

connection 用于和數據源建立連接，在執行任何操作之前必須建立連接。

創建Connection對象時，需要提供連接字符串，連接字符串是用；分號分割的一系列名稱/值的選項，用來描述連接的基本信息。

IDbConnection 接口定義了核心的connection屬性和方法，該接口由所有connection類實現。

1、使用當前登陸到windows的用戶身份訪問數據庫，無需輸入密碼。

string conStr = "Data Source=.; Initial Catalog=hzsweb; Integrated Security=SSPI";

2、使用賬號密碼連接數據庫。

string conStr = "Server=localhost;DataBase=hzsweb;uid=sa;pwd=123;";

連接字符串中需要指定數據庫所在的服務器（locahost 和. 都是本地的意思），數據庫名稱，以及驗證方式。

因為數據庫放到服務器后 ip 數據庫名都要經常改變，所以常把連接字符串放在配置文件中。

設置web.config

<configuration>  <connectionStrings>    <add name="connStr" connectionString="Server=localhost;DataBase=hzsweb;uid=sa;pwd=123;"/>  </connectionStrings></configuration>

添加引用

引用->右鍵->添加->框架->選擇->System.Configuration。

在ConnectionStrings集合中獲取連接字符串。

var conStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ToString();

以后如果數據庫參數有變動，只需要修改配置文件即可。

測試連接

var conStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ToString();//創建connection對象System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();con.ConnectionString = conStr; //也可以使用用構造方法寫在括號中//打開連接con.Open();context.Response.Write(con.State.ToString()); //輸出連接狀態//關閉連接con.Close();

結果為Open，已連接狀態。需要注意的是，每次使用完畢需要關閉連接，釋放資源。con.Close()效果等同于con.Dispose()。

使用using語句自動釋放資源

using (con){    //打開連接    con.Open();}context.Response.Write(con.State.ToString()); //輸出連接狀態

輸出結果為Closed，已關閉。可以看出在using語塊后是自動釋放資源的。必須實現IDispose接口，才可以使用using語句自動釋放。

連接池

連接池保持已經打開的數據庫連接，這些連接在使用相同數據源的會員間共享，這樣就省了不斷創建和銷毀連接的時間。

當客戶端調用open()方法請求打開連接時，連接直接由連接池提供而不是再次創建，當調用Close()方法釋放時，它并沒有被釋放，而是重新回到池中等待下一次請求。

<add name="connStr" connectionString="Server=localhost;DataBase=hzsweb;uid=sa;pwd=123;pooling=true;min pool size=5;max pool size=10"/>

pooling ：是否打開連接池，默認為true。

min pool size ：連接池中最小連接數。

max pool size ：連接池中允許最大連接數。

查看連接池狀態可以使用sql系統存儲過程sp_who2如：

exec sp_who2

如果設置最小連接池數量為5，則數據庫中就有 5 個對應數據庫名的連接，如果沒有操作，其狀態就會處于 sleeping 狀態。

Command 類

command類可以執行所有類型的SQL語句，和connection類一樣，實現了IDbCommand接口。

在使用command類操作數據時，需要指明三個屬性。CommandType，CommandText和Connection。

CommandType 枚舉值：Text（一條SQL語句，默認值），StoredPRocedure（存儲過程），TableDirect（表的名稱，獲取表的所有記錄）。

使用ExecuteNonQuery()插入一條數據

int result;var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();//創建connection對象using (var con = new SqlConnection(conStr)){    //創建command對象    using (var cmd = new SqlCommand())    {        cmd.CommandType = System.Data.CommandType.Text; //默認值 sql語句        cmd.CommandText = "insert into article ([title],[content],[createTime]) values ('title','content','2015-08-31')"; //執行腳本        cmd.Connection = con; //cmd使用的連接        con.Open(); //打開連接        result = cmd.ExecuteNonQuery(); //返回受影響行數 插入、修改、刪除 都用這個    }}context.Response.Write(result);

返回結果為1。

使用ExecuteScalar()返回表中的總記錄數

var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();int count;using (var con = new SqlConnection(conStr)){    using (var cmd = new SqlCommand("select count(*) from news", con)) //構造函數 type默認就是text    {        con.Open();        count = Convert.ToInt32(cmd.ExecuteScalar()); //返回第一行第一個字段的值    }}

DataReader 類

可以讀取多條數據，從DataReader返回的數據都是快速的只是向前的數據流。

DataReader的常用方法：

Read() ：判斷下一行是否讀取到數據，如果有返回true，否則為false。

GetValue() : 返回當前行中指定需要的字段值。

getValues() : 將當前行中的值保存到數組中。可以使用DataReader.FieldCount屬性確定一行記錄的列數。

NextResult() : 多個結果集時，用來獲取下一個結果集

Close() ：關閉Reader

使用ExecuteReader()方法查詢表中所有記錄

var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();System.Text.StringBuilder sb = new System.Text.StringBuilder();using (var con = new SqlConnection(conStr)){    using (var cmd = new SqlCommand("select * from news", con))    {        con.Open();        var rd = cmd.ExecuteReader();        while (rd.Read()) //一直讀取到沒有為止        {            //sb.Append(rd["title"]);            sb.Append(rd.GetValue(1));            sb.Append(rd.GetString(2));            sb.Append("<br/>");        }
　　　　 rd.Close();    }}

CommandBehavior 自動關掉關聯連接

ExecuteReader()方法有一個重載，可以在關閉DataReader的同時，自動關掉關聯的連接。//con.Close()

var rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);

封裝為方法調用時，很有用。

多個結果集

當在一個sql語句中使用；分號分割多個查詢結果集時，可以使用rd.Nextresult()來找到下一個結果集。

var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();System.Text.StringBuilder sb = new System.Text.StringBuilder();using (var con = new SqlConnection(conStr)){    using (var cmd = new SqlCommand("select top 10 * from news;select top 10 *  from article", con))    {        con.Open();        var rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);        int i = 1;        do        {            sb.Append("<h2>第" + i + "個結果集：</h2>");            while (rd.Read())            {                for (int j = 0; j < rd.FieldCount; j++)                {                    sb.Append("<li>");                    sb.Append(rd.GetName(j).ToString()); //獲取字段名                    sb.Append("：");                    sb.Append(rd.GetValue(j).ToString()); //獲取字段值                    sb.Append("</li>");                }            }            i++;        } while (rd.NextResult());        rd.Close();    }}

結果為拆分的兩組前10條數據。

SQL注入

由于拼接字符串的不安全，前臺輸入追加條件 or '1' = '1' 則永遠為真。以及輸入些其他對數據庫表操作的語句。或者輸入--把你后面的代碼都注釋掉。

解決辦法1： string.Replace("/'","/'/'"); 將所有1個單引號轉換為兩個單引號

解決方法2：參數化編程，將需要拼接值的地方，用一個參數變量表示，而操作數據庫的時候，給這個參數賦值。

參數化編程

1、將需要拼接字符串的值，用一個@引導的變量名代替。

2、使用SqlParameter類型將參數變量與值綁定在一起。

3、將SqlParameter對象交給Command對象的Prarmeters集合。

using (var con = new SqlConnection(conStr)){    using (var cmd = new SqlCommand("delete from article where id = @id", con))    {        con.Open();        cmd.Parameters.Add("@id", 1197);        sb.Append(cmd.ExecuteNonQuery());    }}

調用存儲過程

創建一個存儲過程，添加指定數據，并返回新增的id。

use hzswebgocreate proc InsertArticle    @title varchar(250),    @content varchar(250),    @createTime datetime,    @lastId int outputasbegin    insert into article (title,content,createTime)        values (@title,@content,@createTime);    set @lastId = @@IDENTITY;end

在c#中調用存儲過程

var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();using (var con = new SqlConnection(conStr)){    using (var cmd = new SqlCommand("InsertArticle", con))    {        cmd.CommandType = CommandType.StoredProcedure; //存儲過程        con.Open();        cmd.Parameters.AddRange(new SqlParameter[]{            new SqlParameter("@title","標題"),            new SqlParameter("@content","內容"),            new SqlParameter("@createTime",DateTime.Now.ToString()),            new SqlParameter("@lastId",SqlDbType.Int,4)        });        cmd.Parameters["@lastId"].Direction = ParameterDirection.Output; //設置為 輸出參數        int result = cmd.ExecuteNonQuery();        int lastId = (int)cmd.Parameters["@lastId"].Value;        context.Response.Write(lastId);    }}

事務

事務是一組必須全部成功或全部失敗的操作，事務的目標是保證數據總能處于有效一致的狀態。

事務有4個被稱為ACID屬性的特征。

Atomic（原子性）：事務中的所有步驟必須同時成功或失敗。

Consist（一致性）：事務使底層數據庫在穩定狀態間轉換。

lsolated（隔離性）：每個事務都是獨立的實體，不會互相影響。

Durable（持久性）：在事務成功前，事務產生的變化永久的存儲在硬盤上。

使用事務時不要在事務中間使用select語句返回數據，應該在事務開始前返回數據，這樣可以減少事務鎖定數據的數目。

在T-SQL中使用事務

在T-SQL中使用Begin transaction 開始事務，使用commit提交事務，使用rollback回滾事務。

create proc updateSort(    @sortA int,    @sortB int,    @id_a int,    @id_b int)as    begin try        begin transaction            update article set sort = @sortA where id = @id_a;            update article set sort = @sortB where id = @id_b;        commit --提交    end try    begin catch        if(@@trancount>0)            rollback --回滾    end catchexec updateSort 50,51,1,2

在C#中使用事務

使用connection對象.BeginTransaction()方法返回一個transaction對象，用于管理事務。

var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();using (var con = new SqlConnection(conStr)){    var cmd1 = new SqlCommand("update article set sort = @sortA where id = @idA", con);    var cmd2 = new SqlCommand("update article set sort = @sortB where id = @idB", con);    cmd1.Parameters.AddRange(new SqlParameter[] {         new SqlParameter("@sortA",100),        new SqlParameter("@idA",1)    });    cmd2.Parameters.AddRange(new SqlParameter[] {         new SqlParameter("@sortB",101),        new SqlParameter("@idB",2)    });    SqlTransaction tran = null;    try    {        con.Open();        tran = con.BeginTransaction();        cmd1.Transaction = tran;        cmd2.Transaction = tran;        cmd1.ExecuteNonQuery();        cmd2.ExecuteNonQuery();        tran.Commit();    }    catch    {        tran.Rollback();    }}

可以使用tran.save("abc");方法指定保存點，并使用tran.Rollback("abc");回滾到某保存點。回滾全部無需輸入參數。

DataSet

DataSet（數據集）內存中的數據庫。

在SQL Server中

數據庫對象（實例/Sqlexpress）

架構（dbo，表的命名空間）

表（列數據）

行

DataSet中

DataSet實例（new DataSet()）

Tables表集合 newDataTable（表名，命名空間）

DataColumn （DataTable中的列）

DataRow（DataTable中的行）

DataAdapter 類

要在DataSet中提取記錄并將它們填入表中，需要使用DataAdapter對象，它含有查詢和更新的全部命令。

SelectCommand 查詢數據。

var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();using (var con = new SqlConnection(conStr)){    //創建DataAdapter對象，并設置查詢語句和數據庫連接    SqlDataAdapter sda = new SqlDataAdapter();    //或直接使用構造方法new SqlDataAdapter("sql",con);    sda.SelectCommand = new SqlCommand();    sda.SelectCommand.Connection = con;    sda.SelectCommand.CommandText = "select * from article";    DataSet ds = new DataSet();    //將數據填充到數據集，使用Fill()時自動打開連接    sda.Fill(ds);    DataTable dt = ds.Tables[0]; //第一個數據表    foreach (DataRow row in dt.Rows)    {        context.Response.Write(row[0]);        context.Response.Write(row[1]);        context.Response.Write(row[2]);        context.Response.Write(row[3]);        context.Response.Write("<hr>");    }}

DeleteComand 刪除數據

var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();using (var con = new SqlConnection(conStr)){    SqlDataAdapter sda = new SqlDataAdapter("select top 10 id,title from article",con);    DataSet ds = new DataSet();    sda.Fill(ds);    sda.DeleteCommand = new SqlCommand("delete  from article where id = 1",con);    sda.Update(ds);    DataTable dt = ds.Tables[0];    foreach (DataRow row in dt.Rows)    {        context.Response.Write(row[0]);        context.Response.Write(row[1]);        context.Response.Write("<hr>");    }}

修改使用UpdateCommand，添加使用InsertCommand 不要忘記Update(ds) 否則不會更新。

手動創建一個DataSet

var ds = new DataSet("web");var dt = new DataTable("table1");var colId = new DataColumn("id", typeof(int));var colName = new DataColumn("name", typeof(string));var colSex = new DataColumn("sex", typeof(string));colId.AutoIncrement = true; //自增colId.AutoIncrementSeed = 1; //起始1colId.AutoIncrementStep = 1; //遞增colId.Unique = true; //唯一dt.Columns.Add(colId);dt.Columns.Add(colName);dt.Columns.Add(colSex);ds.Tables.Add(dt);var row = dt.NewRow();row[0] = 1;row[1] = "奉先";row[2] = "男";dt.Rows.Add(row);foreach (DataRow r in dt.Rows){    context.Response.Write(r[0]);    context.Response.Write(r[1]);    context.Response.Write(r[2]);    context.Response.Write("<hr>");}

SQLHelper

將一些重復的數據庫連接Connection,Command,DataReader等封裝成一個類，調用方法時只需要傳入一些參數和數據庫連接字符串就可以訪問數據庫了。

public static class SQLHelper{    public static readonly string conStr = ConfigurationManager.ConnectionStrings["conStr"].ToString();    /// <summary>    /// 執行增、刪、改操作，返回受影響行數    /// </summary>    /// <param name="sql"></param>    /// <param name="cmdParams"></param>    /// <returns></returns>    public static int ExecuteNonQuery(string sql, params SqlParameter[] cmdParams)    {        using (var con = new SqlConnection(conStr))        {            using (var cmd = new SqlCommand(sql, con))            {                cmd.Parameters.AddRange(cmdParams);                con.Open();                return cmd.ExecuteNonQuery();            }        }    }    /// <summary>    /// 返回首行首列信息    /// </summary>    /// <param name="sql"></param>    /// <param name="cmdParams"></param>    /// <returns></returns>    public static object ExecuteScalar(string sql, params SqlParameter[] cmdParams)    {        using (var con = new SqlConnection(conStr))        {            using (var cmd = new SqlCommand(sql, con))            {                cmd.Parameters.AddRange(cmdParams);                con.Open();                return cmd.ExecuteScalar();            }        }    }    /// <summary>    /// 返回只進的讀取流    /// </summary>    /// <param name="sql"></param>    /// <param name="cmdParams"></param>    /// <returns></returns>    public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] cmdParams)    {        var con = new SqlConnection(conStr);        try        {            using (var cmd = new SqlCommand(sql, con))            {                cmd.Parameters.AddRange(cmdParams);                con.Open();                return cmd.ExecuteReader(CommandBehavior.CloseConnection);            }        }        catch (Exception e)        {            con.Close();            throw e;        }    }    /// <summary>    /// 返回DataTable    /// </summary>    /// <param name="sql"></param>    /// <param name="cmdParams"></param>    /// <returns></returns>    public static DataTable getDataTable(string sql, params SqlParameter[] cmdParams)    {        var ds = new DataSet();        using (var sda = new SqlDataAdapter(sql, conStr))        {            sda.SelectCommand.Parameters.AddRange(cmdParams);            sda.Fill(ds);        }        return ds.Tables[0];    }}

調用

//修改string sql = "update article set sort = @sort where id = @id";var prams = new SqlParameter[]{    new SqlParameter("@sort",15),    new SqlParameter("@id",3)};int result = SQLHelper.ExecuteNonQuery(sql);//條數string sqlCount = "select count(*) from article";var count = SQLHelper.ExecuteScalar(sqlCount);//DataReaderstring readerSql = "select id,title,createTime from article";using (var rd = SQLHelper.ExecuteReader(readerSql)){    while (rd.Read())    {        ////    }}//DataTablestring dtSql = "select id,title,createTime from article";var dt = SQLHelper.getDataTable(dtSql);foreach (DataRow row in dt.Rows){    ///}