ASP.NET总结ADO.NET操作数据库五大对象

<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();
        }

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

ASP.NET总结ADO.NET操作数据库五大对象

标签：