Oracle Data Provider到CLR类型的映射

在哪里可以找到ODP到CLR类型映射的列表？
在Oracle数据库中,NUMBER(9,0)类型在.NET应用程序中作为System.Decimal从MS Oracle驱动程序中出现,但作为来自ODP驱动程序的System.Int32.我需要从数据库中出来的类型的确切规范(而不是CLR到DB参数映射).

解决方法

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);
                }
            }

        }
    }
}

最有趣的部分：

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!