分装一个SqlHelper类方便使用，着重理解params SqlParameters[] commandParamters

浏览数：70 / 时间：2015年06月12日
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using _17DateStr.DataSet1TableAdapters;
/* DataSet
 * 每次读取的数据都创建连接，执行Command得到SqlDataReader太麻烦，让我们封装一个方法
 * 
 * SqlDataReader对于小数据量的数据来说带来的只有麻烦，优点可以忽略不计
 * 
 * SqlDataReader是连接相关的，SqlDataReader中的查庖结果并不是放到程序中的
 * 而是放在数据库服务器中，SqlDataReader只是相当于放了一个指针(游标),只能读取当前游标指向的行
 * 一旦连接开就不能再读取，这样做的好处就是无论查询结果有多少条，对程序占用的内存都几乎没有影响
 * ADO.Net中提供了数据集的机制，将查询结果填充到本地内存中，这样连接断开，服务器断开都不影响数据的读取
 * DataSet dataset = new DataSet();
 * SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dataset);
 * 
 * SqlDataAdapter是DataSet和数据库之间的沟通的桥梁,数据集DataSet包含若干表DataTable,DataTable包含若干行DataRow
 * foreach(DataRow row in dataset.Tables[0].Rows){
 *    row["name"];
 * }
 * 
 * 
 * SQLHelper
 * 封装一个SQLHelper类方便使用，提供ExecuteData Table(string sql, params SqlParameter[] parameters)
 * ExecuteNonQuery(string sql, params SqlParameter[] parameters)
 * ExecuteScalar(string sql, params SqlParameter[] parameters)
 * 等方法，网上有自由软件提供的最全的SQLHelper类，是Enterprise Libarary 中的一部分
 * 用Sqlhelper重写登陆程序
 * 用SQLHelper重写省市选择程序, 电话地址归属地查询
 * new SqlParameter("e",0)的陷阱
 * sqlconnection在程序中一直保持它open可以吗?对于数据库来说，连接是非常宝贵的资源，一定要用完了就close display
 * 
 *
 * DataSet的更新
 * 可以更新row["Name"] = "yzk"; 删除行datatable.Rows.Remove(),新增行datatable.NewRow(); 这一切都是修改的内存中的DataSet,没有修改数库
 * 可以调用SqlDataAdpater的update方法将对DataSet的修改提交到数据库，Update方法有很多重载方法,可以提交整个DataSet,DataTable,或者若干DataRow
 * 但是需要为sqlDataAdapter提供DeleteCommand, UpdateCommand InsertCommand它才知道如何将对DataSet的修改提交到数据库
 * 由于这几个Command要求格式非常苛刻，因此开发人员自己写非常困难，可以用SqlCommandBuilder自动生成这几个command，用法很简单
 * new SqlCommandBuilder(adapter) 查看生成的Command(没有直接赋值给SqlDataAdapter)
 * 看SqlCommandBuilder的, sqlCommandBuilder要求表必须有主键
 * (*)通过DataRow的RowState可以获得行的状态(删除,修改,新增等);调用DataSet的GetChanges()方法得到变化的结果集,降低传递的资源占用
 * 
 */
