asp.net下ADO.NET操作数据库的几种方式总结

第一种、通过直接在cs文件中编写查询语句调用 dbcom.ExecuteNonQuery();执行插入数据

     //连接字符串

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

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

DbProviderFactory dbproviderfactory = DbProviderFactories.GetFactory(ProviderName);

DbConnection dbcon = dbproviderfactory.CreateConnection();

dbcon.ConnectionString = ConnectionString;

DbCommand dbcom = dbproviderfactory.CreateCommand();

dbcom.Connection = dbcon;

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

dbcom.CommandType = CommandType.Text;

//添加参数

DbParameter dbparameter = dbproviderfactory.CreateParameter();

dbparameter.ParameterName = "@name";

dbparameter.DbType = DbType.String;

dbparameter.Value = "xiaolong";

dbcom.Parameters.Add(dbparameter);

dbparameter = dbproviderfactory.CreateParameter();

dbparameter.ParameterName = "@psw";

dbparameter.DbType = DbType.String;

dbparameter.Value = "123";

dbcom.Parameters.Add(dbparameter);

dbcon.Open();

try

{

   dbcom.ExecuteNonQuery();

}

catch(Exception ex)

{

   //将错误写入日志里

   AddLogError(ex.ToString());

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

}

finally

{

   dbcon.Close();

}

第二种、当然是调用存储过程

  //存储过程的

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

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

DbProviderFactory dbproviderfactory = DbProviderFactories.GetFactory(ProviderName);

DbConnection dbcon = dbproviderfactory.CreateConnection();

dbcon.ConnectionString = ConnectionString;

DbCommand dbcom = dbproviderfactory.CreateCommand();

dbcom.Connection = dbcon;

dbcom.CommandType = CommandType.StoredProcedure;

dbcom.CommandText = "InsertTable01";

DbParameter dbparameter = dbproviderfactory.CreateParameter();

dbparameter.DbType = DbType.String;

dbparameter.ParameterName = "@name";

dbparameter.Value = "xiaohe";

dbcom.Parameters.Add(dbparameter);

dbparameter = dbproviderfactory.CreateParameter();

dbparameter.ParameterName = "@psw";

dbparameter.DbType = DbType.String;

dbparameter.Value = "123";

dbcom.Parameters.Add(dbparameter);

dbcon.Open();

try

{

   dbcom.ExecuteNonQuery();

}

catch (Exception ex)

{

   AddLogError(ex.ToString());

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

}

finally

{

   dbcon.Close();

}

 第三种、就是可能同时插入两张表或者三张表或者更多。必须是同时，运用到了事务回滚机制。存储过程写法就不给出来了和上面一样。只是在存储过程编写事务。

//同时插入两张的表

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

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

DbProviderFactory dbproviderfactory = DbProviderFactories.GetFactory(ProviderName);

DbConnection dbcon = dbproviderfactory.CreateConnection();

dbcon.ConnectionString = ConnectionString;

DbCommand dbcom = dbproviderfactory.CreateCommand();

dbcom.Connection = dbcon;

dbcom.CommandType = CommandType.Text;

DbParameter dbparameter = dbproviderfactory.CreateParameter();

dbparameter.DbType = DbType.String;

dbparameter.ParameterName = "@name";

dbparameter.Value = "xiaohei";

dbcom.Parameters.Add(dbparameter);

dbparameter = dbproviderfactory.CreateParameter();

dbparameter.ParameterName = "@psw";

dbparameter.DbType = DbType.String;

dbparameter.Value = "123";

dbcom.Parameters.Add(dbparameter);

dbparameter = dbproviderfactory.CreateParameter();

dbparameter.DbType = DbType.String;

dbparameter.ParameterName = "@teacher";

dbparameter.Value = "heihei";

dbcom.Parameters.Add(dbparameter);

dbcon.Open();

//事务开始

DbTransaction dbtran = dbcon.BeginTransaction();

dbcom.Transaction = dbtran;

try

{

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

   dbcom.ExecuteNonQuery();

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

   dbcom.ExecuteNonQuery();

   //成功就提交

   dbtran.Commit();

}

catch (Exception ex)

{

   //出错就回滚

   dbtran.Rollback();

   AddLogError(ex.ToString());

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

}

finally

{

   dbcon.Close();

   dbtran.Dispose();

}

第四种、通过使用DbDataAdapter来获得查询的结果

  //查询数据库

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

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

DbProviderFactory dbproviderfactory = DbProviderFactories.GetFactory(ProviderName);

DbConnection dbcon = dbproviderfactory.CreateConnection();

dbcon.ConnectionString = ConnectionString;

DbCommand dbcom = dbproviderfactory.CreateCommand();

dbcom.Connection = dbcon;

dbcom.CommandText = "Select * from dt_Table01 where [email protected]";

dbcom.CommandType = CommandType.Text;

DbParameter dbparameter = dbproviderfactory.CreateParameter();

dbparameter.ParameterName = "@name";

dbparameter.DbType = DbType.String;

dbparameter.Value = "xiaolong";

dbcom.Parameters.Add(dbparameter);

DataSet ds = new DataSet();

DbDataAdapter dbDataAdapeter = dbproviderfactory.CreateDataAdapter();

dbDataAdapeter.SelectCommand = dbcom;

try

{

   dbDataAdapeter.Fill(ds, "data");

}

catch (Exception ex)

{

   AddLogError(ex.ToString());

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

}

finally

{

   dbDataAdapeter.Dispose();

}

第5种、使用DbDataReader或者查询结果，这里给出查询第一行第一列值，当然也可以直接调用Command.ExecuteScalar();函数

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

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

DbProviderFactory dbproviderfactory = DbProviderFactories.GetFactory(ProviderName);

DbConnection dbcon = dbproviderfactory.CreateConnection();

dbcon.ConnectionString = ConnectionString;

DbCommand dbcom = dbproviderfactory.CreateCommand();

dbcom.Connection = dbcon;

dbcom.CommandText = "Select * from dt_Table01 where [email protected]";

dbcom.CommandType = CommandType.Text;

DbParameter dbparameter = dbproviderfactory.CreateParameter();

dbparameter.ParameterName = "@name";

dbparameter.DbType = DbType.String;

dbparameter.Value = "xiaolong";

dbcom.Parameters.Add(dbparameter);

dbcon.Open();

DbDataReader dbDataReader=null;

string re = string.Empty;

try

{

  dbDataReader= dbcom.ExecuteReader(CommandBehavior.SingleRow);

}

catch (Exception ex)

{

   AddLogError(ex.ToString());

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

}

finally

{

   dbcon.Close();

   dbDataReader.Close();

}

//读入值

if (dbDataReader.Read())

{

   //得到第一列值

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

}

re = re + "hah";

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

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

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

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

来自:http://www.web2bar.cn/Article/5793.aspx