概括ADO.NET中Database数据库连接形式(基础）

　　第一种

　　通过直接在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         try25         {26             dbcom.ExecuteNonQuery();27         }28         catch(Exception ex)29         {30             //将错误写入日志里31             AddLogError(ex.ToString());32             Response.Redirect("~/ErrorPage.aspx");33         }34         finally35         {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         try23         {24             dbcom.ExecuteNonQuery();25         }26         catch (Exception ex)27         {28             AddLogError(ex.ToString());29             Response.Redirect("~/ErrorPage.aspx");30         }31         finally32         {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         try33         {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         finally49         {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         try20         {21             dbDataAdapeter.Fill(ds, "data");22         }23         catch (Exception ex)24         {25             AddLogError(ex.ToString());26             Response.Redirect("~/ErrorPage.aspx");27         }28         finally29         {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         try19         {20            dbDataReader= dbcom.ExecuteReader(CommandBehavior.SingleRow);21         }22         catch (Exception ex)23         {24             AddLogError(ex.ToString());25             Response.Redirect("~/ErrorPage.aspx");26         }27         finally28         {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";

本文代码下载