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

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  =   " 操作成功！ " ;