菜鸟学习Ado.net笔记一：Ado.net学习之SqlHelper类

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Win32;

namespace SqlHelp
{
    /// <summary>
    /// 定义SqlParameter所需的参数对象
    /// </summary>
    public class Parameter
    {
        /// <summary>
        /// 参数集合构造函数
        /// </summary>
        /// <param name="paramname">参数名称</param>
        /// <param name="value">参数所对应的对象的值</param>
        public Parameter(string paramname, object value)
        {
            this.ParamName = paramname;
            this.Obj = value;
        }
        /// <summary>
        /// 参数名称
        /// </summary>
        public string ParamName
        {
            get;
            set;
        }
        /// <summary>
        /// 参数名称所对应的对象的值
        /// </summary>
        public object Obj
        {
            get;
            set;
        }
    }
    /// <summary>
    /// SqlHelper  ^_^ !
    /// </summary>
    public class SqlHelper
    {
        /// <summary>
        /// 连接字符串字段
        /// </summary>
        private static string connStr;

        /// <summary>
        /// SQL连接字符串属性
        /// </summary>       
        public static string ConnStr
        {
            get { return SqlHelper.connStr; }
            set { SqlHelper.connStr = value; }
        }

        private static SqlParameter[] GetSqlParameterToArr(List<Parameter> listP)
        {
            List<SqlParameter> list = new List<SqlParameter>();
            foreach (var item in listP)
            {
                list.Add(new SqlParameter(item.ParamName, item.Obj));
            }
            return list.ToArray();
        }

        /// <summary>
        /// 执行TSQL 语句并返回受影响的行
        /// </summary>
        /// <param name="sql">需要执行的sql语句</param>
        /// <returns></returns>

        public static int ExecuteNonQuery(string sql)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 执行TSQL 语句并返回受影响的行 
        /// </summary>
        /// <param name="sql">需要执行的sql语句</param>
        /// <param name="paramList">参数的泛型集合</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, List<Parameter> paramList)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列 
        /// </summary>
        /// <param name="sql">需要执行的sql语句</param>
        /// <returns></returns>

        public static object ExecuteScalar(string sql)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        return cmd.ExecuteScalar();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列 
        /// </summary>
        /// <param name="sql">需要执行的sql语句</param>
        /// <param name="paramList">参数的泛型集合</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, List<Parameter> paramList)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));
                        return cmd.ExecuteScalar();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        /// <summary>
        /// 返回已经填充结果的DataSet 
        /// </summary>
        /// <param name="sql">需要执行的sql语句</param>
        /// <returns></returns>

        public static DataSet ExecuteDataSet(string sql)
        {
            try
            {
                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;
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 返回已经填充结果的DataSet 
        /// </summary>
        /// <param name="sql">需要执行的sql语句</param>
        /// <param name="paramList">参数的泛型集合</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string sql, List<Parameter> paramList)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));
                        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                        DataSet dataset = new DataSet();
                        adapter.Fill(dataset);
                        return dataset;
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        /// <summary>
        /// 返回查询结果集所返回的字段值的泛型集合 
        /// </summary>
        /// <param name="sql">需要执行的sql语句</param>
        /// <returns></returns>

        public static List<object> ExecuteReader(string sql)
        {
            List<object> obj = new List<object>();
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    obj.Add(reader.IsDBNull(i) ? "空值" : reader.GetValue(i));
                                }
                            }
                            return obj;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        /// <summary>
        /// 返回查询结果集所返回的字段值的泛型集合 
        /// </summary>
        /// <param name="sql">需要执行的sql语句</param>
        /// <param name="paramList">参数的泛型集合</param>
        /// <returns></returns>
        public static List<object> ExecuteReader(string sql, List<Parameter> paramList)
        {
            List<object> obj = new List<object>();
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    obj.Add(reader.IsDBNull(i) ? "空值" : reader.GetValue(i));
                                }
                            }
                            return obj;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        /// <summary>
        /// 获取SqlServer数据库实例名数组 
        /// </summary>
        /// <returns></returns>
        public static string[] GetInstances()
        {
            RegistryKey reg = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server");
            string[] instances = (string[])reg.GetValue("InstalledInstances", "");
            try
            {
                if (instances.Length > 0)
                {
                    for (int i = 0; i < instances.Length; i++)
                    {
                        if (instances[i] == "MSSQLSERVER")
                        {
                            instances[i] = System.Environment.MachineName;
                        }
                        else
                        {
                            instances[i] = System.Environment.MachineName + @"\" + instances[i];
                        }
                    }
                }
                return instances;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
    }
}

测试：
1、获取实例

窗体拖入ComboBox控件，设置name值为cbx_server

引入SqlHelper

using SqlHelp

窗体load事件加入：

 cbx_server.Items .AddRange ( GetInstances()); 

2、执行带参数查询方法

窗体拖入按钮，name为Bt_Test，并且拖入TextBox控件，name值为txt_Param

引入SqlHelper

using SqlHelp

在按钮点击事件中加入：

        private void Bt_Test_Click(object sender, EventArgs e)
        {
            SqlHelper.ConnStr = @"Data Source=localhost;Initial Catalog=UFsystem;Integrated Security=True";
            Parameter param = new Parameter("@id", txt_Param.Text);
            List<Parameter> list = new List<Parameter>();
            list.Add(param);
            List<object> obj = SqlHelper.ExecuteReader(@"select * from ua_user where cuser_id=@id", list);
            foreach (var item in obj)
            {
                Console.WriteLine(item);
            }
        }

输出：

admin
admin
空值
True
空值
空值
空值
空值

正在学习c#，有什么地方不对或不合适的请指教。