第19课-数据库开发及ado.net ADO.NET--SQLDataReader使用.SqlProFiler演示.ADoNET连接池,参数化查询.SQLHelper .通过App.Config文件获得连接字符串

第19课-数据库开发及ado.net

ADO.NET--SQLDataReader使用.SqlProFiler演示.ADoNET连接池,参数化查询.SQLHelper .通过App.Config文件获得连接字符串

01对TblStudent表进行增删改查

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

 

namespace _01对TblStudent表进行增删改查

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

 

        private void Form1_Load(object sender, EventArgs e)

        {

            ///把班级表中的数据加载到下拉菜单中

            LoadClassInfoToCombox();

            //tSId, tSName, tSGender, tSAddress, tSPhone, tSAge, tSBirthday, tSCardId, tSClassId

            //窗体加载的时候加载学生数据到DataGridView

            LoadStudents();

 

        }

 

        private void LoadStudents()

        {

            List<Student > list = new List<Student>();

            string constr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;Integrated Security=True;";

           

            using (SqlConnection  con=new SqlConnection (constr))

            {

                //把班级中的数据查询出来

                string sql = "select * from TblStudent ";

                using (SqlCommand cmd=new SqlCommand (sql,con))

                {

                    con.Open();

                    //应该调用cmd.ExecuteReader()方法，因为返回的是多行多列

                    using (SqlDataReader reader=cmd.ExecuteReader() )

                    {

                        //在使用DataReader的时候则必须保证SqlConnection是打开状态

                        if (reader .HasRows )

                        {

                            //循环获取数据

                            while (reader .Read ())

                            {

                                #region 构建一个数据源List集合

                                Student  model1 = new Student();

                                model1.TsId  = reader .GetInt32(0);

                                model1.TsName =reader .IsDBNull (1)?null:reader.GetString (1);

                                model1.Gender =reader .GetString (2);

                                model1.Address =reader.IsDBNull (3)?null:reader.GetString (3);

                                model1 .Phone =reader .IsDBNull (4)?null:reader .GetString (4);

                                model1 .Age =reader.IsDBNull (5)?null:(int?) reader.GetInt32 (5);

                                model1 .Birthday =reader.IsDBNull (6)?null: (DateTime ?)reader.GetDateTime (6);

                                model1.TsCardId =reader .IsDBNull (7)?null :reader .GetString (7);

                                model1 .ClassId =reader.GetInt32 (8);

                                list.Add(model1);

                                #endregion

                            }

                        }                   

                    }

 

                }

 

 

            }

            dataGridView1 .DataSource =list;

        }

        private void LoadClassInfoToCombox()

        {

            List<ClassModel> list = new List<ClassModel>();

            string constr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;Integrated Security=True;";

           

            using (SqlConnection  con=new SqlConnection (constr))

            {

                //把班级中的数据查询出来

                string sql = "select tclassName,tclassId from TblClass ";

                using (SqlCommand cmd=new SqlCommand (sql,con))

                {

                    con.Open();

                    //应该调用cmd.ExecuteReader()方法，因为返回的是多行多列

                    using (SqlDataReader reader=cmd.ExecuteReader() )

                    {

                        //在使用DataReader的时候则必须保证SqlConnection是打开状态

                        if (reader .HasRows )

                        {

                            //循环获取数据

                            while (reader .Read ())

                            {

                                #region 通过Items.Add()方式向ComboBox中添加数据

                                //int clsId = reader.GetInt32(1);

                                //string clsName = reader.GetString(0);

                                //ClassModel model = new ClassModel();

                                //model.ClassId = clsId;

                                //model.ClassName = clsName;

                                //cboCalssId.Items.Add(model);

                                #endregion

 

                                #region 构建一个数据源List集合

                                int clsId1 = reader.GetInt32(1);

                                string clsName1 = reader.GetString(0);

                                ClassModel model1 = new ClassModel();

                                model1.ClassId = clsId1;

                                model1.ClassName = clsName1;

                                list.Add(model1);

                                #endregion

 

 

 

                            }

                        }                   

                    }

                }

            }

            //设置Combox的数据源

            //一般在做数据绑定的时候，指定的名称必须是属性的名称，不能是字段。

            cboCalssId.DisplayMember = "ClassName";

            cboCalssId.ValueMember = "ClassId";

            cboCalssId.DataSource = list;

 

            //绑定编辑框里面的下拉菜单

            cboEditClassId.DisplayMember = "ClassName";

            cboEditClassId.ValueMember = "ClassId";

            cboEditClassId.DataSource = list;

       

        }

        /// <summary>

        /// 添加

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void button1_Click(object sender, EventArgs e)

        {

            ///采集用户的输入

            string name=txtName .Text.Trim ().Length ==0?null:txtName .Text .Trim ();

            string gender=cboGender .Text .Trim ();

            string address=txtAddress .Text .Trim ().Length ==0?null :txtAddress .Text .Trim ();

            string phone = txtPhone.Text.Trim().Length == 0 ? null : txtPhone.Text.Trim();

            int? age = txtAge.Text.Trim().Length == 0 ? null : (int?)int.Parse(txtAge .Text .Trim ());

            DateTime birthday=dateTimePicker1.Value;

            string cardid=txtCardId .Text .Trim ();

            int calssId =Convert .ToInt32(cboCalssId.SelectedValue) ;

 

            string constr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;Integrated Security=True;";

            string sql = string.Format("insert into TblStudent(tSName,tSGender,tSAddress, tSPhone, tSAge, tSBirthday, tSCardId, tSClassId) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7})"

                , name, gender, address, phone, age==null?"null":age.ToString (), birthday, cardid, calssId);

           

            int r = 0;

            using (SqlConnection con=new SqlConnection (constr) )

            {

                using (SqlCommand cmd =new SqlCommand (sql,con))

                {

                    con.Open();

                    r = cmd.ExecuteNonQuery();

                    con.Close();

                }

            }

            MessageBox.Show("成功添加"+r+"行数据");

        }

        /// <summary>

        /// 当通过cbo.Items.Add()方式向combobox中添加数据时，获取Id的方式。

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void button4_Click(object sender, EventArgs e)

        {

            if (cboCalssId.SelectedItem !=null )

            {

                ClassModel model = cboCalssId.SelectedItem as ClassModel;

                if (model !=null)

                {

                    MessageBox.Show(model .ClassName +model .ClassId );

                }

            }

        }

        /// <summary>

        /// 获取通过数据源绑定的ComboBox的选中项名称跟ID

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void button5_Click(object sender, EventArgs e)

        {

            MessageBox.Show(cboCalssId.Text +" "+cboCalssId .SelectedValue );

        }

 

        private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)

        {

            //获取当前选中行

            //e.RowIndex

            DataGridViewRow row=dataGridView1.Rows [e.RowIndex ];

            //row.Cells[0];

            txtEditId.Text = row.Cells[0].Value.ToString();

            txtEditName.Text =row.Cells[1].Value == null? string.Empty : row.Cells[1].Value.ToString();

 

            if (row.Cells[2].Value.ToString() == "男")

            {

                cboEidtGender.SelectedIndex = 0;

            }

            else

            {

                cboEidtGender.SelectedIndex = 1;

            }

 

            txtEditAddress.Text = row.Cells[3].Value == null ? string.Empty : row.Cells[3].Value.ToString();

            txtEditPhone.Text = row.Cells[4].Value == null ? string.Empty : row.Cells[4].Value.ToString();

            txtEditAge.Text = row.Cells[5].Value == null ? string.Empty : row.Cells[5].Value.ToString();

            if (row.Cells[6].Value!=null)

            {

                dateTimePicker2.Value = DateTime.Parse(row.Cells[6].Value.ToString());

            }

            txtEditCardId.Text = row.Cells[7].Value == null ? string.Empty : row.Cells[7].Value.ToString();

            //设置下来菜单的SelectedValue为某值，则下拉菜单会自动选中这个值。

            //在数据源绑定的时候，ValueMemeber是Int类型，所以这里设置SelectedValue的时候也必须使用Int类型；

            cboEditClassId.SelectedValue =int.Parse ( row.Cells[8].Value.ToString());

 

        }

 

        private void button3_Click(object sender, EventArgs e)

        {

            //SelectedRows .Count >0表示有选中行

            if (dataGridView1.SelectedRows .Count >0)

            {

                //根据ID来删除某条记录

                //1.获取当前选中行的ID

                string id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();

                //根据Id删除当前选中记录

                string sql =string.Format ( "delete from TblStudent where tsid={0}",id );

                string constr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;Integrated Security=True;";

                using (SqlConnection  con=new SqlConnection (constr))

                {

                    using (SqlCommand cmd=new SqlCommand (sql,con))

                    {

                        con.Open();

                        cmd.ExecuteNonQuery();

                    }

                }

                LoadStudents();

            }

        }

       

    }

}

