一起谈.NET技术，概括ADO.NET数据库连接的所有形式(基础）-阿里云开发者社区

一起谈.NET技术，概括ADO.NET数据库连接的所有形式(基础）

2019-06-01 864

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 　　你可能熟悉其中部分数据库读取的形式，但是熟悉全部的估计很少，或者你完全忘记了，因为这些形式被封装到你自己框架的底层，自己搞过一两次就在也没有动手写过了。但是笔者觉得这部分知识是应该牢固掌握的，这个是基础。

　　你可能熟悉其中部分数据库读取的形式，但是熟悉全部的估计很少，或者你完全忘记了，因为这些形式被封装到你自己框架的底层，自己搞过一两次就在也没有动手写过了。但是笔者觉得这部分知识是应该牢固掌握的，这个是基础。

第一种：通过直接在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 // 存储过程的
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.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 // 同时插入两张的表
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.CommandType = CommandType.Text;
10
11 DbParameter dbparameter = dbproviderfactory.CreateParameter();
12 dbparameter.DbType = DbType.String;
13 dbparameter.ParameterName = " @name " ;
14 dbparameter.Value = " xiaohei " ;
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 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;
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 = " Select * from dt_Table01 where name=@name " ;
10 dbcom.CommandType = CommandType.Text;
11 DbParameter dbparameter = dbproviderfactory.CreateParameter();
12 dbparameter.ParameterName = " @name " ;
13 dbparameter.DbType = DbType.String;
14 dbparameter.Value = " xiaolong " ;
15 dbcom.Parameters.Add(dbparameter);
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 " );
27 }
28 finally
29 {
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 " ;
9 dbcom.CommandType = CommandType.Text;
10 DbParameter dbparameter = dbproviderfactory.CreateParameter();
11 dbparameter.ParameterName = " @name " ;
12 dbparameter.DbType = DbType.String;
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)
23 {
24 AddLogError(ex.ToString());
25 Response.Redirect( " ~/ErrorPage.aspx " );
26 }
27 finally
28 {
29 dbcon.Close();
30 dbDataReader.Close();
31 }
32 // 读入值
33 if (dbDataReader.Read())
34 {
35 // 得到第一列值
36 re = dbDataReader.GetValue( 0 ).ToString();
37 }
38 re = re + " hah " ;

当然还可以自己编写一些常用的调用例如：或者查询结果第一列的值等等。

大致上就是以上这几种形式了。

存储过程，Command直接执行，DataReader,DataAdapter,事务

由于笔者知识有限，如果还有其他形式请您补充上来

本文代码下载

一起谈.NET技术，概括ADO.NET数据库连接的所有形式(基础）

热门文章

最新文章

相关课程

相关电子书

相关实验场景