你的位置:首页 > ASP.net教程

[ASP.net教程]ASP.NET总结ADO.NET操作数据库五大对象


1、Connection对象主要提供与数据库的连接功能

配置web.config文件

<appSettings>
<add key="ConnectionString" value="Server=10.136.*.*;database=MTL;uid=sa;pwd=sa;"/>
</appSettings>

创建获取配置节的连接字符串

public SqlConnection GetSqlConnection()

{
   string str = ConfigurationManager.AppSettings["ConnectionString"].ToString();
   SqlConnection sqlconn = new SqlConnection(str);
   return sqlconn;

}

    string str = "Server=(local); userid=sa;pwd=123456;database=SQLName";    //创建SqlConnection对象,并设置连接数据库字符串    SqlConnection sql = new SqlConnection(str);    sql.Open();//打开数据库连接    //数据库相关操作.....    sql.Close();//关闭数据库连接(不关闭将会消耗内存资源)

示例代码

 

2、Command对象主要对数据源进行增、删、查、改等操作;下面举几个例子:

     查询操作

 if (SqlCommandText.Text != "")    {      try      {        SqlConnection MyConn = GetSqlConnection();        MyConn.Open();        string StrString = "select * from Table1 where State=@State";        SqlCommand Sqlcmd = new SqlCommand(StrString, MyConn);        Sqlcmd.Parameters.Add("@State", SqlDbType.NVarChar, 2).Value = this.SqlCommandText.Text.Trim();        SqlDataAdapter MyDataAdpater = new SqlDataAdapter(Sqlcmd);        DataSet ds = new DataSet();        MyDataAdpater.Fill(ds);        if (ds.Tables[0].Rows.Count > 0)        {          Response.Write("存在此信息!");        }        else        {          Response.Write("不存在此信息!");        }      }      catch (Exception ex)      {        Response.Write(ex.Message);      }    }

示例代码

     添加操作

    if (this.SqlCommandText.Text.Trim() != "")    {      try      {        SqlConnection SqlConn = GetSqlConnection();        SqlConn.Open();        string Str = "insert into Table1(State) values('" + this.SqlCommandText.Text.Trim() + "')";        SqlCommand Sqlcomm = new SqlCommand(Str, SqlConn);        Sqlcomm.ExecuteNonQuery();        SqlConn.Close();        Response.Write("添加成功!");      }      catch (Exception ex)      {        Response.Write(ex.Message);      }    }

示例代码

     调用存储过程

    //存储过程代码,向db_table里面插入数据;    use db_table    go    Create proc ProcClass    (@Name varchar(50))    as    insert into Table1(Name) values(@Name)    go    //存储过程可以使管理数据库和显示数据库信息等错做变得非诚容易,它是SQL语句和可选控制流语句的预编译集合,    //存储在数据库内,在程序中可以通过SqlCommand对象来调用,其执行速度比SQL语句块    SqlConnection Sqlconn = GetSqlConnection();    Sqlconn.Open();    SqlCommand cmd = new SqlCommand("ProcClass", Sqlconn);    cmd.CommandType = CommandType.StoredProcedure;    cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = this.SqlCommandText.Text.Trim();    cmd.ExecuteNonQuery();    cmd.Dispose();    Sqlconn.Close();

示例代码

    实现事务的处理

    //事务:事务是由一组相关任务组成的单元,该单元的任务要么全部成功,要么全部失败;事务最终执行的结果是两种状态,即提交和终止;    SqlConnection Sqlconn = GetSqlConnection();    Sqlconn.Open();    string str = "insert into EquipmentInfo(State) values('" + this.SqlCommandText.Text.Trim() + "')";    SqlTransaction SqlTran = Sqlconn.BeginTransaction();    SqlCommand cmd = new SqlCommand(str, Sqlconn);    cmd.Transaction = SqlTran;    try    {      cmd.ExecuteNonQuery();      SqlTran.Commit();      Sqlconn.Close();      Response.Write("添加成功!");    }    catch (Exception ex)    {      Response.Write("添加失败!");      throw;    }

示例代码

 

3、DataAdapter数据适配器和DataSet数据集

SqlDataAdapter对象是DataSet对象和数据源之间联系的桥梁;
主要从数据源中检索数据,填充DataSet对象中的表或者把用户对DataSet对象做出的修改写入数据源;

    DataSet ds = new DataSet();    string str = "select * from Table1";    SqlConnection conn = GetSqlConnection();    conn.Open();    SqlDataAdapter sqlda = new SqlDataAdapter(str, conn);    sqlda.Fill(ds);

示例代码

 

4、DataReader对象读取器 

读取器以基于连接的,快速的、未缓冲的及只向前移动的方式来读取数据,
一次读取一条记录,然后遍历整个结果集;

    SqlConnection Sqlconn = GetSqlConnection();    string str = "select * from Table1";    SqlCommand cmd = new SqlCommand(str, Sqlconn);    cmd.CommandType = CommandType.Text;    try    {      Sqlconn.Open();      //执行sql语句,并返回DataReader对象      SqlDataReader Reader = cmd.ExecuteReader();      this.SqlCommandText.Text = "序号,新闻内容 ";      while (Reader.Read())      {        this.SqlCommandText.Text += Reader["NewsID"] + "," + Reader["NewsContent"];      }      Reader.Close();    }    catch (Exception ex)    {      Response.Write(ex.ToString());    }    finally     {      Sqlconn.Close();    }

示例代码

----------------个人学习过程中的一些总结,写的有不对的地方还请多多指教---------------------