通用DBHelper类

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

namespace ClassLibrary1
{
    public class DBhelper
    {
        private static SqlConnection conn = null;

        /// <summary>
        /// 返回数据库连接字符串的方法
        /// </summary>
        /// <returns>返回数据库连接字符串</returns>
        private SqlConnection Conn()
        {
            //string connString = ConfigurationManager.AppSettings[""].ToString();
            string connString = ConfigurationManager.ConnectionStrings["sql"].ToString();

            if (conn == null)
            {
                if (connString == null)
                {
                    connString = string.Format(@"server = 192.168.0.100/sa ; uid = HeartFly ; pwd = HeartFly ; database = HeartFly");
                }

                conn = new SqlConnection(connString);
                conn.Open();
            }
            else if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }
            else if (conn.State == System.Data.ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }

            return conn;
        }

        #region 执行SQL，返回受影响的行数
        /// <summary>
        /// 执行SQL，返回受影响的行数
        /// 适用于单条数据的添加、删除、修改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql, params SqlParameter[] p)
        {
            using (SqlCommand comm = new SqlCommand(sql, Conn()))
            {
                if(p != null)
                {
                    comm.Parameters.AddRange(p);
                }

                return comm.ExecuteNonQuery();
            }
        }

        #endregion

        #region 执行SQL，返回受影响的行数
        /// <summary>
        /// 执行SQL，返回受影响的行数
        /// 适用于单条数据的添加、删除、修改
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql)
        {
            using (SqlCommand comm = new SqlCommand(sql, Conn()))
            {
                return comm.ExecuteNonQuery();
            }
        }

        #endregion

        #region 执行查询，返回第一行的第一列
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列，忽略其他列或行
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static int GetScalar(string sql, params SqlParameter[] p)
        {
            using (SqlCommand comm = new SqlCommand(sql,Conn()))
            {

                if (p != null)
                {
                    comm.Parameters.AddRange(p);
                }

                return  Convert.ToInt32(comm.ExecuteScalar());
            }
        }

        #endregion

        #region 执行查询，返回第一行的第一列
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列，忽略其他列或行
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int GetScalar(string sql)
        {
            using (SqlCommand comm = new SqlCommand(sql, Conn()))
            {
                return Convert.ToInt32(comm.ExecuteScalar());
            }
        }

        #endregion

        #region 查询返回泛型集合
        /// <summary>
        /// 返回泛型集合
        /// </summary>
        /// <typeparam name="T">集合的类型</typeparam>
        /// <param name="sql">T-SQL语句</param>
        /// <returns></returns>
        public static IList<T> SelectIn<T>(string sql)
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(sql, Conn()))
            {
                try
                {
                    DataSet ds = new DataSet();

                    sda.Fill(ds);

                    return DataSetToIList<T>(ds.Tables[0]);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    return null;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        #endregion

        #region 将DataSet转换成IList集合

        private static IList<T> DataSetToIList<T>(DataTable dt)
        {
            if (dt == null)
            {
                return null;
            }
            if (dt.Rows.Count < 0)
            {
                return null;
            }

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

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                T _t = Activator.CreateInstance<T>();

                PropertyInfo[] propertys = _t.GetType().GetProperties();

                foreach (PropertyInfo item in propertys)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (item.Name.ToLower().Equals(dt.Columns[j].ColumnName.ToLower()))
                        {
                            if (dt.Rows[i][j] != DBNull.Value)
                            {
                                item.SetValue(_t, dt.Rows[i][j], null);
                            }
                            else
                            {
                                item.SetValue(_t, null, null);
                            }
                            break;
                        }
                    }
                }
                list.Add(_t);
            }
            return list;
        }
        #endregion
