ConnectionInit方法用于初始化数据库连接对象，
只需要修改databasetype参数即可进行适用各个版本的数据库，
ExecuteNonQuery方法用于执行增、删、改操作，返回受影响的行数，
ExecuteTableSQL方法用于执行查询操作，返回datatable格式的数据，
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Dapper;
namespace DataManager
{
public static class DaHelper
{
private static IDbConnection ConnectionObject = null;//连接数据类的对象
private static string ConnectionString = "";//连接的字符串
private static int DataBaseType = 0;//数据库的类型0=sqlserver,1=access,2=oracle,3=mysql
/// <summary>
/// 设置连接的字符串及数据库类型
/// </summary>
/// <param name="str">连接的字符串</param>
/// <param name="_type">数据库类型0=sqlserver,1=access,2=oracle,3=mysql</param>
public static void ConnectionInit(string str, int _type)
{
ConnectionString = str;
DataBaseType = _type;
SetConnection();
}
/// <summary>
/// 设置连接类的对象
/// </summary>
private static void SetConnection()
{
switch (DataBaseType)
{
case 0:
ConnectionObject = new System.Data.SqlClient.SqlConnection(ConnectionString);//连接sqlserver
break;
case 1:
ConnectionObject = new System.Data.OleDb.OleDbConnection(ConnectionString);//连接access
break;
case 2:
ConnectionObject = new System.Data.OracleClient.OracleConnection(ConnectionString);//连接oracle
//处理办法：
//在oracle 安装目录下 找到 Oracle.DataAccess.dll添加引用，然后 using Oracle.DataAccess.Client;
//其他的都不用动，即可。
//连接字符串中 如有 用的是 user=xxx 就改成user id=xxx
//把原来 Using 的System.Data.OracleClient去掉即可
break;
case 3:
ConnectionObject = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);//连接mysql
break;
}
}
/// <summary>
/// 打开数据库连接
/// </summary>
private static void OpenConnection()
{
if (ConnectionObject.State == System.Data.ConnectionState.Closed)
{
ConnectionObject.Open();
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
private static void CloseConnection()
{
if (ConnectionObject.State == System.Data.ConnectionState.Open)
{
ConnectionObject.Close();
}
}
/// <summary>
/// 执行sql并且返回受影响的行数
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <param name="para"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, object _object)
{
lock (ConnectionObject)
{
try
{
OpenConnection();
return ConnectionObject.Execute(sql, _object);
}
catch
{
throw;
}
finally
{
CloseConnection();
}
}
}
/// <summary>
/// 执行查询的sql语句，并且返回datatable结果
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static DataTable ExecuteTableSQL(string sql, object _object)
{
lock (ConnectionObject)
{
try
{
OpenConnection();
IDataReader idr = ConnectionObject.ExecuteReader(sql, _object);
return IDataReaderToDataTable(idr);
}
catch
{
throw;
}
finally
{
CloseConnection();
}
}
}
/// <summary>
/// 把idatareader转换成datatable
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
private static DataTable IDataReaderToDataTable(IDataReader reader)
{
DataTable objDataTable = new DataTable();
int intFieldCount = reader.FieldCount;
for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
{
objDataTable.Columns.Add(reader.GetName(intCounter), typeof(string));
}
objDataTable.BeginLoadData();
object[] objValues = new object[intFieldCount];
while (reader.Read())
{
reader.GetValues(objValues);
objDataTable.LoadDataRow(objValues, true);
}
reader.Close();
objDataTable.EndLoadData();
return objDataTable;
}
初始化例子：
DataManager.DaHelper.ConnectionInit("server=127.0.0.1;uid=root;pwd=;database=TouchDisplay;pooling=false;CharSet=utf8;port=3306;", 3);//连接mysql数据库
增、删、改例子：
/// <summary>
/// 新增排队记录
/// </summary>
/// <param name="CarNo">车牌</param>
/// <param name="QueueNo">排队号码</param>
/// <param name="QueueIndex">排队序号</param>
/// <param name="IcCard">Ic卡号</param>
/// <param name="CargoNo">油品</param>
/// <param name="ComeTime">登记时间</param>
/// <param name="StateNo">状态</param>
/// <returns></returns>
public static int AddQueue(string CarNo, string QueueNo, int QueueIndex,string IcCard, string CargoName,DateTime ComeTime, int StateNo)
{
string sql = "Insert Into Table_Queue (CarNo,QueueNo,QueueIndex,IcCard,CargoName,ComeTime,StateNo) Values(@CarNo,@QueueNo,@QueueIndex,@IcCard,@CargoName,@ComeTime,@StateNo);";
object para = new
{
CarNo = CarNo,
QueueNo = QueueNo,
QueueIndex = QueueIndex,
IcCard=IcCard,
CargoName = CargoName,
ComeTime=ComeTime,
StateNo = StateNo,
};
return DaHelper.ExecuteNonQuery(sql, para);
}
/// <summary>
/// 根据车牌号码删除队列中的车辆
/// </summary>
/// <param name="CarNo"></param>
/// <returns></returns>
public static int RemoveCarDataByCarNo(string CarNo)
{
string sql = "delete from Table_Queue where CarNo=@CarNo and StateNo = 0";
object para = new
{
CarNo = CarNo,
};
return DaHelper.ExecuteNonQuery(sql, para);
}
/// <summary>
/// 根据车牌号更新车辆信息
/// </summary>
/// <param name="CarNo"></param>
/// <param name="QueueIndex"></param>
/// <returns></returns>
public static int UpdateQueueByCarNo(string CarNo, string IcCard, string CargoName)
{
string sql = "update Table_Queue set IcCard=@IcCard ,CargoName=@CargoName where CarNo=@CarNo and StateNo = 0 or CarNo=@CarNo and StateNo = 1";
object para = new
{
CarNo = CarNo,
IcCard = IcCard,
CargoName = CargoName,
};
return DaHelper.ExecuteNonQuery(sql, para);
}
查询例子：
/// <summary>
/// 获取排队列表
/// </summary>
/// <returns></returns>
public static DataTable GetAllQueueing()
{
string sql = "select QueueNo ,CarNo ,CargoName from Table_Queue where StateNo =0 order by QueueIndex";
DataTable dt = DaHelper.ExecuteTableSQL(sql, null);
return dt;
}
the end