自己编写SqlHelper（11）

第一步编写SqlHelper类，在项目根文件右击添加类命名为SqlHelper.cs代码如下：（步骤均在之前相同的项目中）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace ExecuteReader执行查询
{
    class SqlHelper
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
        //封装方法的原则是：把不变的放到方法里，变化的方法参数中
        public static int ExecuteNonQuery(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        public static object ExecuteScalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    return cmd.ExecuteScalar();
                }
            }
        }
        public static DataSet ExecuteDataSet(string sql)
        {

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet dataset = new DataSet();
                    adapter.Fill(dataset);
                    return dataset;
                 }
            }
        }
　　　　　public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    return ds.Tables[0];
                }
            }
         }
    }
}

第二步：在窗体btnSqlHelper，并为其添加Click事件，代码如下：

private void btnSqlHelper_Click(object sender, RoutedEventArgs e)
        {
            int change =SqlHelper.ExecuteNonQuery("insert into T_Student(Name,Age) values('三郎',18)");
            int j = (int)SqlHelper.ExecuteScalar("select count(*) from  T_Student");
            MessageBox.Show(change+","+j);

            DataTable table1 = SqlHelper.ExecuteDataTable("select * from T_Student where Name=@Name or Age>@Age", new SqlParameter("@Name", "战一"), new SqlParameter("Age", 20));
            foreach (DataRow row in table1.Rows)
            {
                string name = (string)row["Name"];
                MessageBox.Show(name);
            }

            DataSet dataset = SqlHelper.ExecuteDataSet("select * from  T_Student where Age>20");
            DataTable table = dataset.Tables[0];
            //DataRowCollection rows = table.Rows;
            //for (int i = 0; i < rows.Count; i++)
            //{
            //    DataRow row = rows[i];
            //    int age = (int)row["Age"];
            //    string name = (string)row["Name"];
            //    MessageBox.Show(name + "," + age);
            // }
            foreach (DataRow row in dataset.Tables[0].Rows)
            {
                int age = (int)row["Age"];
                MessageBox.Show(age.ToString());
            }
        }