概括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 //存储过程的
 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,事务

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

本文代码下载

 

 

 

 

 

 

 

 

 

posted @   胡佳180815  阅读(4398)  评论(33)  编辑  收藏  举报
编辑推荐：
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
阅读排行：
· 周边上新：园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源！
· 分享 3 个 .NET 开源的文件压缩处理库，助力快速实现文件压缩解压功能！
· Ollama——大语言模型本地部署的极速利器
· [AI/GPT/综述] AI Agent的设计模式综述
点击右上角即可分享
微信分享提示
AI IDE Trae