说明原因 :
因公司项目是很早的老项目,底层用的dbhelper数据库操作类,
因需要支持多个数据库,本次是要求支持postgresql数据库,边学边做,期间也遇到了很多postgresql数据库的问题,如果重新改框架、换底层等等一些来不及、所有就直接改了连接数据库、封装了许多sqlsugar的底层查询方法及更新事务方法、目前支持mysql、postgresql、oracle、sqlserver等
要连接PostgreSQL数据库,需要使用PostgreSQL的.NET数据提供程序,例如Npgsql。可以使用NpgsqlConnection类来建立与PostgreSQL数据库的连接。以下是一个简单的示例:
using Npgsql;
using System.Data;string connectionString = "Server=myServerAddress;Port=myPortNumber;Database=myDataBase;User Id=myUsername;Password=myPassword;";
NpgsqlConnection connection = new NpgsqlConnection(connectionString);
connection.Open();// 使用连接执行查询或更新等操作connection.Close();
但是因为一些原因,还是直接改了底层,下面举例说明一个典型的例子,其中两个Query方法中是查询数据库存储为dataset的方法(未改前),两个入参类型不同:
/// <summary>/// 执行查询语句,返回DataSet/// </summary>/// <param name="SQLString">查询语句</param>/// <returns>DataSet</returns>public static DataSet Query(string SQLString, params OleDbParameter[] cmdParms){using (OleDbConnection connection = new OleDbConnection(connectionString)){OleDbCommand cmd = new OleDbCommand();PrepareCommand(cmd, connection, null, SQLString, cmdParms);using (OleDbDataAdapter da = new OleDbDataAdapter(cmd)){DataSet ds = new DataSet();try{da.Fill(ds, "ds");cmd.Parameters.Clear();}catch (System.Data.OleDb.OleDbException ex){throw new Exception(ex.Message);}return ds;}}}/// <summary>/// 执行查询语句,返回DataSet/// </summary>/// <param name="SQLString">查询语句</param>/// <returns>DataSet</returns>public static DataSet Query(string SQLString, List<OleDbParameter> cmdParms){using (OleDbConnection connection = new OleDbConnection(connectionString)){OleDbCommand cmd = new OleDbCommand();PrepareCommand(cmd, connection, null, SQLString, cmdParms);using (OleDbDataAdapter da = new OleDbDataAdapter(cmd)){DataSet ds = new DataSet();try{da.Fill(ds, "ds");cmd.Parameters.Clear();}catch (System.Data.OleDb.OleDbException ex){throw new Exception(ex.Message);}return ds;}}}
OleDbConnection是不支持连接postgresql数据库,所以就考虑到使用比较主流的框架SqlSugar,其中把OleDbParameter转换为SugarParameter进行查询,代码如下:
#region OleDbParameter转SugarParameter/// <summary>/// OleDbParameter转SugarParameter处理/// </summary>/// <returns></returns>public static (SugarParameter[], string) GetSugarParameterArry(OleDbParameter[] dbParameters, string SQLString){SugarParameter[] SGParameter = new SugarParameter[dbParameters.Length];DateTime? dtime = new DateTime?();for (int i = 0; i < dbParameters.Length; i++){int firstIndex = SQLString.IndexOf("?", i + 1);//查找第几次出现的_的索引位置if (firstIndex > -1){SQLString = SQLString.Remove(firstIndex, 1).Insert(firstIndex, ":" + dbParameters[i].ParameterName); //删除索引位置处的一个字符,再进行该位置的insert}if (dbParameters[i].DbType.ToString() == "DateTime"){if (dbParameters[i].Value != null && dbParameters[i].Value.GetType().Name != "DBNull"){dtime = Convert.ToDateTime(dbParameters[i].Value);SGParameter[i] = new SugarParameter(dbParameters[i].ParameterName, dtime);}else{SGParameter[i] = new SugarParameter(dbParameters[i].ParameterName, dtime, dbParameters[i].DbType);}}else if (dbParameters[i].DbType.ToString() == "Decimal"){SGParameter[i] = new SugarParameter(dbParameters[i].ParameterName, Convert.ToDecimal(dbParameters[i].Value));}else if (dbParameters[i].DbType.ToString() == "AnsiString" && dbParameters[i].Value != null&& dbParameters[i].Value.GetType().Name != "String"){SGParameter[i] = new SugarParameter(dbParameters[i].ParameterName, dbParameters[i].Value.ToString());}else{SGParameter[i] = new SugarParameter(dbParameters[i].ParameterName, ((dbParameters[i].Value == null || Convert.ToString(dbParameters[i].Value) == "") ? DBNull.Value : (object)dbParameters[i].Value).ToString());}}return (SGParameter, SQLString);}/// <summary>/// OleDbParameter转SugarParameter处理/// </summary>/// <returns></returns>public static SugarParameter[] GetSugarParameterList(List<OleDbParameter> dbParameters, ref string SQLString){SugarParameter[] SGParameter = new SugarParameter[dbParameters.Count];DateTime? dtime = new DateTime?();for (int i = 0; i < dbParameters.Count; i++){int firstIndex = SQLString.IndexOf("?", i + 1);//查找第几次出现的_的索引位置if (firstIndex > -1){SQLString = SQLString.Remove(firstIndex, 1).Insert(firstIndex, ":" + dbParameters[i].ParameterName); //删除索引位置处的一个字符,再进行该位置的insert}SGParameter[i] = new SugarParameter(dbParameters[i].ParameterName, dbParameters[i].Value);if (dbParameters[i].DbType.ToString() == "DateTime"){if (dbParameters[i].Value != null && dbParameters[i].Value.GetType().Name != "DBNull"){if (dbParameters[i].Value.ToString() != ""){dtime = Convert.ToDateTime(dbParameters[i].Value);SGParameter[i] = new SugarParameter(dbParameters[i].ParameterName, dtime);}else{SGParameter[i] = new SugarParameter(dbParameters[i].ParameterName, dtime, dbParameters[i].DbType);}}else{SGParameter[i] = new SugarParameter(dbParameters[i].ParameterName, dtime, dbParameters[i].DbType);}}else if (dbParameters[i].DbType.ToString() == "Decimal"){SGParameter[i] = new SugarParameter(dbParameters[i].ParameterName, Convert.ToDecimal(dbParameters[i].Value));}else if (dbParameters[i].DbType.ToString() == "AnsiString" && dbParameters[i].Value != null&& dbParameters[i].Value.GetType().Name != "String"){SGParameter[i] = new SugarParameter(dbParameters[i].ParameterName, dbParameters[i].Value.ToString());}else{SGParameter[i] = new SugarParameter(dbParameters[i].ParameterName, ((dbParameters[i].Value == null || Convert.ToString(dbParameters[i].Value) == "") ? DBNull.Value : (object)dbParameters[i].Value).ToString());}}return SGParameter;}/// <summary>/// 根据数据库类型替换某些内置函数/// </summary>/// <param name="SqlString_Place"></param>/// <returns></returns>public static string ReplaceType(string SqlString_Place){if (!string.IsNullOrEmpty(DbTypeStr)){//数据库providerName:SqlClient MySqlClient SQLite OracleManaged/OracleClient Npgsqlif (DbTypeStr.EndsWith("SqlClient", StringComparison.OrdinalIgnoreCase)){//SqlSugar.DbType.SqlServer;}else if (DbTypeStr.EndsWith("MySqlClient", StringComparison.OrdinalIgnoreCase)){//SqlSugar.DbType.MySql;}else if (DbTypeStr.EndsWith("SQLite", StringComparison.OrdinalIgnoreCase)){//SqlSugar.DbType.Sqlite;}else if (DbTypeStr.EndsWith("Oracle", StringComparison.OrdinalIgnoreCase)){//SqlSugar.DbType.Oracle;SqlString_Place = CaseInsenstiveReplace(SqlString_Place, "decode", "COALESCE");}else if (DbTypeStr.EndsWith("Npgsql", StringComparison.OrdinalIgnoreCase)){//SqlSugar.DbType.PostgreSQL;SqlString_Place = CaseInsenstiveReplace(SqlString_Place, "to_date", "to_timestamp");SqlString_Place = CaseInsenstiveReplace(SqlString_Place, "sysdate", "current_timestamp");SqlString_Place = CaseInsenstiveReplace(SqlString_Place, "sys_guid", "gen_random_uuid");SqlString_Place = CaseInsenstiveReplace(SqlString_Place, "INSTR", "strpos");}}return SqlString_Place;}static string CaseInsenstiveReplace(string originalString, string oldValue, string newValue){Regex regEx = new Regex(oldValue,RegexOptions.IgnoreCase | RegexOptions.Multiline);return regEx.Replace(originalString, newValue);}
下表列出了在Oracle数据库中常用的函数和它们在PostgreSQL数据库中的替代函数:
Oracle函数 | Postgresql替代函数 |
---|---|
NVL(exp1, exp2) | COALESCE(exp1, exp2) |
DECODE(exp, value1, result1, value2, result2, ..., default_result) | 使用CASE 表达式进行等效操作 |
GREATEST(value1, value2, ...) | GREATEST(value1, value2, ...) |
LEAST(value1, value2, ...) | LEAST(value1, value2, ...) |
MONTHS_BETWEEN(date1, date2) | EXTRACT(MONTH FROM age(date2, date1)) |
LAST_DAY(date) | date_trunc('month', date) + interval '1 month - 1 day' |
TRUNC(date [, format]) | date_trunc('format', date) |
ADD_MONTHS(date, num_months) | date + interval 'num_months month' |
SYSDATE | CURRENT_TIMESTAMP |
ROWNUM | ROW_NUMBER() OVER() |
TO_NUMBER(string [, format]) | CAST(string AS numeric) |
TO_DATE(string, format) | TO_DATE(string, format) 或 CAST(string AS date) |
INSTR(string, substring [, start_position [, occurrence]]) | POSITION(substring IN string [, start_position [, occurrence]]) |
SUBSTR(string, start [, length]) | SUBSTRING(string FROM start [FOR length]) |
LENGTH(string) | LENGTH(string) 或 CHAR_LENGTH(string) |
REPLACE(string, search_string, replacement_string) | REPLACE(string, from_string, to_string) |
UPPER(string) | UPPER(string) |
LOWER(string) | LOWER(string) |
ROUND(number [,decimal_places]) | ROUND(number [, decimal_places]) 或 TRUNC(number [, decimal_places]) |
ABS(number) | ABS(number) |
CEIL(number) | CEILING(number) |
FLOOR(number) | FLOOR(number) |
这些函数在Oracle和PostgreSQL之间的语法和行为可能存在差异,因此在编写跨平台应用程序时,请务必查阅有关不同数据库之间的特定功能和语法差异的文档。
以上两个查询方法修改之后为:
/// <summary>/// 执行查询语句,返回DataSet/// </summary>/// <param name="SQLString">查询语句</param>/// <returns>DataSet</returns>public static DataSet Query(string SQLString, params OleDbParameter[] cmdParms){SugarDbContext sugar = new SugarDbContext();var result = GetSugarParameterArry(cmdParms, SQLString);DataSet ds = null;try{using (var db = sugar.GetInstance()){SQLString = ReplaceType(SQLString);ds = db.Ado.GetDataSetAll(result.Item2, result.Item1);}--注意:oracel默认字段是大写,postgresql默认字段是小写}catch (Exception ex){throw new Exception(ex.Message + "\r\nSQLString:" + SQLString);}return ds;}/// <summary>/// 执行查询语句,返回DataSet/// </summary>/// <param name="SQLString">查询语句</param>/// <returns>DataSet</returns>public static DataSet Query(string SQLString, List<OleDbParameter> cmdParms){SugarDbContext sugar = new SugarDbContext();SugarParameter[] arParms = GetSugarParameterList(cmdParms, ref SQLString);DataSet dataSet = null;try{using (var Db = sugar.GetInstance()){SQLString = ReplaceType(SQLString);dataSet = Db.Ado.GetDataSetAll(SQLString, arParms);}--注意:oracel默认字段是大写,postgresql默认字段是小写return dataSet;}catch (Exception ex){throw new Exception(ex.Message + "\r\nSQLString:" + SQLString);}}
SugarDbContext类
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using SqlSugar;namespace DBUtility
{public class SugarDbContext{//获取连接字符串 private static string Connection = DESEncrypt.Decrypt(ConfigurationManager.ConnectionStrings["OleDbConnectionString"].ConnectionString);private static DbType DBType;private static readonly string providerName = ConfigurationManager.AppSettings["DbType"];public SugarDbContext(){}/// <summary>/// SugarDb/// </summary>/// <param name="ConnectionString"></param>/// <returns></returns>public SqlSugarClient GetInstance(string ConnectionString = null){if (!string.IsNullOrEmpty(ConnectionString)){Connection = ConnectionString;}if (string.IsNullOrEmpty(Connection))throw new ArgumentNullException("数据库连接字符串为空!");elseDBType = GetSugarDbType();return new SqlSugarClient(new ConnectionConfig(){ConnectionString = Connection, //必填, 数据库连接字符串DbType = DBType, //必填, 数据库类型IsAutoCloseConnection = true, //默认false, 时候知道关闭数据库连接, 设置为true无需使用using或者Close操作InitKeyType = InitKeyType.SystemTable //默认SystemTable, 字段信息读取, 如:该属性是不是主键,是不是标识列等等信息});}/// <summary>/// 根据链接字符串的providerName决定那种数据库类型/// </summary>/// <param name="setting"></param>/// <returns></returns>private DbType GetSugarDbType(){DBType = SqlSugar.DbType.Oracle;if (!string.IsNullOrEmpty(providerName)){//数据库providerName:SqlClient MySqlClient SQLite OracleManaged/OracleClient Npgsqlif (providerName.EndsWith("SqlClient", StringComparison.OrdinalIgnoreCase)){DBType = SqlSugar.DbType.SqlServer;}else if (providerName.EndsWith("MySqlClient", StringComparison.OrdinalIgnoreCase)){DBType = SqlSugar.DbType.MySql;}else if (providerName.EndsWith("SQLite", StringComparison.OrdinalIgnoreCase)){DBType = SqlSugar.DbType.Sqlite;}else if (providerName.EndsWith("Oracle", StringComparison.OrdinalIgnoreCase)){DBType = SqlSugar.DbType.Oracle;}else if (providerName.EndsWith("Npgsql", StringComparison.OrdinalIgnoreCase)){DBType = SqlSugar.DbType.PostgreSQL;}}return DBType;}}
}
以上是总结的之前遇到的问题,其他未注明,如有错误,还请指正~