C#-ADO基础

1.ExecuteReader执行查询
      //定义连接
            using (SqlConnection conn = new SqlConnection(@"server=PC-201109081152\SQLEXPRESS;uid=sa;pwd=nko_123;database=Test"))
            {
                conn.Open();//
                using (SqlCommand cmd = conn.CreateCommand())//
                {
                    cmd.CommandText = "select * from Table1";//查询指令
                    using (SqlDataReader reader = cmd.ExecuteReader())//所有实现了Dispose接口的都是以using资源释放
                    {
                        while (reader.Read())//查询结果放服务器中，不占用本地内存，在服务器类似指针的东西，查询完成指针指向第一条结果之前，如果没移动到最后一条，返回就false
                        {
                            string SNo = reader.GetInt32(0).ToString();
                            string SName = reader.GetString(1);
                            MessageBox.Show(SNo+SName);//返回第一和第二列值，全部返回
                        }
                    }
                }
            }
        }

2.参数化查询避免SQL注入漏洞攻击
       using(SqlConnection conn=new SqlConnection(@"server=PC-201109081152\SQLEXPRESS;uid=sa;pwd=nko_123;database=Test"))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select Sname,SNo from Table1 where SSex='男' ";
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            //.GetInt32(1) 得到的是int
                            //.GetInt64(1)的到的是long
                            MessageBox.Show(reader.GetString(0) + reader.GetInt32(1));//这个0参数取决于查询结果的第几列，而不是数据库中，Sname是0，SNo是1

                        }
                    }
                }
            }

普通的参数化是采用拼接字符串：cmd.CommandText = "select Sname,SNo from Table1 where SSex='"+TextBox1.Text+"'";
当+TextBox1.Text中输入1或者1=1让where条件为真，就返回查询结果了

现在使用参数化：
                   cmd.CommandText = "select Sname,SNo from Table1 whereSSex=@sex";
                    cmd.Parameters.Add(new SqlParameter("@sex", TextBox1.Text));

3.DataSet
SqlDataReader中的查询结果并不是放程序中的，而是在数据库服务器，这样的好处是无论查询结果有多少条，对程序占用的内存都几乎没有影响

DataSet 的好处是降低数据库服务器的压力，编程也简单
使用例子：
  using (SqlConnection conn = new SqlConnection(@"server=PC-201109081152\SQLEXPRESS;uid=sa;pwd=nko_123;database=Test"))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from Table1";
                    //SqlDataAdapter是帮忙把查询结果添加到Dataset的类
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet dataset = new DataSet();
                    adapter.Fill(dataset);//把查询结果填充到dataset中
                    DataTable table = dataset.Tables[0];
                    DataRowCollection rows = table.Rows; //所有行的集合
                    //DataRow row = rows[0];
                    //int SNo = (int)row["SNo"];
                    //MessageBox.Show(SNo.ToString());
                    //rows是个集合也可以用for，foreatch输出所有
                    for (int i = 0; i < rows.Count; i++)
                    {
                        DataRow row = rows[0];
                        int SNo = (int)row["SNo"];
                        MessageBox.Show(SNo.ToString());
                    }
                }
            }
        }

 

4.连接字符串写到配置文件  新建app.config
在App.config中添加
  <appSettings>
    <add key="ConnenctionString" value="server=PC-201109081152\SQLEXPRESS;uid=sa;pwd=nko_123;database=Test" />
   </appSettings>
引用system.configuation

     string connStr = ConfigurationManager.AppSettings["ConnenctionString"];
      MessageBox.Show(connStr);