02Command对象StatementComplete事件

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Text;

 

namespace _02Command对象StatementComplete事件

{

    class Program

    {

        static void Main(string[] args)

        {

            string constr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;Integrated Security=True";

            string sql = "insert into TblClass values('AAAA','BBBBB');delete from TblClass where tclassId>20;update TblClass set tclassName='xxxx' where tclassId=16;";

            using (SqlConnection con=new SqlConnection (constr))

            {

                using (SqlCommand cmd=new SqlCommand (sql,con))

                {

                    cmd.StatementCompleted += new System.Data.StatementCompletedEventHandler(cmd_StatementCompleted);

                    con.Open();

                    int r = cmd.ExecuteNonQuery();

                    Console.WriteLine(r);

                }

               

            }

            Console.WriteLine("OK");

            Console.ReadKey();

        }

 

        private static void cmd_StatementCompleted(object sender, System.Data.StatementCompletedEventArgs e)

        {

            Console.WriteLine("本次操作影响了：{0}",e.RecordCount);

        }

    }

}

SQLDataReader使用

1、 DataReader使用完毕需要Close()与Dispose()

2、 DataReader使用时，必须保证连接对象（Connection）是打开状态

3、 DataReader使用时，必须独享一个连接对象（Connection）。

4、 返回reader数据存在 数据库服务器缓存

