ADO.Net的四大对象及参数-01总结

1.Connection 对象

web.config配置

<connectionStrings>
    <add name="connectionString" providerName="System.Data.SqlClient" connectionString="Data Source=.;database=cx_data;Persist Security Info=True;User ID=sa;Password=123123;"/>
  </connectionStrings>

 

调用：

  SqlConnection con = new SqlConnection();
        con.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
        con.Open();

 

 2.Command对象

 

     string sql = string.Format("insert into Test (username,userpwd) values('{0}','{1}')", this.txtusername.Text, this.txtpwd.Text);
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.CommandType = System.Data.CommandType.Text;

 

3.DataReader对象

示例1

 SqlConnection con = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from Members", con);
        try
        {
            SqlDataReader dr = cmd.ExecuteReader();
            this.GridView1.DataSource = dr;
            this.GridView1.DataBind();
        }
        catch (Exception ex)
        {
            this.lblinfo.Text = ex.Message;
        }
        finally
        {
            con.Close(); 
        }

 

示例2

 

SqlConnection con = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from Members", con);
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            this.TextBox1.Text += dr[5].ToString() + "\t" + dr["user_account"].ToString() + "\t" + dr.GetString(6).ToString()+"\r\n";
        }

 

 

4.DataAdapter对象

 

 SqlDataAdapter da = new SqlDataAdapter("select * from Members", System.Web.Configuration.WebConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);

        DataSet ds = new DataSet();
        da.Fill(ds, "学生信息");

        this.GridView1.DataSource = ds.Tables[0];
        this.GridView1.DataBind();

 

 

5.参数

 

//创建数据库连接
        SqlConnection con = new SqlConnection();
        con.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
        con.Open();
        //创建Command
        string sql = "update test set username=@username where userpwd=@userpwd";
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.CommandType = System.Data.CommandType.Text;
        SqlParameter spname = new SqlParameter();
        spname.ParameterName = "@username";
        spname.DbType = DbType.String;
        spname.SqlDbType = SqlDbType.NVarChar;
        spname.Size = 255;
        spname.Value = this.txtusername.Text;

        cmd.Parameters.Add(spname);//方式1

        cmd.Parameters.Add("@userpwd", SqlDbType.NVarChar).Value = this.txtpwd.Text;//方式2

        
        int count = cmd.ExecuteNonQuery();
        con.Close();
        this.lblinfo.Text = "操作成功！";