第二十九讲 : ADO.NET（玩SQL语句 主要是OLEDB实例操作）

主要内容
    1. 执行命令(查，增，删，改)
        实例讲解
        要点记录：
            1. 变量的域的使用技巧
            2. ExecuteScalar在增删改中的应用
            3. 字符串的Format属性使用，使用方法类似于Console.WriteLine();的输出格式
            4. 窗口隐藏，退出时使用Application.exit()退出整个程序
    
    2. 参数化SqlParameter
        参数
        参数对象
        优点：
            安全
            防SQL注入
        两种方式：
            1.使用cmd.Parameters.AddWithValue("@id",值)

            2. SqlParameter类作为专门参数类型

看标题写提玩SQL语句来着，但是SQL语句前面都玩过了，先玩一把大家都不常玩的Access 和 Excel吧，这两个数据读写折磨了3天呢，不管你们看不看，学不学，我先把这个笔记 打好了再说。另外，Excel删除和更新及Access的更新模块功能还没有写，明天到公司补上。还有SQL语句我也会再练习一下，特别是这一节老师讲的使用参数传递的这一块。

Access And Excel操作实例代码贴上来：

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.OleDb;using System.Text.RegularExpressions;using Excel = Microsoft.Office.Interop.Excel;using System.Reflection;//引用才能使用Missing字段namespace WF29{    public partial class Form1 : Form    {        //定义一个连接字符串，sql的很常用，我就整个不常用的Excel2007        static string Dir = "D:\\ZERO";        static string fileName = "db.xlsx";        //郑重说明，HDR参数是标记是否将首行作为标题行，IMEX这个参数，        //网上大多的说法是，0是汇出，1是汇入，2是可读写，但是我被整整折磨了3天。最后确定是0值是可读可写，还是菜啊，咋就不试试呢？SHIT!        string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Dir + "\\" + fileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=0;'";        //Microsoft.Jet.OLEDB.4.0 , Microsoft.ACE.OLEDB.12.0这两个引擎都可以用在access的数据读取上。        string astr = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\onedb.mdb";        //下面这几个变量还没有开始玩，先注释一下。好像更牛逼的样子，可以操作表中的单元格的        //private Excel.Application excelApp = null;        //private Excel.Workbook book = null;        //private Excel.Worksheet sheet = null;        //private Excel.Range range = null;        public Form1()        {            //窗体初始化            InitializeComponent();        }        /// <summary>        /// 闪退        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void button3_Click(object sender, EventArgs e)        {            Application.Exit();        }        /// <summary>        /// 窗体加载        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void Form1_Load(object sender, EventArgs e)        {            RefreshL();        }        /// <summary>        /// 刷新ListBox对象中的数据        /// </summary>        private void RefreshL()        {            listBox1.Items.Clear();            listBox2.Items.Clear();            // 数据查寻字符串            string esql = "select * from [Sheet1$]";            string s = "select * from products";            //为ListBox控件内默认添加些内容进去            using (OleDbConnection ocon = new OleDbConnection(strConn))            {                //建立好连接对象                //接下来创建命令对象                OleDbCommand ocmd = new OleDbCommand(esql, ocon);                //定义命令对象的类型，其实这里是多次一举，只是练手的                ocmd.CommandType = CommandType.Text;                //打开连接对象                ocon.Open();                //打开数据流这个水笼头,加了个参数，意思是当关闭掉水笼头时同时关闭连接对象                OleDbDataReader odr = ocmd.ExecuteReader(CommandBehavior.CloseConnection);                //读取数据                while (odr.Read())                {                    listBox1.Items.Add(odr["excelId"].ToString() + " : " + odr["excelName"].ToString());                }                odr.Close();            }            using (OleDbConnection acon = new OleDbConnection(astr))            {                OleDbCommand acmd = new OleDbCommand(s, acon);                acmd.CommandType = CommandType.Text;                acon.Open();                OleDbDataReader odr = acmd.ExecuteReader(CommandBehavior.CloseConnection);                while (odr.Read())                {                    listBox2.Items.Add(                        odr["ID"].ToString() + "___" +                        odr["bname"].ToString() + "___" +                        odr["price"].ToString() + "___" +                        odr["classid"].ToString()                        );                }                odr.Close();            }        }        /// <summary>        /// 添加数据        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void button1_Click(object sender, EventArgs e)        {            string excelId = tbId.Text.Trim();//获取要加入的项ID            string esql = "insert into [Sheet1$](excelId,excelName) values(" + excelId + "," + tbName.Text.Trim() + ")";            string esql2 = "insert into [Sheet1$](excelId,excelName) values('" + excelId + "','" + tbName.Text.ToString().Trim() + "')";            //string esql3 = "insert into [Sheet1$](excelId,excelName) Values('1234','测试')";            //string esql4 = "Insert into [Sheet1$](excelId,excelName) values('1235','Thomas')";                        if (idExists(excelId, strConn, "[Sheet1$]"))            {                //先获取用户输入匹配的数据                string s = @"^\d*\b";                Regex reg = new Regex(s);                if (reg.IsMatch(tbId.Text.Trim()))                {                    InsertData(esql2,strConn);                }                else                {                    MessageBox.Show("请为ID输入数字！");                }                RefreshL();            }            else {                MessageBox.Show("此ID"+excelId+"已经存在！");            }        }        /// <summary>        /// 插入数据的操作        /// </summary>        /// <param name="esql2"></param>        private void InsertData(string esql2,string DataConn)        {            using (OleDbConnection ocon = new OleDbConnection(DataConn))            {                OleDbCommand ocmd = new OleDbCommand(esql2, ocon);                ocmd.CommandType = CommandType.Text;                ocon.Open();                int ok =(int) ocmd.ExecuteNonQuery();  //一直在报错,"至少一个参数没有被指定值。","操作必须使用一个可更新的查询"                if (ok > 0)                {                    MessageBox.Show("添加成功了！");                }                else                {                    MessageBox.Show("添加失败了！");                }            }        }        /// <summary>        /// 判断一个id是否存在某个表中，这里主要是判断ACCESS和EXCLE类的表        /// 接下要判断这个ID在表中是不是已经存在呢        /// </summary>        /// <param name="id">以表中的字段名为变量的ID</param>        /// <param name="DataConn">连接字符串</param>        /// <param name="TableName">访问的表名</param>        /// <returns>返回布尔值</returns>        private bool idExists(string id, string DataConn, string TableName)        {            bool Exs = false;            if (DataConn == strConn)//通过连接字符串来判断主键ID            {                string excelId = id;                string sql = "select Count(" + excelId + ") from " + TableName + " where excelId='" + excelId+"'";                using (OleDbConnection ocon = new OleDbConnection(DataConn))                {                    OleDbCommand ocmd = new OleDbCommand(sql, ocon);                    ocmd.CommandType = CommandType.Text;                    ocon.Open();                    int ok = (int)ocmd.ExecuteScalar();  //获取首行首列的值                    if (ok > 0)                    {                        Exs = false;                    }                    else                    {                        Exs = true;                    }                }            }            else {                int ID = int.Parse(id);                string sql = "select Count(" + ID + ") from " + TableName + " where ID=" + ID;                using (OleDbConnection ocon = new OleDbConnection(DataConn))                {                    OleDbCommand ocmd = new OleDbCommand(sql, ocon);                    ocmd.CommandType = CommandType.Text;                    ocon.Open();                    int ok =(int) ocmd.ExecuteScalar();  //获取首行首列的值                    if (ok > 0)                    {                        Exs = false;                    }                    else                    {                        Exs = true;                    }                }            }                        return Exs;        }        /// <summary>        /// 将选中项添加到文本框中        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)        {            SyncData();        }        /// <summary>        /// 提取的选中项添加到文本框中的方法，只能满足ListBox1对象        /// 徒劳了，无法共用        /// </summary>        private void SyncData()        {            if (listBox1.SelectedItems.Count > 0)            {                int i = listBox1.Text.IndexOf(" ");                string id = listBox1.Text.Substring(0, i);                string esql = "select * from [Sheet1$] where excelId='" + id+"'";                using (OleDbConnection ocon = new OleDbConnection(strConn))                {                    OleDbCommand ocmd = new OleDbCommand(esql, ocon);                    ocmd.CommandType = CommandType.Text;                    ocon.Open();                    OleDbDataReader odr = ocmd.ExecuteReader(CommandBehavior.CloseConnection);                    while (odr.Read())                    {                        tbId.Text = odr["excelId"].ToString();                        tbName.Text = odr["excelName"].ToString();                    }                    odr.Close();                }            }        }        /// <summary>        /// Listbox2中将选中项数据显示到文本框中        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void listBox2_SelectedIndexChanged(object sender, EventArgs e)        {            SyncData2();        }        /// <summary>        /// 提取的选中项添加到文本框中的方法，只能满足ListBox2对象        /// </summary>        private void SyncData2()        {            if (listBox2.SelectedItems.Count > 0)            {                int i = listBox2.Text.IndexOf("___");                string id = listBox2.Text.Substring(0, i);                string esql = "select * from products where ID=" + id;                using (OleDbConnection ocon = new OleDbConnection(astr))                {                    OleDbCommand ocmd = new OleDbCommand(esql, ocon);                    ocmd.CommandType = CommandType.Text;                    ocon.Open();                    OleDbDataReader odr = ocmd.ExecuteReader(CommandBehavior.CloseConnection);                    while (odr.Read())                    {                        tbeId.Text = odr["ID"].ToString();                        tbeName.Text = odr["bname"].ToString();                        tbePrice.Text = odr["price"].ToString();                        tbeClass.Text = odr["classid"].ToString();                    }                    odr.Close();                }            }        }        /// <summary>        /// 向ListBox2中添加数据        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void btneAdd_Click(object sender, EventArgs e)        {            string ID = tbeId.Text.Trim();            if (idExists(ID, astr, "products"))            {                string sql = "insert into products(ID,bname,price,classid) values(" + Convert.ToInt32(tbeId.Text.Trim()) + ",'" + tbeName.Text.Trim() + "'," + Convert.ToSingle(tbePrice.Text.Trim()) + "," + Convert.ToInt32(tbeClass.Text.Trim()) + ")";                //先获取用户输入匹配的数据                string s = @"^\d*\b";                Regex reg = new Regex(s);                if (reg.IsMatch(tbeId.Text.Trim()))                {                    //调用的公用方法，插入数据                    InsertData(sql, astr);                }                else                {                    MessageBox.Show("请为ID输入数字！");                }                RefreshL();            }            else {                MessageBox.Show("此ID" + ID + "已经存在！");            }        }        /// <summary>        /// Excel 表中删除选中项        /// 好似Excel不支持删除数据哦        /// 另想办法        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void button2_Click(object sender, EventArgs e)        {            //if (listBox1.SelectedItems.Count > 0)            //{             //    //如果有选中项则删掉它            //    int i = listBox1.Text.IndexOf(" ");            //    string id = listBox1.Text.Substring(0, i);            //    string esql = "delete from [Sheet1$] where excelId='" + id + "'";            //    using (OleDbConnection ocon = new OleDbConnection(strConn))            //    {            //        OleDbCommand ocmd = new OleDbCommand(esql, ocon);            //        ocmd.CommandType = CommandType.Text;            //        ocon.Open();            //        int ok = ocmd.ExecuteNonQuery();            //        if (ok > 0)            //        {            //            MessageBox.Show("删除数据成功");            //        }            //        else {            //            MessageBox.Show("删除数据失败");            //        }            //    }            //    RefreshL();            //}        }        /// <summary>        /// Access表中删除选中项        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void btneDel_Click(object sender, EventArgs e)        {            int i = listBox2.Text.IndexOf("___");            string id = listBox2.Text.Substring(0, i);            string esql = "delete from products where ID=" + id;            if (listBox2.SelectedItems.Count > 0)            {                using (OleDbConnection ocon = new OleDbConnection(astr))                {                    OleDbCommand ocmd = new OleDbCommand(esql, ocon);                    ocmd.CommandType = CommandType.Text;                    ocon.Open();                    int ok = ocmd.ExecuteNonQuery();                    if (ok > 0)                    {                        MessageBox.Show("删除数据成功");                    }                    else                    {                        MessageBox.Show("删除数据失败");                    }                }            }            RefreshL();        }    }}

结果：前面的ListBox1是Excel操作，后面的是ListBox2是对Access数据的操作