5、 Reader 只读、只进（每次读取一条释放一条，所以只能向前）

6、 Reader.IsDbNull(index)判断当前列是否为空

7、 如果返回多个结果集则用Reader.NextResult()方法。通过do-while循环。

登录

       private void button1_Click(object sender, EventArgs e)

        {

            //连接数据库判断用户登录是否成功

            string constr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;Integrated Security=True";

            string sql = string.Format("select count(*) from users where loginId='{0}' and loginPwd='{1}'",textBox1.Text .Trim (),textBox2 .Text .Trim ());

            int r = 0;

            using (SqlConnection con=new SqlConnection (constr))

            {

                using (SqlCommand cmd=new SqlCommand (sql,con))

                {

                    con.Open();

                    r = Convert .ToInt32 ( cmd.ExecuteScalar ());

                }

            }

            if (r > 0)

            {

                MessageBox.Show("登录成功！");

            }

            else

            {

                MessageBox.Show("登录失败！");

            }

        }

通过SQL Profiler 事件探查器

在工具→SQL Server Profiler 检测SQL的每一步的执行

通ADO.NET连接池 [默认启用连接池]

由于每次正常连接数据库都至少执行3个操作

1. 1.       登录数据库服务器
2. 2.       执行操作
3. 3.       注销用户
4.      #region 默认情况下，ADO.NET 启用了连接池(300多毫秒)
5.             //string constr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;user=sa;Password=root";
6.             string constr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;user=sa;Password=root";
7.             Stopwatch watch = new Stopwatch();
8.             watch.Start();
9.             for (int i = 0; i < 10000; i++)
10. 10.             {
11. 11.                 //1.当第一次创建一个创建对象调用Open()操作的时候，该连接对象会向数据库发出一个连接请求，并且进行连接。
12. 12.                 //2.当调用该对象的Close()方法时，并没有真的关闭该连接对象，而是将该连接对象放入了“连接池”中。
13. 13.                 //3.当下次再创建一个连接对象的时候，如果该连接对象所使用的连接字符串与池中现有的连接对象
14. 14.                 //所使用的连接字符串【一模一样 注：大小写、空格等什么都必须一样】
15. 15.                 //这时，当调用con.Open()方法时，并不会真正的再次创建一个连接，而是会使用“池”中现有的连接对象。
16. 16.                 //4.如果再次创建的连接对象所使用的连接字符串与池中的对象的连接字符串不一样，此时，则会创建一个新的连接。
17. 17.                 //5.特别注意：只有当调用了连接对象的Close()方法后，当前连接才会放入到池中。
18. 18.                 //池越大，会浪费内存，并不是真正的关闭。
19. 19.                 //自己写一个连接池。类似于数组之类的。
20. 20.                 //mongoDB数据库.Redist 频繁使用数据库。
21. 21.                 //6.Colse()则入池，Dispose()跟“池”无关。
22. 22.                 //如何清空连接池？
23. 23.                 //Connection的静态方法ClearAllPool()、ClearPool()
24. 24.                 //设置 连接池 大小
25. 25.                 //SqlConnectionStringBuilder constrBuilder = new SqlConnectionStringBuilder();
26. 26.                 //constrBuilder.MaxPoolSize = "";
27. 27.                 //
28. 28.                 using (SqlConnection con = new SqlConnection(constr))
29. 29.                 {
30. 30.                     con.Open();
31. 31.                     con.Close();
32. 32.                     con.Dispose();
33. 33.                 }
34. 34.             }
35. 35.             //如何清空连接池？
36. 36.             //SqlConnection.ClearAllPools();
37. 37.             watch.Stop();
38. 38.             Console.WriteLine(watch.ElapsedMilliseconds);
39. 39.             Console.WriteLine("OK");
40. 40.             Console.ReadKey();
41. 41.             #endregion
42. 42.  
43. 43.  
44. 44.             #region ADO.NET 不启用连接池，禁用连接池（;Pooling=false）（30000多毫秒）
45. 45.             //string constr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;user=sa;Password=root";
46. 46.             //string constr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;user=sa;Password=root;Pooling=false";
47. 47.             //Stopwatch watch = new Stopwatch();
48. 48.             //watch.Start();
49. 49.             //for (int i = 0; i < 10000; i++)
50. 50.             //{
51. 51.             //    using (SqlConnection con = new SqlConnection(constr))
52. 52.             //    {
53. 53.             //        con.Open();
54. 54.             //        con.Close();
55. 55.             //    }
56. 56.             //}
57. 57.             //watch.Stop();
58. 58.             //Console.WriteLine(watch.ElapsedMilliseconds);
59. 59.             //Console.WriteLine("OK");
60. 60.             //Console.ReadKey();
61. 61.             #endregion