namespace _17DateStr
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            SqlHelp.ExecuteNonQuery("Insert into T_Persons(name,age) values(@name,@age)", new SqlParameter("name","tom"),new SqlParameter("age",11));
            MessageBox.Show("插入成功!");
        }
 
        private void button2_Click(object sender, EventArgs e)
        {
            object i = SqlHelp.ExecuteScalar("select count(*) FROM T_Persons");
            MessageBox.Show("记录总数为:"+Convert.ToString(i));
        }
 
        private void button3_Click(object sender, EventArgs e)
        {
            SqlDataReader reader = SqlHelp.ExecuteReader("Select * FROM T_Persons");
            while (reader.Read())
            {
                string name = reader.GetString(reader.GetOrdinal("name"));
                MessageBox.Show("用户名为:" + name);
            }
        }
 
        private void button4_Click(object sender, EventArgs e)
        {
            /*string connstr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * FROM T_Persons";
                    DataSet dataset = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dataset);
                    DataTable table = dataset.Tables[0];
                    for (int i = 0; i < table.Rows.Count; i++) 
                    {
                        DataRow row = table.Rows[i];
                        string name = Convert.ToString(row["name"]);
                        MessageBox.Show(name);
                    }
                }
            }*/
            DataSet dataset = new DataSet();
            string connstr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * FROM T_Persons";
                    
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dataset);
                }
            }
            DataTable table = dataset.Tables[0];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                string name = Convert.ToString(row["name"]);
                MessageBox.Show(name);
            }
 
        }
 
        private void button5_Click(object sender, EventArgs e)
        {
            DataTable table = SqlHelp.ExecuteDataTable("select * FROM T_Persons");
            foreach (DataRow row in table.Rows) 
            {
                string name = Convert.ToString(row["name"]);
                MessageBox.Show(name);
            }
 
        }
 
        private void tb_login_Click(object sender, EventArgs e)
        {
            string name = tb_username.Text;
            string password = tb_password.Text;
            if (name != "" && password != "")
            {
                DataTable table = SqlHelp.ExecuteDataTable("select * FROM T_User where name=@name", new SqlParameter("name", name));
                if (table.Rows.Count >= 1)
                {
                    DataRow row = table.Rows[0];
                    int errorTimes =Convert.ToInt32(row["errortimes"]);
                    if(errorTimes >= 3)
                    {
                        MessageBox.Show("用户被锁定,暂时不能登陆!");
                        return;
                    }else{
                        string p = Convert.ToString(row["password"]);
                        if(p == password){
                           MessageBox.Show("登陆成功!");
                           SqlHelp.ExecuteNonQuery("update T_User set errortimes=0 where name=@name", new SqlParameter("name",name));
                           return;
                        }else{
                            
                           MessageBox.Show("登陆失败!");
                           SqlHelp.ExecuteNonQuery("update T_User set errortimes = errortimes+1 where name = @name",new SqlParameter("name",name));
                           return;
                        }
                    }
                }
                else {
                    MessageBox.Show("用户名不存在!");
                }
            }
            else {
                MessageBox.Show("用户名和密码不能为空");
            }
             
        }
 
        private void Form1_Load(object sender, EventArgs e)
        {
            DataTable table = SqlHelp.ExecuteDataTable("select * FROM promary");
            cb_promary.DisplayMember = "name";
            foreach (DataRow row in table.Rows) 
            {
                 Promary pr = new Promary();
                 pr.id = Convert.ToInt32(row["proID"]);
                 pr.name = Convert.ToString(row["proName"]);
 
                 cb_promary.Items.Add(pr);
            }
        }
 
        private void cb_promary_SelectedIndexChanged(object sender, EventArgs e)
        {
            Promary pr = (Promary)cb_promary.SelectedItem;
            int id = pr.id;
            string name = pr.name;
            DataTable table = SqlHelp.ExecuteDataTable("select * FROM city WHERE proID=@proID",new SqlParameter("proID",id));
            cb_city.Items.Clear();
            cb_city.DisplayMember = "name";
            foreach (DataRow row in table.Rows) 
            {
                Promary p = new Promary();
                p.id = Convert.ToInt32(row["cityID"]);
                p.name = Convert.ToString(row["cityName"]);
                cb_city.Items.Add(p);
            }
        }
 
        private void button6_Click(object sender, EventArgs e)
        {
            DataTable table = SqlHelp.ExecuteDataTable("select * FROM T_User WHERE id = @id",new SqlParameter("id",(object)0));
        }
 
        private void button7_Click(object sender, EventArgs e)
        {
            String connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connStr)) 
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand()) 
                {
                    cmd.CommandText = "select * FROM T_Persons";
 
                    DataSet dataset = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dataset);
 
                    DataTable table = dataset.Tables[0];
                    DataRow row = table.Rows[0];
                    row["name"] = "jason"; //修改值
 
                    foreach (DataRow r in table.Rows) 
                    {
                        string name = Convert.ToString(r["name"]);
                        MessageBox.Show("name值为:"+name);
                    }
                    //table.Rows.RemoveAt(1); //删除值
                    //table.NewRow();         //新建一个Row
 
 
                    //这里的sqlCommandBuilder主要是生成更新语句
                    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                     
                    //这里出现了小错误，没有为表设置主键，所以提示错误
                    //"update T_Persons set name=@name, password=@password"
                    adapter.Update(dataset);//对DataSet的修改都在内存中，没有提交到数据库
                }
            }
        }
 
        private void button8_Click(object sender, EventArgs e)
        {
            T_UserTableAdapter adapter = new T_UserTableAdapter();
            _17DateStr.DataSet1.T_UserDataTable data = adapter.GetData();
            for (int i = 0; i < data.Count; i++) 
            {
                _17DateStr.DataSet1.T_UserRow userRow = data[i];
                MessageBox.Show(userRow.name);
            }
 
        }
    }
 
    public class Promary 
    {
        public int id { get; set; }
        public string name { get; set; } 
    }
}