SQLHelper

刚写的SQLHelper,已经验证。使用这个帮助类有个条件，那就是实体类的公开属性必须和数据库的字段对应(忽略大小写)，

否则通过实体类反射数据库的字段将会对应不上，结果你懂的

using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.Reflection;
using System.Configuration;
using System.Text;
namespace DAL
{
    public class SQLHelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string sConn
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["ahap"].ConnectionString;
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="queryColumn">查询的字段</param>
        /// <param name="tableName">查询的表名</param>
        /// <param name="dataTableName">返回的DataTable名称</param>
        /// <param name="strwhere">条件</param>
        /// <param name="orderBy">排序</param>
        /// <returns>DataTable</returns>
        public static DataTable Query(string queryColumn, string tableName, string dataTableName, string strwhere, string orderBy)
        {
            string sql = string.Format("select {0} from {1} where 1=1 {2} {3}", queryColumn, tableName, strwhere, orderBy);
            DataTable table = new DataTable(dataTableName);
            try
            {
                using (SqlConnection con = new SqlConnection(sConn))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
                    adapter.Fill(table);
                    return table;
                }
            }
            catch (Exception ex)
            {

                return null;
            }
        }

        /// <summary>
        /// 添加(通过反射)
        /// </summary>
        /// <param name="obj">要存入的对象</param>
        /// <param name="tableName">对应的表名</param>
        /// <param name="primaryKey">对应表主键(主键默认自增，不让插入数据)</param>
        /// <returns>是否成功</returns>
        public static bool Add(object obj, string tableName, string primaryKey)
        {
            Type t = obj.GetType();
            //返回共有属性
            PropertyInfo[] pi = t.GetProperties();
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("insert into {0} ( ", tableName);
            for (int i = 0; i < pi.Length; i++)
            {
                //主键不插入数据
                if (pi[i].Name == primaryKey)
                    continue;
                sql.Append(pi[i].Name + ",");
            }
            //移除最后的逗号
            sql.Remove(sql.Length - 1, 1);
            sql.Append(") values(");
            int j = 0;
            //参数长度等于共有属性的个数减去主键个数
            SqlParameter[] paras = new SqlParameter[pi.Length - 1];
            for (int i = 0; i < pi.Length; i++)
            {
                //主键不插入数据
                if (pi[i].Name == primaryKey)
                    continue;
                sql.Append("@" + pi[i].Name + ",");
                //为参数化赋值
                object value = ConvertToDBNull(t.GetProperty(pi[i].Name).GetValue(obj, null));
                paras[j] = new SqlParameter("@" + pi[i].Name, value);
                j++;

            }
            //移除最后的逗号
            sql.Remove(sql.Length - 1, 1);
            sql.Append(" )");
            SqlConnection con = null;
            try
            {

                using (con = new SqlConnection(sConn))
                {
                    SqlCommand com = new SqlCommand(sql.ToString(), con);
                    com.Parameters.AddRange(paras);
                    con.Open();
                    com.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception ex)
            {

                return false;
            }
        }

        /// <summary>
        /// 更新(通过反射)
        /// </summary>
        /// <param name="obj">要存入的对象</param>
        /// <param name="tableName">对应的表名</param>
        /// <param name="strwhere">更新条件</param>
        ///<param name="primaryKey">对应表主键(主键默认自增，不更新)</param>
        /// <returns>是否成功</returns>
        public static bool Update(object obj, string tableName, string strwhere, string primaryKey)
        {
            Type t = obj.GetType();

            PropertyInfo[] pi = t.GetProperties();
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("update {0} set ", tableName);
            int j = 0;
            SqlParameter[] paras = new SqlParameter[pi.Length - 1];
            for (int i = 0; i < pi.Length; i++)
            {
                //主键不更新
                if (pi[i].Name == primaryKey)
                    continue;
                sql.Append(pi[i].Name + " = @" + pi[i].Name + ",");
                //为参数化赋值
                object value = ConvertToDBNull(t.GetProperty(pi[i].Name).GetValue(obj, null));
                paras[j] = new SqlParameter("@" + pi[i].Name, value);
                j++;
            }
            //移除最后的逗号
            sql.Remove(sql.Length - 1, 1);
            sql.Append(" where 1=1 " + strwhere);
            SqlConnection con = null;
            try
            {
                using (con = new SqlConnection(sConn))
                {
                    SqlCommand com = new SqlCommand(sql.ToString(), con);
                    com.Parameters.AddRange(paras);
                    con.Open();
                    com.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }

        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="tableName">对应表名</param>
        /// <param name="strwhere">条件</param>
        /// <returns>是否成功</returns>
        public static bool Delete(string tableName, string strwhere)
        {
            string sql = "delete " + tableName + " where 1=1 " + strwhere;
            SqlConnection con = null;
            try
            {
                using (con = new SqlConnection(sConn))
                {
                    SqlCommand com = new SqlCommand(sql, con);
                    con.Open();
                    com.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception ex)
            {

                return false;
            }
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="queryColumn">查询字段</param>
        /// <param name="tableName">查询表名</param>
        /// <param name="strwhere">条件</param>
        /// <param name="orderBy">排序</param>
        /// <param name="dataTableName">返回的DataTable名</param>
        /// <param name="pageSize">第几页</param>
        /// <param name="pageIndex">每页几条数据</param>
        /// <param name="primaryKey">该表主键</param>
        /// <returns>DataTable</returns>
        public static DataTable Paging(string queryColumn, string tableName, string strwhere, string orderBy, string dataTableName, int pageSize, int pageIndex, string primaryKey)
        {
            string sql = string.Format(@"
                select top({0}) {1} from {2} where 1=1 and {3}  not in (select top({4}) {5} 
                from {6} where 1=1 {7} {8}) {9} {10} ",
            pageIndex, queryColumn, tableName, primaryKey, (pageSize - 1) * pageIndex, primaryKey,
            tableName, strwhere, orderBy, strwhere, orderBy);
            DataTable table = new DataTable(dataTableName);
            try
            {
                using (SqlConnection con = new SqlConnection(sConn))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
                    adapter.Fill(table);
                    return table;
                }
            }
            catch (Exception ex)
            {

                return null;
            }
        }

        /// <summary>         
        /// DataSetToList         
        /// </summary>          
        /// <typeparam name="T">转换类型</typeparam>         
        /// <param name="dataSet">数据源</param>         
        /// <param name="tableIndex">需要转换表的索引</param>        
        /// /// <returns>泛型集合</returns>
        public static IList<T> DataSetToList<T>(DataSet dataset, int tableIndex)
        {
            //确认参数有效
            if (dataset == null || dataset.Tables.Count <= 0 || tableIndex < 0)
            {
                return null;
            }

            DataTable dt = dataset.Tables[tableIndex];

            IList<T> list = new List<T>();


            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //创建泛型对象
                T _t = Activator.CreateInstance<T>();

                //获取对象所有属性
                PropertyInfo[] propertyInfo = _t.GetType().GetProperties();

                //属性和名称相同时则赋值
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    foreach (PropertyInfo info in propertyInfo)
                    {
                        if (dt.Columns[j].ColumnName.ToUpper().Equals(info.Name.ToUpper()))
                        {
                            if (dt.Rows[i][j] != DBNull.Value)
                            {
                                info.SetValue(_t, dt.Rows[i][j], null);
                            }
                            else
                            {
                                info.SetValue(_t, null, null);
                            }

                            break;
                        }
                    }
                }

                list.Add(_t);
            }

            return list;
        }

        /// <summary>
        /// 参数化类型不能插入空值，如果为null必须转换成DBNull
        /// </summary>
        public static object ConvertToDBNull(object str)
        {
            if (str == null)
            {
                return DBNull.Value;
            }
            else
            {
                return str;
            }
        }
    }
}