概括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 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";