C# Dapper 操作Oracle数据库

easyboot

已于 2023-08-30 11:50:56 修改

阅读量2.2k

点赞数 1

文章标签：数据库 c# oracle

于 2023-08-30 11:44:02 首次发布

本文链接：https://blog.csdn.net/easyboot/article/details/132579406

版权

文章介绍了如何在.NET应用中使用NuGet包管理器配置Oracle和MySQL数据库连接，包括使用Dapper进行数据库操作和读取配置文件的示例。内容涉及AppSettingsHelper类、DapperOracleHelper中的DapperFactory和执行SQL查询、插入、更新和删除操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

nuget安装内容

1.配置连接字符串 OracleConnectionString这个可用

{

  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",

  "DB": {
    "OracleConnStr": "Password=123456;User ID=system;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.20.13)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));",
    "OracleConnectionString": "data source=192.168.20.13:1521/XE;password=123456;user id=system;Incr Pool Size=5;Decr Pool Size=2;",
    "OracleConnStr1": "Data Source=LINKASIA;User Id=system;Password=123456",
    "MySQLConnStr": "server=127.0.0.1;port=3306;user=root;password=123456; database=spongeglueing;",
    "SqliteDBName": "mydb.db"
  },
  "SerialPort": {
    "Name": "COM!"
  }
}

2.读取配置文件类


using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.Json;

namespace TireDirection.Helper.Settings
{
    /// <summary>
    /// 读取配置信息
    /// </summary>
    public class AppSettingsHelper
    {
        /// <summary>
        /// 
        /// </summary>
        public static IConfiguration Configuration { get; set; }
        static AppSettingsHelper()
        {
            //ReloadOnChange = true 当appsettings.json被修改时重新加载            
            Configuration = new ConfigurationBuilder()
            .Add(new JsonConfigurationSource { Path = "Config\\appsettings.json", ReloadOnChange = true })
            .Build();
        }
    }
}

3.Dapper数据库操作类

using Dapper;
using Oracle.ManagedDataAccess.Client;
using TireDirection.Helper.Settings;

namespace TireDirection.Helper.DB
{
    public class DapperOracleHelper
    { 
        public class DapperFactory
        { 
            public static readonly string ConnectionStr = AppSettingsHelper.Configuration["DB:OracleConnectionString"] ?? "";
            public static OracleConnection CrateOracleConnection()
            {
                var connection = new OracleConnection(ConnectionStr);
                connection.Open();
                return connection;
            }
        }

        /// <summary>
        /// 执行SQL返回集合
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns></returns>
        public static List<T> Query<T>(string strSql)
        {
            using (var conn = DapperFactory.CrateOracleConnection())
            {
                return conn.Query<T>(strSql, null).ToList();
            }
        } 

        public static List<T> QuerySqlString<T>(string sqlStr,object param)
        {
            List<T> result = new List<T>();
            using (var conn = DapperFactory.CrateOracleConnection())
            {
                //string query = "SELECT  *  FROM sys_user t where username=:id ";
                //var par = new { id = "admin" };
                //var par = JsonConvert.DeserializeObject(param);
                result = conn.Query<T>(sqlStr, param).ToList();

                // conn.Insert(new SysUser());
                // conn.Update(new SysUser());//根据主键更新
                // conn.Delete(new SysUser()); //根据主键删除
            }
            return result;
        }
        public static int ExecuteNonQuery(string sql, params SQLiteParameter[] ps)
        {
            int result = -1;
            using (var conn = DapperFactory.CrateOracleConnection())
            {
                string query = "SELECT  *  FROM sys_user t where username=:id ";
                var par = new { id = "admin" };
                result = conn.Execute(query, par);

               // conn.Insert(new SysUser());
               // conn.Update(new SysUser());//根据主键更新
               // conn.Delete(new SysUser()); //根据主键删除
            }
            return result;
        }
    }
}

4.操作数据实例

            public static List<DBTestModel> Test( )
            {
                List<DBTestModel> result = new List<DBTestModel>();
                string SqlStr = @$"Select ID, Name,Value from LINKASIA ";
                try
                {
                    result = DapperOracleHelper.Query<DBTestModel>(SqlStr);

                    SqlStr = @$"Select ID, Name,Value from LINKASIA where ID=:id";
                    var par =   new { id = "1" };
                    result = DapperOracleHelper.QuerySqlString<DBTestModel>(SqlStr, par);
                }
                catch (Exception ex)
                {
                    Log.Logger.Error($"LinkAsiaDB GetAllSpongeGlueing 异常 {ex.Message}");
                }

                return result;
            }