自己写的一个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;
}