#region
        /// <summary>
        /// 将一个数据表转换成一个JSON字符串
        /// </summary>
        /// <param name="source">需要转换的表。</param>
        /// <returns></returns>
        public  string DataTableToJson(DataTable source)
        {
            if (source.Rows.Count == 0)
                return "";
            StringBuilder sb = new StringBuilder("[");
            foreach (DataRow row in source.Rows)
            {
                sb.Append("[");
                for (int i = 0; i < source.Columns.Count; i++)
                {
                    sb.Append('"' + row[i].ToString() + "/",");
                }
                sb.Remove(sb.Length - 1, 1);
                sb.Append("],");
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append("]");
            return sb.ToString();
        }

        /// <summary>
        /// DataTable转换为json，以{"属性":"值"}的形式
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <returns>JSON字符串</returns>
        public  string CreateJsonParameters(DataTable dt)
        {
            StringBuilder JsonString = new StringBuilder();
                  
            if (dt != null && dt.Rows.Count > 0)
            {
                JsonString.Append("{ ");
                JsonString.Append("/"TableInfo/":[ ");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    JsonString.Append("{ ");
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (j < dt.Columns.Count - 1)
                        {
                            JsonString.Append("/"" + dt.Columns[j].ColumnName.ToString() + "/":" + "/"" + dt.Rows[i][j].ToString() + "/",");
                        }
                        else if (j == dt.Columns.Count - 1)
                        {
                            JsonString.Append("/"" + dt.Columns[j].ColumnName.ToString() + "/":" + "/"" + dt.Rows[i][j].ToString() + "/"");
                        }
                    }
                    if (i == dt.Rows.Count - 1)
                    {
                        JsonString.Append("} ");
                    }
                    else
                    {
                        JsonString.Append("}, ");
                    }
                }
                JsonString.Append("]}");
                return JsonString.ToString();
            }
            else
            {
                return null;
            }
        }
#endregion

 

#region 将 list集合转换成json字符串
/// <summary>
        /// 将 list集合转换成json字符串
 ///此方法比较繁琐，我们直接应用System.Web.Script.Serialization命名空间，
 ///然后调用JavaScriptSerializer类的Serialize方法，此方法可以将object类型的数据转换为json类型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public string CreateListToJson<T>(List<T> list)
        {
            StringBuilder sbString = new StringBuilder();
            sbString.Append("[{");
            if (list == null || list.Count <= 0)
            {
                return "";
            }
            else
            {
                foreach (T item in list)
                {
                    PropertyInfo[] process = item.GetType().GetProperties();
                   
                    foreach (PropertyInfo temp in process)
                    {
                        sbString.Append("/"" + temp.Name.ToString() + ":/"" + temp.GetValue(item, null) + "/",");
                    }
                   
                    sbString.Remove(sbString.Length - 1, 1);
                    sbString.Append("},");
                }

                sbString.Remove(sbString.Length - 1, 1);
                sbString.Append("]");
                return sbString.ToString();
            }
           
        }
#endregion

#region

/// <summary>
        /// 将list集合转换为xml
        /// </summary>
        /// <typeparam name="T">任意类型</typeparam>
        /// <param name="list">任意类型的集合</param>
        public static void ListToXml<T>(List<T> list)
        {
            if (list == null || list.Count <= 0)
            {
                return ;
            }
            else
            {
                XmlDocument xdoc = new XmlDocument();

                XmlDeclaration xd = xdoc.CreateXmlDeclaration("1.0", "GBK", null);
                xdoc.AppendChild(xd);

                XmlElement fele = xdoc.CreateElement("ListToXml"); //根节点

                for (int i = 0; i < list.Count; i++)
                {
                    PropertyInfo[] property = list[i].GetType().GetProperties();

                    XmlElement zele = xdoc.CreateElement("Model-"+ (i+1));//集合中的对象

                    for (int j = 0; j < property.Length; j++)
                    {
                        XmlElement sele = xdoc.CreateElement(property[j].Name.ToString());

                        sele.InnerText = property[j].GetValue(list[i], null).ToString();

                        //sele.SetAttribute(property[j].Name, property[j].GetValue(list[i], null).ToString());

                        zele.AppendChild(sele);
                    }

                    fele.AppendChild(zele);
                }

                xdoc.AppendChild(fele);
                xdoc.Save(AppDomain.CurrentDomain.BaseDirectory + "/ListToXml.xml");

            }
        }

#endregion

        #region 同时执行多条sql语句
        /// <summary>
        /// 同时执行多条sql语句
        /// 可同时进行数据的添加、删除、修改等操作
        /// </summary>
        /// <param name="sqllist">sql语句集合</param>
        /// <returns>都执行则返回ture，否则返回false</returns>
        public bool method(IList<string> sqllist)
        {
            using (SqlCommand comm = new SqlCommand())
            {
                comm.Connection = conn;

                SqlTransaction st = null;

                try
                {

                    st = Conn().BeginTransaction();

                    comm.Transaction = st;

                    foreach (string sql in sqllist)
                    {
                        comm.CommandText = sql;
                        comm.ExecuteNonQuery();
                    }
                    st.Commit();
                    return true;
                }
                catch (Exception)
                {
                    st.Rollback();
                    return false;
                }

                finally
                {
                    conn.Close();
                }

            }
        }
        #endregion
    }
}