自己写的一个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 = ; i < ; 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 == -)

            {

                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 > )

            {

                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>)

            {

                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(, index) + ",";

                values += item.Substring(index+) + ",";

            }

            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 = ;

            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==)

            {

                return null;

            }

            DataRow dr = dt.Rows[rowIndex];

            Dictionary<string, object> list = new Dictionary<string, object>();

            for (int i = ; 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, );

            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, , "DeviceBaseInfo.Id");

            DataTable dt = SqlHelper.GetTable(cmdStr);

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

            return data;

        }

最新文章

  1. grep及正则表达式
  2. 事故记录：php-cgi进程过多导致系统资源耗尽
  3. ngrok 外网访问小能手
  4. vsftpd的主动模式与被动模式
  5. ASP.NET Web API通过ActionFilter来实现缓存
  6. Linux命令行–基本的bash shell命令
  7. 如何使用springmvc的@requestbody 返回json数据
  8. DB2错误代码
  9. SqlServer操作远程数据库
  10. Linux 虚拟网络设备详解之 Bridge 网桥
  11. mongoDB 文档操作_增
  12. 使用Swagger2构建强大的RESTful API文档（2）（二十三）
  13. java 异常 throw
  14. 监督学习(Supervised learning)
  15. 《Python黑帽子：黑客与渗透测试编程之道》 基于GitHub的命令和控制
  16. Unix/Linux系统时间函数API
  17. CCTableView(一)
  18. VS2015解决方案资源管理器空白，不显示内容
  19. SQL操作【整理中...】
  20. Net应用架构设计

热门文章

  1. .net（C#）中结构和类的区别
  2. eclipse中 将java项目转换为web项目
  3. Centos7上搭建OpenvpnServer——pritunl
  4. su- 与su的区别
  5. linux常用的命令
  6. Prince2七大流程之项目准备
  7. 【Java】模板方法模式
  8. Excel与Word套打功能使用技巧及EXCEL数据处理成绩
  9. ElasticSearch5+logstash的logstash-input-jdbc实现mysql数据同步
  10. [CF752E]Santa Claus and Tangerines（二分答案，dp）