Oracle数据提供程序到CLR类型的映射 [英] Oracle Data Provider to CLR type mapping
本文介绍了Oracle数据提供程序到CLR类型的映射的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在哪里可以找到ODP到CLR类型映射的列表? 在Oracle数据库上，.NET应用程序中的NUMBER(9,0)类型从MS Oracle驱动程序中以System.Decimal的形式出现，但从ODP驱动程序中以System.Int32的形式出现.我需要一个从数据库出来的类型的精确规范(而不是CLR到DB参数的映射).
Where can I find the listing of ODP to CLR type mapping? On Oracle database, the NUMBER(9,0) type comes out in .NET app as System.Decimal from the MS Oracle driver, but as System.Int32 from ODP driver. I need an exact specification of types coming out from database (not the CLR to DB parameter mapping).
推荐答案
运行此简单测试以获取SqlServer和Oracle(MS和ODP.NET驱动程序)的映射:
Run this simple test to get mappings for SqlServer and Oracle (both MS and ODP.NET drivers):
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}\t\t{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, string template, 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}\t\t{1}", "byte".PadRight(30, ' '), typeof(byte).FullName);
Console.WriteLine("{0}\t\t{1}", "short".PadRight(30, ' '), typeof(short).FullName);
Console.WriteLine("{0}\t\t{1}", "int".PadRight(30, ' '), typeof(int).FullName);
Console.WriteLine("{0}\t\t{1}", "long".PadRight(30, ' '), typeof(long).FullName);
Console.WriteLine("{0}\t\t{1}", "float".PadRight(30, ' '), typeof(float).FullName);
Console.WriteLine("{0}\t\t{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(), (type, value) => SqlServerToClrInteger.Add(type, value));
}
WriteHeader("Oracle integers mapping (Microsoft Oracle Client)");
using (var connection = new System.Data.OracleClient.OracleConnection(OracleConnectionString))
{
EnumerateTypes(connection, "SELECT CAST(0 AS {0}) FROM DUAL", OracleIntegers());
}
WriteHeader("Oracle floats mapping (Oracle Data Provider)");
using (var connection = new OracleConnection(OracleConnectionString))
{
EnumerateTypes(connection, "SELECT CAST(0 AS {0}) FROM DUAL", OracleFloatings(), (type, value) => OracleToClrFloating.Add(type, value));
}
WriteHeader("SQLServer floats mapping");
using (var connection = new SqlConnection(SqlServerConnectionString))
{
EnumerateTypes(connection, "SELECT CAST(0 AS {0})", SqlServerFloatings(), (type, value) => SqlServerToClrFloating.Add(type, value));
}
WriteHeader("Oracle floats mapping (Microsoft Oracle Client)");
using (var connection = new System.Data.OracleClient.OracleConnection(OracleConnectionString))
{
EnumerateTypes(connection, "SELECT CAST(0 AS {0}) FROM DUAL", 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, pair.Key.Replace("number", "numeric"));
else
{
if (!SqlServerToClrFloating.Values.Contains(pair.Value))
WriteRow(pair.Key, "???");
else
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
这篇关于Oracle数据提供程序到CLR类型的映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
