目錄

一、引入?yún)⒖?/p>

1. ConfigurationManager的調(diào)用前提：

?2. NpgsqlConnection的調(diào)用前提：

?3. OracleConnection的調(diào)用前提：

二、設(shè)置數(shù)據(jù)庫鏈接字串

1. 在App.config中設(shè)定鏈接數(shù)據(jù)庫詳情

2. 獲取數(shù)據(jù)庫鏈接字串

三、調(diào)用

1.調(diào)用Oracle數(shù)據(jù)庫

2.調(diào)用postgre數(shù)據(jù)庫

3.結(jié)合不同數(shù)據(jù)庫聯(lián)合查詢

---

一、引入?yún)⒖?/h2>

1. ConfigurationManager的調(diào)用前提：

方案總管 > 項(xiàng)目的“參考”> 右鍵-加入?yún)⒖?> 在架構(gòu)中勾選System.Configuration > 確定。

代碼需引用：using System.Configuration;

?2. NpgsqlConnection的調(diào)用前提：

點(diǎn)擊工具欄的“工具” > NuGet套件管理員 > 管理方案的NuGet套件（程式若啟動(dòng)了，先終止運(yùn)行） > 搜尋插件名Npgsql > 查看該項(xiàng)目是否安裝。

代碼需引用：using Npgsql;

?3. OracleConnection的調(diào)用前提：

點(diǎn)擊工具欄的“工具” > NuGet套件管理員 > 管理方案的NuGet套件（程式若啟動(dòng)了，先終止運(yùn)行） > 搜尋插件名Oracle.ManagedDataAccess > 查看該項(xiàng)目是否安裝。

代碼需引用：using Oracle.ManagedDataAccess.Client;

二、設(shè)置數(shù)據(jù)庫鏈接字串

1. 在App.config中設(shè)定鏈接數(shù)據(jù)庫詳情

<?xml version="1.0" encoding="utf-8" ?>
<configuration><startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" /></startup><appSettings><add key="BookOracle" value="User Id=Luobogan;Password=12345678;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.XXX.XXX.XXX)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=Book)))"/><add key="BookPostgre" value="Host=10.XXX.XXX.XXX;Port=5432;Database=Book;Username=Luobogan;Password=12345678;"/></appSettings>
</configuration>

2. 獲取數(shù)據(jù)庫鏈接字串

// 獲取Oracle鏈接字串時(shí)：
private string OracleConn = ConfigurationManager.AppSettings["BookOracle"].ToString();
// 獲取postgre鏈接字串時(shí)：
private string PgConn = ConfigurationManager.AppSettings["BookPostgre"].ToString();

三、調(diào)用

1.調(diào)用Oracle數(shù)據(jù)庫