SQL注入漏洞攻击，带参数的SQL语句

防范注入漏洞攻击的方法：不使用SQL语句拼接，通过参数赋值或存储过程（其实参数赋值后面机制也是调用了存储过程）。

构造恶意的Password：hello’ or 1=1 - -

比如：

select count(*) from users where loginId='magi' or 1=1 --' and loginPwd='magi'

 

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

 

namespace _03登录

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            #region 通过拼接SQL语句实现验证用户登录

            ////连接数据库判断用户登录是否成功

            //string constr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;Integrated Security=True";

            //string sql = string.Format("select count(*) from users where loginId='{0}' and loginPwd='{1}'",textBox1.Text .Trim (),textBox2 .Text .Trim ());

            //int r = 0;

            //using (SqlConnection con=new SqlConnection (constr))

            //{

            //    using (SqlCommand cmd=new SqlCommand (sql,con))

            //    {

            //        con.Open();

            //        r = Convert .ToInt32 ( cmd.ExecuteScalar ());

            //    }

            //}

            //if (r > 0)

            //{

            //    MessageBox.Show("登录成功！");

            //}

            //else

            //{

            //    MessageBox.Show("登录失败！");

            //}

            #endregion

            #region 通过带参数的sql语句实现验证登录

            int r=0;

            string constr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;user=sa;password=root;";

            using (SqlConnection con=new SqlConnection (constr))

            {

                string sql = "select count(*) from users where loginId=@uid and loginPwd=@pwd";

                using (SqlCommand cmd=new SqlCommand (sql,con))

                {

                    //在执行之前要做一些操作

                    //为Command对象添加参数

                    //SqlParameter puid = new SqlParameter("@uid", textBox1.Text.Trim());

                    //cmd.Parameters.Add (puid );

                    //SqlParameter ppwd = new SqlParameter("@pwd", textBox2.Text.Trim());

                    //cmd.Parameters.Add(ppwd);

                    ///更简单方法

                    cmd.Parameters.AddWithValue("@uid", textBox1.Text.Trim());

                    cmd.Parameters.AddWithValue("@pwd", textBox2.Text.Trim());

                    con.Open();

                   r =Convert .ToInt32 ( cmd.ExecuteScalar());

                }

            }

 

            if (r > 0)

            {

                MessageBox.Show("登录成功！");

            }

            else

            {                                      

                MessageBox.Show("登录失败！");

            }

            #endregion                                   

        }

    }

}

 

