Oracle Data Provider到CLR类型的映射
发布时间：2020-12-12 12:40:58 所属栏目：百科 来源：网络整理
导读：在哪里可以找到ODP到CLR类型映射的列表？ 在Oracle数据库中,NUMBER(9,0)类型在.NET应用程序中作为System.Decimal从MS Oracle驱动程序中出现,但作为来自ODP驱动程序的System.Int32.我需要从数据库中出来的类型的确切规范(而不是CLR到DB参数映射). 解决方法 运
|
在哪里可以找到ODP到CLR类型映射的列表？
在Oracle数据库中,NUMBER(9,0)类型在.NET应用程序中作为System.Decimal从MS Oracle驱动程序中出现,但作为来自ODP驱动程序的System.Int32.我需要从数据库中出来的类型的确切规范(而不是CLR到DB参数映射). 解决方法运行此简单测试以获取SqlServer和Oracle(MS和ODP.NET驱动程序)的映射：using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Oracle.DataAccess.Client;
namespace DbOutTypeTest
{
public class Program
{
private static string SqlServerConnectionString = @"";
private static string OracleConnectionString = @"";
private static void WriteHeader(string title)
{
Console.WriteLine("----------------------------------------------------------");
Console.WriteLine("-- {0}",title);
Console.WriteLine("----------------------------------------------------------");
}
private static void WriteRow(string key,string value)
{
Console.WriteLine("{0}tt{1}",key.PadRight(30,' '),value);
}
private static void EnumerateTypes(IDbConnection connection,string template,IEnumerable<string> types)
{
EnumerateTypes(connection,template,types,(arg1,arg2) => { });
}
private static void EnumerateTypes(IDbConnection connection,IEnumerable<string> types,Action<string,string> action)
{
connection.Open();
using (var command = connection.CreateCommand())
{
foreach (var type in types)
{
var value = "";
command.CommandText = string.Format(template,type);
try
{
using (var reader = command.ExecuteReader())
{
if (reader.Read())
value = reader[0].GetType().FullName;
else
value = "<no data read>";
}
}
catch (Exception ex)
{
value = ex.Message;
}
WriteRow(type,value);
action(type,value);
}
}
}
private static IEnumerable<string> SqlServerIntegers()
{
yield return "tinyint";
yield return "smallint";
yield return "int";
yield return "bigint";
for (int precision = 1; precision <= 38; ++precision)
{
yield return "numeric(" + precision + ",0)";
}
yield break;
}
private static IEnumerable<string> SqlServerFloatings()
{
yield return "real";
yield return "float";
for (int precision = 1; precision <= 38; ++precision)
{
for (int scale = 1; scale <= precision; ++scale)
yield return "numeric(" + precision + "," + scale + ")";
}
yield break;
}
private static IEnumerable<string> OracleIntegers()
{
for (int precision = 1; precision <= 38; ++precision)
{
yield return "number(" + precision + ",0)";
}
yield break;
}
private static IEnumerable<string> OracleFloatings()
{
for (int precision = 1; precision <= 38; ++precision)
{
for (int scale = 1; scale <= precision; ++scale)
yield return "number(" + precision + "," + scale + ")";
}
yield break;
}
public static void Main(string[] args)
{
WriteHeader("C# types - CLR names");
Console.WriteLine("{0}tt{1}","byte".PadRight(30,typeof(byte).FullName);
Console.WriteLine("{0}tt{1}","short".PadRight(30,typeof(short).FullName);
Console.WriteLine("{0}tt{1}","int".PadRight(30,typeof(int).FullName);
Console.WriteLine("{0}tt{1}","long".PadRight(30,typeof(long).FullName);
Console.WriteLine("{0}tt{1}","float".PadRight(30,typeof(float).FullName);
Console.WriteLine("{0}tt{1}","double".PadRight(30,typeof(double).FullName);
var OracleToClrInteger = new Dictionary<string,string>();
var OracleToClrFloating = new Dictionary<string,string>();
var SqlServerToClrInteger = new Dictionary<string,string>();
var SqlServerToClrFloating = new Dictionary<string,string>();
WriteHeader("Oracle integers mapping (Oracle Data Provider)");
using (var connection = new OracleConnection(OracleConnectionString))
{
EnumerateTypes(connection,"SELECT CAST(0 AS {0}) FROM DUAL",OracleIntegers(),(type,value) => OracleToClrInteger.Add(type,value));
}
WriteHeader("SQLServer integers mapping");
using (var connection = new SqlConnection(SqlServerConnectionString))
{
EnumerateTypes(connection,"SELECT CAST(0 AS {0})",SqlServerIntegers(),value) => SqlServerToClrInteger.Add(type,value));
}
WriteHeader("Oracle integers mapping (Microsoft Oracle Client)");
using (var connection = new System.Data.OracleClient.OracleConnection(OracleConnectionString))
{
EnumerateTypes(connection,OracleIntegers());
}
WriteHeader("Oracle floats mapping (Oracle Data Provider)");
using (var connection = new OracleConnection(OracleConnectionString))
{
EnumerateTypes(connection,OracleFloatings(),value) => OracleToClrFloating.Add(type,value));
}
WriteHeader("SQLServer floats mapping");
using (var connection = new SqlConnection(SqlServerConnectionString))
{
EnumerateTypes(connection,SqlServerFloatings(),value) => SqlServerToClrFloating.Add(type,value));
}
WriteHeader("Oracle floats mapping (Microsoft Oracle Client)");
using (var connection = new System.Data.OracleClient.OracleConnection(OracleConnectionString))
{
EnumerateTypes(connection,OracleFloatings());
}
WriteHeader("Suggested integer type mapping Oracle -> SqlServer");
foreach (var pair in OracleToClrInteger)
{
if (pair.Value == "System.Decimal")
WriteRow(pair.Key,pair.Key.Replace("number","numeric"));
else
{
if (!SqlServerToClrInteger.Values.Contains(pair.Value))
WriteRow(pair.Key,"???");
else
WriteRow(pair.Key,SqlServerToClrInteger.First(p => p.Value == pair.Value).Key);
}
}
WriteHeader("Suggested floating type mapping Oracle -> SqlServer");
foreach (var pair in OracleToClrFloating)
{
if (pair.Value == "System.Decimal")
WriteRow(pair.Key,"numeric"));
else
{
if (!SqlServerToClrFloating.Values.Contains(pair.Value))
WriteRow(pair.Key,SqlServerToClrFloating.First(p => p.Value == pair.Value).Key);
}
}
}
}
}
最有趣的部分： ---------------------------------------------------------- -- Oracle integers mapping (Oracle Data Provider) ---------------------------------------------------------- number(1,0) System.Int16 number(2,0) System.Int16 number(3,0) System.Int16 number(4,0) System.Int16 number(5,0) System.Int32 number(6,0) System.Int32 number(7,0) System.Int32 number(8,0) System.Int32 number(9,0) System.Int32 number(10,0) System.Int64 number(11,0) System.Int64 number(12,0) System.Int64 number(13,0) System.Int64 number(14,0) System.Int64 number(15,0) System.Int64 number(16,0) System.Int64 number(17,0) System.Int64 number(18,0) System.Int64 number(19,0) System.Decimal number(20,0) System.Decimal number(21,0) System.Decimal number(22,0) System.Decimal number(23,0) System.Decimal number(24,0) System.Decimal （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |