代码编织梦想

说明原因 :

因公司项目是很早的老项目,底层用的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 Npgsql
                if (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'
SYSDATECURRENT_TIMESTAMP
ROWNUMROW_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("数据库连接字符串为空!");
            else
                DBType = 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 Npgsql
                if (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;
        }
    }
}

以上是总结的之前遇到的问题,其他未注明,如有错误,还请指正~

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_42064877/article/details/130869007

ibatis.net winform搭建_xxyusi的博客-爱代码爱编程

1、框架  ibatis主要dll介绍 IBatisNet.Common.dll 由DataAccess和DataMapper组成的共享程序集 IBatisNet.Common.Logging.Log4Net.dll Log4Net集成记录器,和Log4Net配合使用 IBatisNet.DataMapper.dl

.net通过odp.net managed连接oracle数据库_风神修罗使的博客-爱代码爱编程

1、 Oralce官网 下载 安装 ODTforVS2015_121025 重启VS 2、项目引用 Oracle.ManagedDataAccess.dll 3、数据库连接字符串: user id=xxxx;passwo

sqlsugar 封装.net core和.net 二个版本_⑧o年崋的博客-爱代码爱编程_sqlsugar封装

自己做项目的时候封装的SqlSugar操作类,备份以后用… 简介: SqlSugar :是一款高性能(达到ADO.NET最高性能水平) SqlSugar :是除EF外拉姆达解析最完善的ORM,多表 、UnionALL、 交叉

.Net项目引入ORM SqlSugar框架及简单使用-爱代码爱编程

SqlSugar的优点就不在这里重复了。 一,Nuget 安装 SqlSugar是.NET版本,SqlSugarCore是.NET CORE版本,根据你的项目选择你的引用dll   二,简单使用 1.建立数据库连接 public SqlSugarClient GesmartDb() { SqlSuga

C# SqlSugar框架的学习使用(六)-- 扩展用法-爱代码爱编程

前言 上一篇《C# SqlSugar框架的学习使用(五)-- 更新和删除数据的详细用法》我们已经把SqlSugar的更新和删除的详细使用方法实现了,这篇将介绍一下SqlSugar的一些高级用法。 代码演示 分页查询 这里我只列举一下单表查询,我们在表tVipPayFlow表中随机插入了50000条数据,如下图 程序中

mysql的数据层基类_使用SqlSugar封装的数据层基类-爱代码爱编程

public class BaseDal where T : class ,new() {#region 属性字段 privateDbContext _context;publicDbContext Context {get { return_context; }set { _context =value; } }privateSqlSugar

vb.net调用oracle存储过程,VB.net 调用带参数存储过程-.NET教程,VB.Net语言-爱代码爱编程

–1:存储过程返回dataset 的例子: c# 代码 :通用类 public static int singlestcd(datetime starttime,datetime endtime,int smtp,string jl,string stdcd,string stcdstr, out dataset ds ) { int ret=

学习sqlsugar,分享给更多人收益_山泽林亦的博客-爱代码爱编程

简单查询 static SugarDbContext sugar = new SugarDbContext();         static void Main(string[] args)         {             var db = sugar.Db;             //查询所有             var

轻松教你sql转access_如何把sql数据库转换成access-爱代码爱编程

SQL数据库转access数据库步骤: 1.      建立access数据库:在access中建立access数据库和表,access字段类型与sql中字段类型的对应关系。 2.          在Access中建表是应注意它的保留关键字:比如Password 如果表中的某个字段使用了关键字,那么会导致一系列的语句错误。比如update