自己写的一个SqlHelper,感觉使用起来挺方便的

自己写的一个SqlHelper,感觉使用起来挺方便的

using System;
using System.Data;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using System.IO;
using System.Data.SqlClient;
namespace NdfDeviceNetC
{
    public class SqlHelper
    {
        //数据库连接字符串
        private readonly static string connstr = @"Data Source=127.0.0.1;Initial Catalog=DB;Persist Security Info=True;User ID=sa;Password=123456";

        private static List<bool> IsBusy = new List<bool>();
        private static List<SqlConnection> connList = new List<SqlConnection>();//链接列表,解决打开链接消耗时间问题
        static SqlHelper()
        {
            //打开10个链接
            for (int i = 0; i < 10; i++)
            {
                SqlConnection conn = new SqlConnection(connstr);
                conn.Open();
                connList.Add(conn);
                IsBusy.Add(false);
            }
        }
        /// <summary>
        /// 获得一个可用的链接
        /// </summary>
        /// <returns></returns>
        private static SqlConnection GetConnection()
        {
            int index = IsBusy.IndexOf(false);
            if (index == -1)
            {
                return null;
            }
            IsBusy[index] = true;
            SqlConnection conn = connList[index];
            if (conn.State==ConnectionState.Closed)
            {
                //如果链接已经关闭,重新打开
                conn.Open();
            }
            return connList[index];
        }

