public static List <string> GetFirstRow(EmDbType dbType, string connString, CommandType cmdType, string cmdText,
params DbParameter[] commandParameters)
{
if (string.IsNullOrEmpty(connString))
{
throw new ArgumentNullException("connString");
}
if (string.IsNullOrEmpty(cmdText))
{
throw new ArgumentNullException("cmdText");
}
try
{
var conn = DBFactory.NewConnection(dbType, connString);
conn.Open();
return(GetFirstRow(conn, dbType, cmdType, cmdText, commandParameters));
}
catch (Exception ex)
{
EventLogger.Log("SqlHelper.GetFirstRow 【" + cmdText + "】", ex);
OpenConnection(dbType, connString); //重新刷新
return(null);
}
}
public static DbConnection NewConnection(EmDbType dbtype, string connectionString)
{
if (String.IsNullOrEmpty(connectionString))
{
throw new ArgumentNullException("connectionString");
}
switch (dbtype)
{
case EmDbType.SqlServer:
return(new SqlConnection(connectionString));
case EmDbType.Oracle:
return(new OracleConnection(connectionString));
case EmDbType.MySql:
return(new MySqlConnection(connectionString));
case EmDbType.Access:
return(new OleDbConnection(connectionString));
default:
throw new ArgumentException("不支持的数据库类型", dbtype.ToString());
}
}
/// <summary>
/// 执行带参数的SQL语句
/// </summary>
/// <param name="dbType"></param>
/// <param name="connString"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static string ExecuteNonQuery(EmDbType dbType, string connString, CommandType cmdType, string cmdText,
params DbParameter[] commandParameters)
{
if (string.IsNullOrEmpty(connString))
{
throw new ArgumentNullException("connString");
}
if (string.IsNullOrEmpty(cmdText))
{
throw new ArgumentNullException("cmdText");
}
try
{
DbConnection conn = DBFactory.NewConnection(dbType, connString);
return(ExecuteNonQuery(conn, dbType, cmdType, cmdText, commandParameters));
}
catch (Exception ex)
{
EventLogger.Log($"SqlServerHelper.ExecuteNonQuery SQL[{cmdText}] 错误.", ex);
OpenConnection(dbType, connString); //重新连接
return(ex.Message);
}
}
/// <summary>
/// SQL语句的参数类型转换
/// </summary>
/// <param name="parameterNames"></param>
/// <param name="parameterValues"></param>
/// <returns></returns>
public static DbParameter[] ChangeToDbParams(EmDbType dbtype, string[] parameterNames, string[] parameterValues)
{
List <DbParameter> parameterItems = new List <DbParameter>();
if ((parameterNames == null) || (parameterValues == null))
{
return(parameterItems.ToArray());
}
for (int i = 0; i < parameterNames.Length; i++)
{
DbParameter dbParam = null;
switch (dbtype)
{
case EmDbType.SqlServer:
dbParam = new SqlParameter(parameterNames[i], parameterValues[i]);
break;
case EmDbType.Oracle:
dbParam = new OracleParameter(parameterNames[i], parameterValues[i]);
break;
case EmDbType.MySql:
dbParam = new MySqlParameter(parameterNames[i], parameterValues[i]);
break;
case EmDbType.Access:
dbParam = new OleDbParameter(parameterNames[i], parameterValues[i]);
break;
}
parameterItems.Add(dbParam);
}
return(parameterItems.ToArray());
}
public static List <string> GetFirstRow(DbConnection conn, EmDbType dbType, CommandType cmdType, string cmdText,
params DbParameter[] commandParameters)
{
if (conn == null)
{
throw new ArgumentNullException("conn");
}
if (string.IsNullOrEmpty(cmdText))
{
throw new ArgumentNullException("cmdText");
}
try
{
List <string> result = new List <string>();
DataSet ds = GetDataSet(conn, dbType, cmdType, cmdText, commandParameters);
if ((ds != null) && (ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
{
DataRow dr = ds.Tables[0].Rows[0];
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
result.Add(dr[i].ToString());
}
}
return(result);
}
catch (Exception ex)
{
EventLogger.Log("SqlHelper.GetFirstRow 【" + cmdText + "】", ex);
OpenConnection(dbType, conn); //重新连接
return(null);
}
}
public static EmDbType MainDbType = EmDbType.None; //主 数据库类型
#endregion
#region 0.1 主数据初始化
/// <summary>
/// 初始化主连接
/// </summary>
/// <param name="dbType">数据库类型</param>
/// <param name="datasource">数据源</param>
/// <param name="database">数据库</param>
/// <param name="userid">用户名</param>
/// <param name="password">密码</param>
/// <returns></returns>
public static bool InitialMainConn(EmDbType dbType, string datasource, string database, string userid, string password)
{
string connnString = DBFactory.BuildConnStr(dbType, datasource, database, userid, password);
MainConnectionString = connnString;
MainDbType = dbType;
MainConn = DBFactory.NewConnection(dbType, connnString);
return(InitialMainConn(dbType, connnString));
}
/// <summary>
/// 初始化
/// </summary>
/// <param name="serverPKID">ID</param>
/// <param name="address">地址：格式 IP地址;登录名;密码;数据库;数据库类型</param>
/// <param name="updateRate"></param>
/// <param name="deviceTagParams"></param>
/// <param name="callback"></param>
private void Initial(Int64 serverPKID, string address, int updateRate,
List <DeviceTagParam> deviceTagParams, DataChangeEventHandler callback)
{
pkid = serverPKID;
string[] connStrings = address.Split('|'); //分隔符
#region 初始化参数
DbType = EmDbType.SqlServer;
ConnectionString = address;
#region 数据库类型及连接语句
if (connStrings.Count() >= 2)
{
ConnectionString = connStrings[1];
string sdbtype = connStrings[0].ToLower();
if (sdbtype == "access")
{
DbType = EmDbType.Access;
}
if (sdbtype == "oracle")
{
DbType = EmDbType.Oracle;
}
if (sdbtype == "mysql")
{
DbType = EmDbType.MySql;
}
}
if (connStrings.Count() >= 5) //分开的数据库连接数据
{
if (!string.IsNullOrEmpty(connStrings[2]) && !string.IsNullOrEmpty(connStrings[3]) &&
!string.IsNullOrEmpty(connStrings[4]))
{
ConnectionString = DBFactory.BuildConnStr(DbType, connStrings[1], connStrings[2], connStrings[3],
connStrings[4]);
}
}
#endregion
MainConn = DBFactory.NewConnection(DbType, ConnectionString); //连接语句
Callback = callback; //设置回调函数
DeviceTags = deviceTagParams; //标签
#endregion
CustomProtocol = (connStrings.Length >= 6) ? connStrings[5] : ""; //自定义协议
ProtocolVariable = (connStrings.Length >= 7) ? connStrings[6] : ""; //自定义协议参数
}
/// <summary>
/// 初始化主连接
/// </summary>
/// <param name="dbType">数据库类型</param>
/// <param name="connnString">连接字符串</param>
/// <returns></returns>
public static bool InitialMainConn(EmDbType dbType, string connnString)
{
if (string.IsNullOrEmpty(connnString))
{
throw new ArgumentNullException("connnString");
}
MainConnectionString = connnString;
MainDbType = dbType;
MainConn = DBFactory.NewConnection(dbType, connnString);
return(RefreshMainSqlConnection());
}
public static DataTable GetDataTable(DbConnection conn, EmDbType dbType, CommandType cmdType, string cmdText,
params DbParameter[] commandParameters)
{
if (conn == null)
{
throw new ArgumentNullException("conn");
}
if (string.IsNullOrEmpty(cmdText))
{
throw new ArgumentNullException("cmdText");
}
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var cmd = DBFactory.NewCommand(dbType, conn);
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (commandParameters != null)
{
foreach (var param in commandParameters)
{
cmd.Parameters.Add(param);
}
}
var da = DBFactory.NewDataAdapter(dbType, cmd);
DataSet ds = new DataSet();
da.Fill(ds); //填充数据
cmd.Parameters.Clear();
conn.Close();
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
else
{
return(null);
}
}
catch (Exception ex)
{
EventLogger.Log("SqlHelper.GetDataSet 【" + cmdText + "】", ex);
OpenConnection(dbType, conn); //重新连接
return(null);
}
}
public static string ExecuteNonQuerys(DbConnection conn, EmDbType dbType, List <string> sqlList)
{
if (conn == null)
{
throw new ArgumentNullException("conn");
}
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var cmd = DBFactory.NewCommand(dbType, conn);
using (var trans = conn.BeginTransaction())
{
cmd.Transaction = trans;
try
{
foreach (string sql in sqlList)
{
if (string.IsNullOrWhiteSpace(sql))
{
continue;
}
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ex)
{
EventLogger.Log("SqlHelper.ExecuteNonQuerys SQL【" + string.Join(";", sqlList.ToArray()) + "】 ", ex);
trans.Rollback(); //回滚
OpenConnection(dbType, conn); //重新连接
return(ex.Message);
}
}
conn.Close();
return("OK");
}
catch (Exception ex)
{
EventLogger.Log("SqlHelper.ExecuteNonQuery 错误.", ex);
OpenConnection(dbType, conn); //重新连接
return(ex.Message);
}
}
/// <summary>
/// 打开数据库连接
/// </summary>
/// <param name="dbType">数据库类型</param>
/// <param name="conn">连接语句</param>
/// <returns></returns>
public static bool OpenConnection(EmDbType dbType, DbConnection conn)
{
try
{
conn.Open();
return(true);
}
catch (Exception ex)
{
string error = $"打开链接失败，连接语句 [{conn.ConnectionString}].";
EventLogger.Log(error, ex);
return(false);
}
}
/// <summary>
/// 打开数据库连接
/// </summary>
/// <param name="dbType"></param>
/// <param name="connString">数据库连接语句</param>
/// <returns></returns>
public static bool OpenConnection(EmDbType dbType, string connString)
{
try
{
DbConnection conn = DBFactory.NewConnection(dbType, connString);
conn.Open();
return(true);
}
catch (Exception ex)
{
string error = $"打开链接失败，连接语句 [{connString}].";
EventLogger.Log(error, ex);
return(false);
}
}
/// <summary>
/// 初始化SQL语句，初始化一次
/// </summary>
public static void RefreshDBSetting()
{
if ((ConfigurationManager.ConnectionStrings == null) ||
(ConfigurationManager.ConnectionStrings[_dbConnectName] == null))
{
return;
}
string connectString = ConfigurationManager.ConnectionStrings[_dbConnectName].ConnectionString; //获取
#region 解析连接语句
string[] _Attrib = connectString.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
Dictionary <string, string> _AttribList = new Dictionary <string, string>();
for (int i = 0; i < _Attrib.Length; i++)
{
string[] _AttribValue = _Attrib[i].Split('=');
_AttribList.Add(_AttribValue[0].ToLower(), _AttribValue[1]);
}
#endregion
if (ConfigurationManager.ConnectionStrings[_dbConnectName].ProviderName ==
"Oracle.ManagedDataAccess.Client")
{
_dbType = EmDbType.Oracle;
_dbDefaultSchema = _AttribList["user id"];
}
else if (ConfigurationManager.ConnectionStrings[_dbConnectName].ProviderName == "MySql.Data.MySqlClient")
{
_dbType = EmDbType.MySql;
}
else if (ConfigurationManager.ConnectionStrings[_dbConnectName].ProviderName == "System.Data.SqlClient")
{
_dbType = EmDbType.SqlServer;
_dbDefaultSchema = "dbo";
}
else if (ConfigurationManager.ConnectionStrings[_dbConnectName].ProviderName ==
"JetEntityFrameworkProvider")
{
_dbType = EmDbType.Access;
}
SqlHelper.InitialMainConn(_dbType, connectString);
}
public static string BuildConnStr(EmDbType dbtype, string datasource, string database, string userid, string password)
{
string connStr = "";
switch (dbtype)
{
case EmDbType.SqlServer:
connStr =
String.Format(
"Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}",
datasource, database, userid, password);
break;
case EmDbType.Oracle:
connStr =
String.Format("DATA SOURCE={0}:1521/{1};PASSWORD={3};PERSIST SECURITY INFO=True;USER ID={2}",
datasource, database, userid, password); //Oracle.ManagedDataAccess
//connStr = string.Format("Host={0};Port=1521;User ID={2};Password={3}; Service Name={1}",
// datasource, database, userid, password); //DDTek.Oracle
break;
case EmDbType.MySql:
connStr = String.Format("server={0};user id={2};password={3};database={1}",
datasource, database, userid, password);
break;
case EmDbType.Access:
if (String.IsNullOrEmpty(userid))
{
connStr = String.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source={0};Persist Security Info=False;",
datasource);
}
else
{
connStr = String.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source={0};Persist Security Info=True;" +
"user id={1};Jet OleDb:DataBase Password={2};",
datasource, userid, password);
}
break;
default: //其他
connStr = String.Format("data source={0};database={1};user id={2};password={3}",
datasource, database, userid, password);
break;
}
return(connStr);
}
public static string ExecuteNonQuerys(EmDbType dbType, string connString, List <string> sqlList)
{
if (string.IsNullOrEmpty(connString))
{
throw new ArgumentNullException("connString");
}
try
{
DbConnection conn = DBFactory.NewConnection(dbType, connString);
return(ExecuteNonQuerys(conn, dbType, sqlList));
}
catch (Exception ex)
{
EventLogger.Log("SqlHelper.ExecuteNonQuerys 错误.", ex);
OpenConnection(dbType, connString); //重新连接
return(ex.Message);
}
}
public static string GetFirstValue(DbConnection conn, EmDbType dbType, CommandType cmdType, string cmdText,
params DbParameter[] commandParameters)
{
if (conn == null)
{
throw new ArgumentNullException("conn");
}
if (string.IsNullOrEmpty(cmdText))
{
throw new ArgumentNullException("cmdText");
}
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var cmd = DBFactory.NewCommand(dbType, conn);
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (commandParameters != null)
{
foreach (var param in commandParameters)
{
cmd.Parameters.Add(param);
}
}
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
conn.Close();
return(val?.ToString());
}
catch (Exception ex)
{
EventLogger.Log("SqlHelper.GetFirstValue 【" + cmdText + "】", ex);
OpenConnection(dbType, conn); //重新连接
return(null);
}
}
/// <summary>
/// 执行带参数的SQL语句
/// </summary>
/// <param name="conn"></param>
/// <param name="dbType"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="commandParameters"></param>
/// <returns>OK；错误信息</returns>
public static string ExecuteNonQuery(DbConnection conn, EmDbType dbType, CommandType cmdType, string cmdText,
params DbParameter[] commandParameters)
{
if (conn == null)
{
throw new ArgumentNullException("conn");
}
if (string.IsNullOrEmpty(cmdText))
{
throw new ArgumentNullException("cmdText");
}
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var cmd = DBFactory.NewCommand(dbType, conn);
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (commandParameters != null)
{
foreach (var param in commandParameters)
{
cmd.Parameters.Add(param);
}
}
cmd.ExecuteNonQuery(); //执行
cmd.Parameters.Clear();
conn.Close();
return("OK");
}
catch (Exception ex)
{
EventLogger.Log($"SqlServerHelper.ExecuteNonQuery SQL[{cmdText}] 错误.", ex);
OpenConnection(dbType, conn); //重新连接
return(ex.Message);
}
}
public static DbDataReader ExecuterReader(EmDbType dbType, string connString, CommandType cmdType, string cmdText,
params DbParameter[] commandParameters)
{
if (string.IsNullOrEmpty(connString))
{
throw new ArgumentNullException("connString");
}
if (string.IsNullOrEmpty(cmdText))
{
throw new ArgumentNullException("cmdText");
}
try
{
var conn = DBFactory.NewConnection(dbType, connString);
conn.Open();
var cmd = DBFactory.NewCommand(dbType, conn);
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (commandParameters != null)
{
foreach (var param in commandParameters)
{
cmd.Parameters.Add(param);
}
}
var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return(dr);
}
catch (Exception ex)
{
EventLogger.Log("SqlHelper.ExecuterReader 【" + cmdText + "】", ex);
OpenConnection(dbType, connString); //重新刷新
return(null);
}
}
public static DbDataAdapter NewDataAdapter(EmDbType dbtype, DbCommand cmd)
{
switch (dbtype)
{
case EmDbType.SqlServer:
if (cmd is SqlCommand)
{
return(new SqlDataAdapter((SqlCommand)cmd));
}
throw new ArgumentException("数据库Command的类型不一致", "cmd");
case EmDbType.Oracle:
if (cmd is OracleCommand)
{
return(new OracleDataAdapter((OracleCommand)cmd));
}
throw new ArgumentException("数据库Command的类型不一致", "cmd");
case EmDbType.MySql:
if (cmd is MySqlCommand)
{
return(new MySqlDataAdapter((MySqlCommand)cmd));
}
throw new ArgumentException("数据库Command的类型不一致", "cmd");
case EmDbType.Access:
if (cmd is OleDbCommand)
{
return(new OleDbDataAdapter((OleDbCommand)cmd));
}
throw new ArgumentException("数据库Command的类型不一致", "cmd");
default:
throw new ArgumentException("不支持的数据库类型", "dbtype");
}
}
public static DbCommand NewCommand(EmDbType dbtype, DbConnection conn)
{
switch (dbtype)
{
case EmDbType.SqlServer:
if (conn is SqlConnection)
{
return(((SqlConnection)conn).CreateCommand());
}
throw new ArgumentException("数据库连接的类型不一致", "conn");
case EmDbType.Oracle:
if (conn is OracleConnection)
{
return(((OracleConnection)conn).CreateCommand());
}
throw new ArgumentException("数据库连接的类型不一致", "conn");
case EmDbType.MySql:
if (conn is MySqlConnection)
{
return(((MySqlConnection)conn).CreateCommand());
}
throw new ArgumentException("数据库连接的类型不一致", "conn");
case EmDbType.Access:
if (conn is OleDbConnection)
{
return(((OleDbConnection)conn).CreateCommand());
}
throw new ArgumentException("数据库连接的类型不一致", "conn");
default:
throw new ArgumentException("不支持的数据库类型", "dbtype");
}
}
public static string ExecuteNonQuerys(DbConnection conn, EmDbType dbType, params string[] sqls)
{
List <string> sqlList = new List <string>(sqls);
return(ExecuteNonQuerys(conn, dbType, sqlList));
}