数据导入导出到文本文件

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.IO;

using System.Text;

using System.Windows.Forms;

 

namespace _05数据导入导出到文本文件

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

 

        private void button1_Click(object sender, EventArgs e)

        {

            //把TblArea中的数据导出到文本文件

            string constr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;user=sa;password=root";

            using (SqlConnection con=new SqlConnection(constr) )

            {

                string sql="select * from TblArea";

                using (SqlCommand cmd=new SqlCommand (sql,con))

                {

                    con.Open();

                    using (SqlDataReader  reader=cmd.ExecuteReader ())

                    {

                        if (reader .HasRows )

                        {

                            //创建一个StreamWriter用来写数据

                            using (StreamWriter sw=new StreamWriter ("Area.txt",false ,Encoding .UTF8))

                            {

                                while (reader.Read())

                                {

                                    int id = reader.GetInt32(0);

                                    string city = reader.GetString(1);

                                    int pid = reader.GetInt32(2);

                                    sw.WriteLine(string.Format ("{0},{1},{2}",id,city ,pid ));

                                }

                                this.Text = "导出成功！！！";

                            }                       

                        }

                        else

                        {

                            this.Text = "没有查询到任何数据！！！";

                        }

                    }               

                }

            }

        }

 

        private void button2_Click(object sender, EventArgs e)

        {

            //导入

            //1.读取文本文件

            using (StreamReader reader=new StreamReader("Area.txt"))

            {

                string sql = "insert into NewArea1 values(@name,@pid)";

                string constr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;user=sa;password=root";

                using (SqlConnection con=new SqlConnection (constr))

                {

                    using (SqlCommand  cmd=new SqlCommand (sql,con))

                    {

                        SqlParameter pname = new SqlParameter("@name",SqlDbType .NVarChar ,50);

                        SqlParameter pid = new SqlParameter("@pid",SqlDbType .Int );

                        cmd.Parameters.Add(pname);

                        cmd.Parameters.Add(pid);

                        con.Open();

                        string line;

                        //循环读取每一行

                        while ((line = reader.ReadLine()) != null)

                        {

                            Console.WriteLine(line);

                            //把每行数据按照“，”分割，提取每一列的信息

                            string[] colums = line.Split(',');

                            cmd.Parameters[0].Value = colums[1];

                            cmd.Parameters[1].Value = colums[2];

                            cmd.ExecuteNonQuery();

                        }

                    }

                }              

            }

            MessageBox.Show("导入完毕！！！");

        }

    }

}

SQLHelper

 

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using System.Data;

 

namespace _06自己封装一个SQLhelper

{

    public  static class SqlHelper

    {

        private static readonly string conStr = "Data Source=127.0.0.1\BLEACHMSSQL;Initial Catalog=itcast2013;user=sa;password=root";

        /// <summary>

        /// insert、delete、update

        /// </summary>

        /// <param name="sql"></param>

        /// <param name="pms"></param>

        /// <returns></returns>

        public static  int ExecuteNonQuery(string sql,params SqlParameter[] pms)

        {

            using (SqlConnection con =new SqlConnection (conStr ))

            {

                using (SqlCommand  cmd=new SqlCommand (sql,con))

                {

                    //可变参数PMS如果用户不传递的时候，是一个长度为0的数组，不是null。

                    if (pms!=null)

                    {

                        cmd.Parameters.AddRange(pms);

                    }

                    con.Open();

                    return cmd.ExecuteNonQuery();

                }

            }

        }

 

        /// <summary>

        /// 返回单个值的

        /// </summary>

        /// <param name="sql"></param>

        /// <param name="pms"></param>

        /// <returns></returns>

        public static object ExecuteScalar(string sql, params SqlParameter[] pms)

        {

            using (SqlConnection con = new SqlConnection(conStr))

            {

                using (SqlCommand cmd = new SqlCommand(sql, con))

                {

                    //可变参数PMS如果用户不传递的时候，是一个长度为0的数组，不是null。

                    if (pms != null)

                    {

                        cmd.Parameters.AddRange(pms);

                    }

                    con.Open();

                    return cmd.ExecuteScalar();

                }

            }

        }

 

        ///select 查询多行多列

        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)