        /// <summary>
        /// 释放链接
        /// </summary>
        /// <param name="conn"></param>
        private static void FreeConnect(SqlConnection conn)
        {
            int index = connList.IndexOf(conn);
            ConnectionState state = conn.State;
            IsBusy[index] = false;
        }
        #region 组织select命令
        public static string CmdForSelectTable(string tableName, string selctColumns, string whereStr)
        {
            string cmdstr = string.Format("SELECT {1} FROM [{0}] WHERE {2}", tableName, selctColumns, whereStr);
            return cmdstr;
        }
        public static string CmdForSelectTable(string tableName, string selctColumns, string whereStr, int top)
        {
            string cmdstr = string.Format("SELECT TOP {3} {1} FROM [{0}] WHERE {2}", tableName, selctColumns, whereStr, top);
            return cmdstr;
        }
        public static string CmdForSelectTable(string tableName, string selctColumns, string whereStr, string orderby)
        {
            string cmdstr = string.Format("SELECT {1} FROM [{0}] WHERE {2} ORDER BY {3}", tableName, selctColumns, whereStr, orderby);
            return cmdstr;
        }
        public static string CmdForSelectTable(string tableName, string selctColumns, string whereStr, int top,string orderby)
        {
            string topStr = "";
            string orderbyStr = "";
            if (top > 0)
            {
                topStr = "TOP " + top;
            }
            if (!string.IsNullOrEmpty(orderby))
            {
                orderbyStr = " ORDER BY " + orderby;
            }
            string cmdstr = string.Format("SELECT {3} {1} FROM [{0}] WHERE {2}{4}", tableName, selctColumns, whereStr, topStr, orderbyStr);
            return cmdstr;
        }
        public static string CmdForSelectMulitTable(List<string> tableNameList, string selctColumns, string whereStr,int top ,string orderby)
        {
            string topStr = "";
            string orderbyStr = "";
            string tableName = "";  
            foreach (string name in tableNameList)
            {
                tableName += name+" ";
            }
            if (top>0)
            {
                topStr = "TOP " + top;
            }
            if (!string.IsNullOrEmpty(orderby))
            {
                orderbyStr = " ORDER BY "+orderby;
            }
            string cmdstr = string.Format("SELECT {3} {1} FROM {0} WHERE {2}{4}", tableName, selctColumns, whereStr, topStr, orderbyStr);
            return cmdstr;
        }
        #endregion
        #region 组织插入和更新命令
        private static string CmdForInsertTable(string tableName, List<string> valueList)
        {
            string columns = "";
            string values = "";
            foreach (string item in valueList)
            {
                int index = item.IndexOf('=');
                columns += item.Substring(0, index) + ",";
                values += item.Substring(index+1) + ",";
            }
            columns = columns.Trim(',');
            values = values.Trim(',');
            string sqlstr = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2});select @@IDENTITY", tableName, columns, values);
            return sqlstr;
        }
        private static string CmdForUpdateTable(string tableName, List<string> valueList, string whereStr)
        {
            string setStr = "";
            foreach (string item in valueList)
            {              
                setStr += item + ",";
            }
            setStr = setStr.Trim(',');
            string sqlstr = string.Format("UPDATE [{0}] SET {1} where {2}", tableName, setStr, whereStr);
            return sqlstr;
        }
        #endregion

        #region 查询

        /// <summary>
        /// 获得表结构
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataTable GetTableClone(string tableName)
        {
            string cmdStr = "SELECT TOP 0 * FROM [" + tableName+"]";
            SqlConnection conn = GetConnection();//公用            
            SqlCommand cmd = new SqlCommand(cmdStr, conn);
            DataTable table = null;
            try
            {
                SqlDataAdapter ad = new SqlDataAdapter(cmdStr, conn);
                table = new DataTable();
                ad.Fill(table);
            }
            catch
            {
                throw;
            }
            finally
            {
                FreeConnect(conn);
            }

            return table;
        }
        /// <summary>
        /// 根据命令，执行后返回表格
        /// </summary>
        /// <param name="cmdStr"></param>
        /// <returns></returns>
        public static DataTable GetTable(string cmdStr)
        {
            SqlConnection conn = GetConnection();//公用            
            SqlCommand cmd = new SqlCommand(cmdStr, conn);
            DataTable table = null;
            try
            {
                SqlDataAdapter ad = new SqlDataAdapter(cmdStr, conn);
                table = new DataTable();
                ad.Fill(table);
            }
            catch
            {
                throw;
            }
            finally
            {
                FreeConnect(conn);
            }
            return table;

        }
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="selctColumns"></param>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public static DataTable GetTable(string tableName, string selctColumns, string whereStr)
        {
            string cmdStr = string.Format("SELECT {1} FROM [{0}]", tableName, selctColumns);
            if (!string.IsNullOrEmpty(whereStr))
            {
                cmdStr += "  WHERE " + whereStr;
            }
          
            SqlConnection conn = GetConnection();//公用            
            SqlCommand cmd = new SqlCommand(cmdStr, conn);
            DataTable table = null;
            try
            {
                SqlDataAdapter ad = new SqlDataAdapter(cmdStr, conn);
                table = new DataTable();
                ad.Fill(table);
            }
            catch
            {
                throw;
            }
            finally
            {
                FreeConnect(conn);
            }
            return table;
        }
        /// <summary>
        /// 根据命令，执行后返回一个值
        /// </summary>
        /// <param name="cmdStr"></param>
        /// <returns></returns>
        public string GetValue(string cmdStr)
        {
            return null;
        }

        #endregion

        /// <summary>
        /// 添加一行数据
        /// </summary>
        public static int Add(string tableName, List<string> valueList)
        {
            string cmdStr = CmdForInsertTable(tableName,valueList);
            SqlConnection conn = GetConnection();//公用            
            SqlCommand cmd = new SqlCommand(cmdStr, conn);
            int rowId = 0;
            try
            {
                object o = cmd.ExecuteScalar();
                rowId = int.Parse(o.ToString());
            }
            catch
            {
                throw;
            }
            finally
            {
                FreeConnect(conn);
            }
            return rowId;
        }
        /// <summary>
        /// 批量添加数据
        /// </summary>
        public static void AddTable(string tableName,DataTable table)
        {
            
            SqlConnection conn = GetConnection();//公用 
            SqlBulkCopy bulk = new SqlBulkCopy(conn);
            bulk.DestinationTableName = tableName;
            try
            {
                bulk.WriteToServer(table);
            }
            catch
            {
                throw;
            }
            finally
            {
                FreeConnect(conn);
            }
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        public static bool Update(string tableName, List<string> valueList, string whereStr)
        {
            string cmdStr = CmdForUpdateTable(tableName, valueList, whereStr);
            SqlConnection conn = GetConnection();//公用            
            SqlCommand cmd = new SqlCommand(cmdStr, conn);
            bool ok = false;
            try
            {
                cmd.ExecuteNonQuery();
                ok = true;
            }
            catch
            {
                throw;
            }
            finally
            {
                FreeConnect(conn);
            }
            return ok;
        }
        /// <summary>
        /// 删除数据
        /// </summary>
        public static  bool Remove(string tableName, string whereStr)
        {
            string cmdStr =  string.Format("DELETE FROM [{0}] WHERE ({1})", tableName, whereStr);
            SqlConnection conn = GetConnection();//公用            
            SqlCommand cmd = new SqlCommand(cmdStr, conn);
            bool ok = true;
            try
            {
                cmd.ExecuteNonQuery();
                ok = true;
            }
            catch
            {
                throw;
            }
            finally
            {
                FreeConnect(conn);
            }
            return ok;
        }
        /// <summary>
        /// 转化表格中一行数据
        /// </summary>
        public static Dictionary<string, object> ConventRowToModel(DataTable dt,int rowIndex)
        {
            if (dt.Rows.Count==0)
            {
                return null;
            }
            DataRow dr = dt.Rows[rowIndex];
            Dictionary<string, object> list = new Dictionary<string, object>();
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                string key = dt.Columns[i].ColumnName;
                object value = dr[i];
                list.Add(key, value);
            }
            return list;
        }
    }
}

 

