一起談.NET技術，概括ADO.NET資料庫連接配接的所有形式(基礎）

　　你可能熟悉其中部分資料庫讀取的形式，但是熟悉全部的估計很少，或者你完全忘記了，因為這些形式被封裝到你自己架構的底層，自己搞過一兩次就在也沒有動手寫過了。但是筆者覺得這部分知識是應該牢固掌握的，這個是基礎。

第一種：通過直接在cs檔案中編寫查詢語句調用 dbcom.ExecuteNonQuery();執行插入資料

1 //連接配接字元串

2  string ConnectionString = ConfigurationManager.ConnectionStrings["db_Test01ConnectionString"].ConnectionString;

3  string ProviderName = ConfigurationManager.ConnectionStrings["db_Test01ConnectionString"].ProviderName;

4 DbProviderFactory dbproviderfactory = DbProviderFactories.GetFactory(ProviderName);

5 DbConnection dbcon = dbproviderfactory.CreateConnection();

6 dbcon.ConnectionString = ConnectionString;

7 DbCommand dbcom = dbproviderfactory.CreateCommand();

8 dbcom.Connection = dbcon;

9 dbcom.CommandText = "Insert into dt_Table01(name,psw) values(@name,@psw)";

10 dbcom.CommandType = CommandType.Text;

11

12  //添加參數

13 DbParameter dbparameter = dbproviderfactory.CreateParameter();

14 dbparameter.ParameterName = "@name";

15 dbparameter.DbType = DbType.String;

16 dbparameter.Value = "xiaolong";

17 dbcom.Parameters.Add(dbparameter);

18 dbparameter = dbproviderfactory.CreateParameter();

19 dbparameter.ParameterName = "@psw";

20 dbparameter.DbType = DbType.String;

21 dbparameter.Value = "123";

22 dbcom.Parameters.Add(dbparameter);

23 dbcon.Open();

24  try

25 {

26 dbcom.ExecuteNonQuery();

27 }

28  catch(Exception ex)

29 {

30 //将錯誤寫入日志裡

31 AddLogError(ex.ToString());

32 Response.Redirect("~/ErrorPage.aspx");

33 }

34  finally

35 {

36 dbcon.Close();

37 }

第二種：當然是調用存儲過程

1 //存儲過程的

9 dbcom.CommandType = CommandType.StoredProcedure;

10 dbcom.CommandText = "InsertTable01";

11 DbParameter dbparameter = dbproviderfactory.CreateParameter();

12 dbparameter.DbType = DbType.String;

13 dbparameter.ParameterName = "@name";

14 dbparameter.Value = "xiaohe";

15 dbcom.Parameters.Add(dbparameter);

16 dbparameter = dbproviderfactory.CreateParameter();

17 dbparameter.ParameterName = "@psw";

18 dbparameter.DbType = DbType.String;

19 dbparameter.Value = "123";

20 dbcom.Parameters.Add(dbparameter);

21 dbcon.Open();

22  try

23 {

24 dbcom.ExecuteNonQuery();

25 }

26  catch (Exception ex)

27 {

28 AddLogError(ex.ToString());

29 Response.Redirect("~/ErrorPage.aspx");

30 }

31  finally

32 {

33 dbcon.Close();

34 }

第三種：就是可能同時插入兩張表或者三張表或者更多。必須是同時，運用到了事務復原機制。存儲過程寫法就不給出來了和上面一樣。隻是在存儲過程編寫事務。

1 //同時插入兩張的表

9 dbcom.CommandType = CommandType.Text;

10

14 dbparameter.Value = "xiaohei";

21 dbparameter = dbproviderfactory.CreateParameter();

22 dbparameter.DbType = DbType.String;

23 dbparameter.ParameterName = "@teacher";

24 dbparameter.Value = "heihei";

25 dbcom.Parameters.Add(dbparameter);

26

27 dbcon.Open();

28  //事務開始

29 DbTransaction dbtran = dbcon.BeginTransaction();

30 dbcom.Transaction = dbtran;

31

32 try

33 {

34 dbcom.CommandText = "insert into dt_table01(name,psw) values(@name,@psw)";

35 dbcom.ExecuteNonQuery();

36 dbcom.CommandText = "insert into dt_table03(teacher,psw) values(@teacher,@psw)";

37 dbcom.ExecuteNonQuery();

38 //成功就送出

39 dbtran.Commit();

40 }

41 catch (Exception ex)

42 {

43 //出錯就復原

44 dbtran.Rollback();

45 AddLogError(ex.ToString());

46 Response.Redirect("~/ErrorPage.aspx");

47 }

48 finally

49 {

50 dbcon.Close();

51 dbtran.Dispose();

52 }

第四種：通過使用DbDataAdapter來獲得查詢的結果

1 //查詢資料庫

2 string ConnectionString = ConfigurationManager.ConnectionStrings["db_Test01ConnectionString"].ConnectionString;

3 string ProviderName = ConfigurationManager.ConnectionStrings["db_Test01ConnectionString"].ProviderName;

9 dbcom.CommandText = "Select * from dt_Table01 where name=@name";

12 dbparameter.ParameterName = "@name";

13 dbparameter.DbType = DbType.String;

14 dbparameter.Value = "xiaolong";

16 DataSet ds = new DataSet();

17 DbDataAdapter dbDataAdapeter = dbproviderfactory.CreateDataAdapter();

18 dbDataAdapeter.SelectCommand = dbcom;

19 try

20 {

21 dbDataAdapeter.Fill(ds, "data");

22 }

23 catch (Exception ex)

24 {

25 AddLogError(ex.ToString());

26 Response.Redirect("~/ErrorPage.aspx");

28 finally

30 dbDataAdapeter.Dispose();

31 }

第5種：使用DbDataReader或者查詢結果，這裡給出查詢第一行第一列值，當然也可以直接調用Command.ExecuteScalar();函數

1 string ConnectionString = ConfigurationManager.ConnectionStrings["db_Test01ConnectionString"].ConnectionString;

2 string ProviderName = ConfigurationManager.ConnectionStrings["db_Test01ConnectionString"].ProviderName;

3 DbProviderFactory dbproviderfactory = DbProviderFactories.GetFactory(ProviderName);

4 DbConnection dbcon = dbproviderfactory.CreateConnection();

5 dbcon.ConnectionString = ConnectionString;

6 DbCommand dbcom = dbproviderfactory.CreateCommand();

7 dbcom.Connection = dbcon;

8 dbcom.CommandText = "Select * from dt_Table01 where name=@name";

10 DbParameter dbparameter = dbproviderfactory.CreateParameter();

11 dbparameter.ParameterName = "@name";

13 dbparameter.Value = "xiaolong";

14 dbcom.Parameters.Add(dbparameter);

15 dbcon.Open();

16 DbDataReader dbDataReader=null;

17 string re = string.Empty;

18 try

19 {

20 dbDataReader= dbcom.ExecuteReader(CommandBehavior.SingleRow);

21 }

22 catch (Exception ex)

24 AddLogError(ex.ToString());

25 Response.Redirect("~/ErrorPage.aspx");

26 }

27 finally

28 {

29 dbcon.Close();

30 dbDataReader.Close();

32 //讀入值

33 if (dbDataReader.Read())

34 {

35 //得到第一列值

36 re = dbDataReader.GetValue(0).ToString();

38 re = re + "hah";

當然還可以自己編寫一些常用的調用例如：或者查詢結果第一列的值等等。

大緻上就是以上這幾種形式了。

存儲過程，Command直接執行，DataReader,DataAdapter,事務

由于筆者知識有限，如果還有其他形式請您補充上來

<a href="http://files.cnblogs.com/couhujia/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E8%BF%9E%E6%8E%A5%E5%BD%A2%E5%BC%8F.rar">本文代碼下載下傳</a>