首页 > 代码库 > 自己写的一个SqlHelper,感觉使用起来挺方便的
自己写的一个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{ /// <summary> /// Oracle数据库操作类 /// </summary> 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 =http://www.mamicode.com/ 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 = http://www.mamicode.com/SqlHelper.ConventRowToModel(dt, 0); return data; }
自己写的一个SqlHelper,感觉使用起来挺方便的
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。