查询数据

 

        /// <summary>
        /// 获得所有公司
        /// </summary>
        public static DataTable GetCompanyListAll()
        {
            //表Company
            //列 Id,Name,FullName,Adress,Lng,Lat
            string whereStr = "";
            string Columns = "[Id],[Name],[FullName],[Adress],[Lng],[Lat]";
            DataTable dt = SqlHelper.GetTable(CompanyTable1, Columns, whereStr);

            return dt;
        }
        /// <summary>
        /// 获取公司信息
        /// </summary>
        public static Dictionary<string, object> GetCompanyInfo(int companyId)
        {
            //表CompanyTable
            //列Id,Name,FullName,Adress,Lng,Lat
            string Columns = "[Id],[Name],[FullName],[Adress],[Lng],[Lat]";
            string WhereStr = "Id=" + companyId + "";
            DataTable dt = new DataTable();
            dt = SqlHelper.GetTable(CompanyTable1, Columns, WhereStr);
            Dictionary<string, object> DtnList = SqlHelper.ConventRowToModel(dt, 0);

            return DtnList;
        }

 

插入数据

 

        /// <summary>
        /// 新增加一个公司
        /// </summary>
        public static int AddCompany()
        {
            List<string> valueList = new List<string>();
            valueList.Add("Name='新添加公司'");
            valueList.Add("FullName='新添加公司'");
            int Id = SqlHelper.Add(CompanyTable1, valueList);
            return Id;
        }

 

 

更新数据

 

        /// <summary>
        /// 更新公司信息
        /// </summary>
        public static void UpdateCompany(Dictionary<string, string> companyInfo)
        {
            //表CompanyTable
            //Name='"+companyInfo["Name"]+"'
            //FullName=**FullName**
            //Adress=**CompanyTable**
            //Lng=**Lng**
            //Lat=**Lat**
            //whereStr Id=**CompanyId**
            List<string> valueList = new List<string>();
            valueList.Add("Name='" + companyInfo["Name"] + "'");
            valueList.Add("FullName='" + companyInfo["FullName"] + "'");
            valueList.Add("Adress='" + companyInfo["Adress"] + "'");
            valueList.Add("Lng='" + companyInfo["Lng"] + "'");
            valueList.Add("Lat='" + companyInfo["Lat"] + "'");
            string whereStr = "Id=" + companyInfo["CompanyId"] + "";
            bool ok = SqlHelper.Update(CompanyTable1, valueList, whereStr);

        }

删除

       /// <summary>
        /// 删除一个公司
        /// </summary>
        public static void RemoveCompany(int companyId)
        {
            //表CompanyTable
            //whereStr Id=companyId
            string whereStr = "Id=" + companyId + "";
            bool ok = SqlHelper.Remove(CompanyTable1, whereStr);
        }

多表联合查询

       /// <summary>
        /// 获取设备信息
        /// </summary>
        public static Dictionary<string, object> GetDeviceInfo(int deviceId)
        {
            //表DeviceBaseInfo
            //列 CompanyId,DepartmentId,ProductType,ProductModel,Name,LoadWeight
            //表Company
            //列 FullName
            //表Department
            //列 Name
            //表DeviceState
            //列 Lng,Lat

            List<string> tableNameList = new List<string>();
            tableNameList.Add("DeviceBaseInfo");
            tableNameList.Add("LEFT JOIN DeviceState ON DeviceBaseInfo.Id=DeviceState.DeviceId");
            tableNameList.Add("LEFT JOIN Company ON DeviceBaseInfo.CompanyId=Company.Id");
            tableNameList.Add("LEFT JOIN Department ON DeviceBaseInfo.DepartmentId=Department.Id");
            string selctColumns = "DeviceBaseInfo.Id,DeviceBaseInfo.CompanyId,DeviceBaseInfo.DepartmentId,Company.FullName AS CompanyName,Department.Name AS DepartmentName,ProductType,ProductModel,DeviceBaseInfo.Name,LoadWeight,DeviceState.Lng,DeviceState.Lat";
            string whereStr = "DeviceBaseInfo.Id=" + deviceId;
            string cmdStr = SqlHelper.CmdForSelectMulitTable(tableNameList, selctColumns, whereStr, 1, "DeviceBaseInfo.Id");

            DataTable dt = SqlHelper.GetTable(cmdStr);

            Dictionary<string, object> data = SqlHelper.ConventRowToModel(dt, 0);
            return data;
        }