// 引入組件
using System.Configuration;
using System.Data;
using Oracle.ManagedDataAccess.Client;// 獲取數(shù)據(jù)庫信息&設(shè)置查詢語句（讀取設(shè)定）
private string OracleConn = ConfigurationManager.AppSettings["BookOracle"].ToString();
string bookpublishplace = "新聞出版社";
string OracleSql = $@"SELECT * FROM ""Book"" WHERE publishplace='{bookpublishplace}' ";// 查詢Oracle數(shù)據(jù)庫
DataTable GetResTb = OracleQuery(OracleSql, OracleConn);
List GetResList = GetResTb.AsEnumerable().Where(x => x.Field<string>("author") == "蘿卜").Select(x => x.Field<string>("bookname")).Distinct().ToList();
public DataTable OracleQuery(string sql, string connstr)
{try{using (OracleConnection conn = new OracleConnection(connstr)){conn.Open();OracleCommand cmd = new OracleCommand(sql, conn);// 執(zhí)行查詢，以table形式返回查詢結(jié)果OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);DataTable dt = new DataTable();dataAdapter.Fill(dt);conn.Close();return dt;// 執(zhí)行新增/更新/刪除，用數(shù)字0,1返回更新結(jié)果// int result = cmd.ExecuteNonQuery();// conn.Close();// return result;}}catch (Exception ex){// 執(zhí)行查詢，以空值null返回異常值return null;// 執(zhí)行新增/更新/刪除，用數(shù)字-1返回異常值// return -1;}
}// 執(zhí)行Oracle數(shù)據(jù)庫的存儲(chǔ)過程
OracleParameter[] OracleParameters = new OracleParameter[] {new OracleParameter("存儲(chǔ)過程欄位名1", OracleDbType.NVarchar2, 20, Factoryname, ParameterDirection.Input),new OracleParameter("存儲(chǔ)過程欄位名2", OracleDbType.NVarchar2, 20, Factoryname, ParameterDirection.Input),new OracleParameter("返回存儲(chǔ)過程Table1", OracleDbType.RefCursor, 40, "", ParameterDirection.Output),new OracleParameter("返回存儲(chǔ)過程Table2", OracleDbType.RefCursor, 40, "", ParameterDirection.Output),new OracleParameter("return_sql", OracleDbType.NVarchar2, 20000, "", ParameterDirection.Output),new OracleParameter("return_code", OracleDbType.Int32, 6, 0, ParameterDirection.Output),
};
DataSet BookDataSet = OracleQueryProcedure(OracleConn, "執(zhí)行過程名", OracleParameters);
authorname = '蘿卜'
DataTable getResTb = BookDataSet.Tables[0];
DataRow[] rows = getResTb.Select($"author ='{authorname}'");
if (rows.Length > 0)
{getColumnRes = rows[0]["authorage"].ToString()
}public DataSet OracleQueryProcedure(string connstr, string procedurename, OracleParameter[] Parameter)
{try{using (OracleConnection conn = new OracleConnection(connstr)){conn.Open();OracleCommand cmd = new OracleCommand();cmd.CommandText = procedurename;cmd.Connection = conn;cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.AddRange(Parameter);cmd.ExecuteNonQuery();OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);DataSet ds = new DataSet();dataAdapter.Fill(ds);conn.Close();return ds;}}catch (Exception ex){return null;}
}// 批量插入Oracle數(shù)據(jù)庫
private string targetConn = ConfigurationManager.AppSettings["OracleDB"].ToString();
DataTable resData = 獲取的表數(shù)據(jù);
Dictionary<string, string> AddColumnMappings = new Dictionary<string, string>() {{ "待插入datatable的欄位名", "數(shù)據(jù)庫表的欄位名" },{ "book_id", "BOOKID" },{ "book_name", "BOOKNAME" },
};
var res = OracleBulkInsert(targetConn, "表名", resData, AddColumnMappings) == 1 ? "success" : "fail";
Console.WriteLine($"res：{res}");/// <summary>
/// Oracle數(shù)據(jù)庫--批量插入
/// </summary>
/// <param name="connstr">數(shù)據(jù)庫鏈接</param>
/// <param name="targettb">目標(biāo)表</param>
/// <param name="dataTable">待插入數(shù)據(jù)</param>
/// <param name="AddColumnMappings">列名映射</param>
/// <returns>int</returns>
public int OracleBulkInsert(string connstr, string targettb, DataTable dataTable, Dictionary<string, string> AddColumnMappings)
{try{using (OracleConnection conn = new OracleConnection(connstr)){conn.Open();                    using (var bulkCopy = new OracleBulkCopy(conn)) // 使用 OracleBulkCopy 來批量插入數(shù)據(jù){bulkCopy.DestinationTableName = targettb; // 設(shè)置Oracle目標(biāo)表名bulkCopy.BatchSize = 1000; // 每次批量插入1000行數(shù)據(jù)                        foreach (var kvp in AddColumnMappings){bulkCopy.ColumnMappings.Add(kvp.Key, kvp.Value); // DataTable 的列名會(huì)映射到 Oracle 表的列名}bulkCopy.WriteToServer(dataTable); // 執(zhí)行批量插入}return 1;}}catch (Exception ex){//throw ex;return -1;}
}

2.調(diào)用postgre數(shù)據(jù)庫

在另一篇文章中：Backend - ADO.NET（C# 操作PostgreSQL DB）_c# postgresql-CSDN博客

3.結(jié)合不同數(shù)據(jù)庫聯(lián)合查詢

以下寫法是結(jié)合了ADO.NET 和 LINQ to Objects。

class CombineTb
{public string newbookname { get; set; } // 結(jié)合新表的欄位1public string newbookauthor { get; set; } // 結(jié)合新表的欄位2public string newbookplace { get; set; }    // 結(jié)合新表的欄位3
}
string OracleSql = $@"SELECT * FROM Book' ";
string PgSql = $@"SELECT bookplace,author,bookname FROM BookPublish";
DataTable GetOracleResTb = OracleQuery(OracleSql, OracleConn);
DataTable GetPostgreResTb = PgQuery(PgSql, PgConn);
List<CombineTb> CombineData = (from a in GetOracleResTb.AsEnumerable()join b in GetPostgreResTb.AsEnumerable()on new { bookname = a.Field<string>("name"), author = a.Field<string>("author") } equalsnew { bookname = b.Field<string>("bookname"), author = b.Field<string>("authorname") }select new CombineTb{newbookname = b.Field<string>("bookname"),newbookauthor = b.Field<string>("authorname"),newbookplace = b.Field<string>("bookplace")}).Distinct().ToList();
List<string> authorList = CombineData.Where(x => x.newbookname == "數(shù)據(jù)結(jié)構(gòu)").Select(x => x.newbookauthor).Distinct().ToList();