ADO.NET基本使用

1,引用命名空间

　　　using System.Data.SqlClient;

2，定义五大对象

        SqlConnection con = null;
        SqlCommand cmd = null;
        SqlDataAdapter adapter = null;
        SqlDataReader reader = null;
        DataSet set = null;

3，给五大对象赋值

（1）SqlConnection

　　  第一种方式：使用命令构建者连接数据库
        SqlConnectionStringBuilder conStr2 = new SqlConnectionStringBuilder();
        conStr2.DataSource = @"TARENA-54\SQLEXPRESS";
        conStr2.InitialCatalog = "others";
        conStr2.IntegratedSecurity = true;
        使用时 ：con.ConnectionString =conStr2.ToString();

　　  第二种方式：直接将数据库连接写出来

　　　通过用户名和密码：string str=@"database=pubs;server=localhost\SQLEXPRESS;uid=sa;pwd=123456;"

　　　通过window验证：string str=@"Data Source=localhost\SQLEXPRESS;Initial Catalog=others;Integrated Security=True";

  　　第三种方式：根据配置文件的连接串连接数据库

        在cs代码中获取连接串的方式：

　　  con.ConnectionString = ConfigurationManager.ConnectionStrings["mySql"].ToString();
       con.ConnectionString =ConfigurationManager.ConnectionStrings["db"].ConnectionString .ToString();

　在配置文件中的配置为：
<configuration>
  <connectionStrings >
    <add name ="mySql" connectionString ="Data Source=TARENA-54\SQLEXPRESS;Initial Catalog=others;Integrated Security=True"/>
      <add name="mySql" connectionString="database=SatisfiedCheckDBS;server=HQMSQLClustern\PPSDBInst3;uid=PPSUser;pwd=3007$PPSSQLClient#;"/>
  </connectionStrings>
</configuration>

 

（2）comment的常用法：
     com = new SqlCommand();
            com.Connection = con;
            com.CommandText = "select * from obi";
            com.CommandType = CommandType.Text;
            while (reader.Read())
            {
                ListBox1.Items.Add(reader.GetValue(0).ToString());
                ListBox2.Items.Add(reader.GetValue(1).ToString());
                ListBox3.Items.Add(reader.GetValue(2).ToString());

            }
            reader.Close();

4使用方式

第一种：
            con = new SqlConnection();
            con.ConnectionString = str;
            con.Open();
             
            cmd = new SqlCommand("select * from obi", con);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                comboBox1.Items.Add(reader.GetValue(0));
            }
第二种：
     con = new SqlConnection(@"Data Source=TARENA-54\SQLEXPRESS;Initial Catalog=others;Integrated Security=True");
     con.Open();
            cmd = new SqlCommand("insert obi values(" + IDStr + ",'" + strstr + "','" + dateStr + "')", con);
            cmd.ExecuteNonQuery();

第三种：
     adapter = new SqlDataAdapter(@"select * from obi", con);
            ds = new DataSet();
            adapter .Fill(ds, "obi");  //obi是表名
            listBox1.Items.Add("obi");   //在listBox1显示
     dataGridView1.DataSource = ds.Tables["obi"] ;//在dataGridView1显示