        {

            SqlConnection con = new SqlConnection(conStr);

                using (SqlCommand cmd = new SqlCommand(sql, con))

                {

                    //可变参数PMS如果用户不传递的时候，是一个长度为0的数组，不是null。

                    if (pms != null)

                    {

                        cmd.Parameters.AddRange(pms);

                    }

                    try

                    {

                        con.Open();

                        //注意：在return cmd.ExecuteReader()返回之前，其实Using()已经调用将con.Close();,，则无法直接返回。

                        //参数System .Data.CommandBehavior .CloseConnection表示当外部调用DataReader对象的Close()方法时，

                        //在该Close()方法内部，会自动调用与该DataReader相关联的Connection的Close()方法

                        return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

                    }

                    catch

                    {

                        ///预防因cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);发生异常后，无法调用reader进行关闭。

                        con.Close();//这里的关闭不要写在finally中，因为需要return一个SqlDataRead

                        con.Dispose();

                        throw;

                    }

                }

        }

 

 

        /// <summary>

        /// 封装返回DataTable

        /// </summary>

        /// <returns></returns>

        public static DataTable ExecuteDataTable(string sql,params SqlParameter [] pms)

        {

            DataTable dt = new DataTable();

            using (SqlDataAdapter adapter=new SqlDataAdapter (sql,conStr))

            {

                adapter.Fill(dt);

            }

            return dt;

        }

 

 

 

 

    }

}

数据更新应用

    /// <summary>

        /// 实现更新操作

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void button2_Click(object sender, EventArgs e)

        {

            //tSId, tSName, tSGender, tSAddress, tSPhone, tSAge, tSBirthday, tSCardId, tSClassId

            string sql_update = "update TblStudent set tsname=@name,tsgender=@gender,tsaddress=@address,tsphone=@phone,tsage=@age,tsbirthday=@birthday,tscardId=@cardId,tsclassId=@classid where tsid=@tsid";

            SqlParameter[] pms = new SqlParameter[] {

            new SqlParameter ("@name",SqlDbType .VarChar,50),

            new SqlParameter ("@gender",SqlDbType .Char ,2),

            new SqlParameter ("@address",SqlDbType .VarChar,300),

            new SqlParameter ("@phone",SqlDbType .VarChar,100),

            new SqlParameter ("@age",SqlDbType.Int ),

            new SqlParameter ("@birthday",SqlDbType.DateTime ),

            new SqlParameter ("@cardId",SqlDbType .VarChar,18),

            new SqlParameter ("@classid",SqlDbType.Int ),

            new SqlParameter ("@tsid",SqlDbType.Int ),

            };

 

 

            //当使用带参数的Sql语句时候，如果要向数据库中插入空值，必须使用DBNull.Value不能直接写null

            //DBNull.Value;

            //可为空值的字符：tSName, tSAddress, tSPhone, tSAge, tSBirthday, tSCardId

            pms[0].Value = txtName.Text.Trim().Length == 0 ? DBNull.Value : (object)txtName.Text.Trim();

            pms[1].Value = cboEditGender.Text;

            pms[2].Value = txtEditAddress.Text.Trim().Length == 0 ? DBNull.Value : (object)txtEditAddress.Text.Trim();

            pms[3].Value = txtEditPhone.Text.Trim().Length == 0 ? DBNull.Value : (object)txtEditPhone.Text.Trim();

            pms[4].Value = txtEditAge.Text.Trim().Length == 0 ? DBNull.Value : (object)txtEditAge.Text.Trim();

            pms[5].Value = dateTimePicker2.Value;

            pms[6].Value = txtEditCardId.Text.Trim().Length == 0 ? DBNull.Value : (object)txtEditCardId.Text.Trim();

            pms[7].Value = cboEditClassId.SelectedValue;

            pms[8].Value = txtEditId.Text.Trim();

 

           int r= _06自己封装一个SQLhelper.SqlHelper.ExecuteNonQuery(sql_update,pms);

 

           MessageBox.Show("成功修改了"+r+"行");

        }

通过App.config文件获取连接字符串

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <connectionStrings>

    <add name ="sqlConnStr" connectionString ="Data Source=127.0.0.1BLEACHMSSQL;Initial Catalog=itcast2013;user=sa;password=root"/>

  </connectionStrings>

</configuration>

        //读取配置文件来获取连接字符串

        private static readonly string conStr = ConfigurationManager.ConnectionStrings["sqlConnStr"].ConnectionString ;