sexta-feira, 24 de julho de 2015

DB Utils

using System;
using System.Collections;
using System.Data;
using System.Xml;
using System.Data.OleDb;
using System.Data.Odbc;
//using System.Data.OracleClient;



namespace Lapuinka
{
       ///
       /// Summary description for DBUtils.
       ///
       public class DBUtils
       {
             public static ArrayList GetTables(OleDbConnection conn)
             {
                    ArrayList al = new ArrayList();
                    bool open=false;
                    if(conn.State==System.Data.ConnectionState.Closed)
                    {
                           conn.Open();
                           open=true;
                    }
                    DataTable schemaTables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
                    foreach(DataRow myRow in schemaTables.Rows)
                           al.Add(myRow["TABLE_NAME"]);
                   
                    if(open)
                           conn.Close();
                    return al;

             }
             public static ArrayList GetColumns(OleDbConnection conn,string tableName)
             {
                    ArrayList al = new ArrayList();
                    bool open=false;
                    if(conn.State==System.Data.ConnectionState.Closed)
                    {
                           conn.Open();
                           open=true;
                    }

                   
                    DataTable schemaColumns = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
                           new object[] {null, null, tableName, null});
                    foreach(DataRow myRow in schemaColumns.Rows)
                           al.Add(myRow["COLUMN_NAME"]);

                    if(open)
                           conn.Close();
                    return al;
                   
             }
             private static string DataTypeToString(Object obj)
             {
                    //if (obj is System.ArgIterator) return "System.ArgIterator";
                    if (obj is System.Boolean) return "System.Boolean";
                    if (obj is System.Byte) return "System.Byte";
                    if (obj is System.Char) return "System.Char";
                    if (obj is System.Collections.DictionaryEntry) return "System.Collections.DictionaryEntry";
                    if (obj is System.Collections.Specialized.BitVector32) return "System.Collections.Specialized.BitVector32";
                    if (obj is System.Collections.Specialized.BitVector32.Section) return "System.Collections.Specialized.BitVector32.Section";
                    //                  if (obj is System.Data.OracleClient.OracleBinary) return "System.Data.OracleClient.OracleBinary";
                    //                  if (obj is System.Data.OracleClient.OracleBoolean) return "System.Data.OracleClient.OracleBoolean";
                    //                  if (obj is System.Data.OracleClient.OracleDateTime) return "System.Data.OracleClient.OracleDateTime";
                    //                  if (obj is System.Data.OracleClient.OracleMonthSpan) return "System.Data.OracleClient.OracleMonthSpan";
                    //                  if (obj is System.Data.OracleClient.OracleNumber) return "System.Data.OracleClient.OracleNumber";
                    //                  if (obj is System.Data.OracleClient.OracleString) return "System.Data.OracleClient.OracleString";
                    //                  if (obj is System.Data.OracleClient.OracleTimeSpan) return "System.Data.OracleClient.OracleTimeSpan";
                    if (obj is System.Data.SqlTypes.SqlBinary) return "System.Data.SqlTypes.SqlBinary";
                    if (obj is System.Data.SqlTypes.SqlBoolean) return "System.Data.SqlTypes.SqlBoolean";
                    if (obj is System.Data.SqlTypes.SqlByte) return "System.Data.SqlTypes.SqlByte";
                    if (obj is System.Data.SqlTypes.SqlDateTime) return "System.Data.SqlTypes.SqlDateTime";
                    if (obj is System.Data.SqlTypes.SqlDecimal) return "System.Data.SqlTypes.SqlDecimal";
                    if (obj is System.Data.SqlTypes.SqlDouble) return "System.Data.SqlTypes.SqlDouble";
                    if (obj is System.Data.SqlTypes.SqlGuid) return "System.Data.SqlTypes.SqlGuid";
                    if (obj is System.Data.SqlTypes.SqlInt16) return "System.Data.SqlTypes.SqlInt16";
                    if (obj is System.Data.SqlTypes.SqlInt32) return "System.Data.SqlTypes.SqlInt32";
                    if (obj is System.Data.SqlTypes.SqlInt64) return "System.Data.SqlTypes.SqlInt64";
                    if (obj is System.Data.SqlTypes.SqlMoney) return "System.Data.SqlTypes.SqlMoney";
                    if (obj is System.Data.SqlTypes.SqlSingle) return "System.Data.SqlTypes.SqlSingle";
                    if (obj is System.Data.SqlTypes.SqlString) return "System.Data.SqlTypes.SqlString";
                    if (obj is System.DateTime) return "System.DateTime";
                    if (obj is System.Decimal) return "System.Decimal";
                    if (obj is System.Double) return "System.Double";
                    if (obj is System.Enum) return "System.Enum";
                    if (obj is System.Guid) return "System.Guid";
                    if (obj is System.Int16) return "System.Int16";
                    if (obj is System.Int32) return "System.Int32";
                    if (obj is System.Int64) return "System.Int64";
                    if (obj is System.IntPtr) return "System.IntPtr";
                    if (obj is System.SByte) return "System.SByte";
                    if (obj is System.Single) return "System.Single";
                    if (obj is System.TimeSpan) return "System.TimeSpan";
                    //if (obj is System.TypedReference) return "System.TypedReference";
                    if (obj is System.UInt16) return "System.UInt16";
                    if (obj is System.UInt32) return "System.UInt32";
                    if (obj is System.UInt64) return "System.UInt64";
                    if (obj is System.UIntPtr) return "System.UIntPtr";
                    if (obj.GetType()==typeof(void)) return "System.Void";
                    return "?";
             }
             public enum FieldTypes { unknown, String, Integer, Float, Boolean, DateTime, Time, Blob }

            
             public static FieldTypes TypeToFieldTypes(Type type)
             {
                    return TypeToFieldTypes(type.ToString());
             }
             public static FieldTypes TypeToFieldTypes(string sztype)
             {
                    //Primitive Types: unknown, String, Integer, Float, Boolean, DateTime, Time, Blob
                    switch (sztype)
                    {
                           case "System.ArgIterator"                                                        : return FieldTypes.unknown;
                           case "System.Boolean"                                                                  : return FieldTypes.Boolean;
                           case "System.Byte"                                                                     : return FieldTypes.Integer;
                           case "System.Char"                                                                     : return FieldTypes.String;
                           case "System.Collections.DictionaryEntry"                                 : return FieldTypes.unknown;
                           case "System.Collections.Specialized.BitVector32"                  : return FieldTypes.unknown;
                           case "System.Collections.Specialized.BitVector32.Section"   : return FieldTypes.unknown;
                           case "System.Data.SqlTypes.SqlBinary"                                     : return FieldTypes.unknown;
                           case "System.Data.SqlTypes.SqlBoolean"                                    : return FieldTypes.Boolean;
                           case "System.Data.SqlTypes.SqlByte"                                              : return FieldTypes.Integer;
                           case "System.Data.SqlTypes.SqlDateTime"                            : return FieldTypes.DateTime;
                           case "System.Data.SqlTypes.SqlDecimal"                                    : return FieldTypes.Float;
                           case "System.Data.SqlTypes.SqlDouble"                                     : return FieldTypes.Float;
                           case "System.Data.SqlTypes.SqlGuid"                                              : return FieldTypes.unknown;
                           case "System.Data.SqlTypes.SqlInt16"                                      : return FieldTypes.Integer;
                           case "System.Data.SqlTypes.SqlInt32"                                      : return FieldTypes.Integer;
                           case "System.Data.SqlTypes.SqlInt64"                                      : return FieldTypes.Integer;
                           case "System.Data.SqlTypes.SqlMoney"                                      : return FieldTypes.Float;
                           case "System.Data.SqlTypes.SqlSingle"                                     : return FieldTypes.Float;
                           case "System.Data.SqlTypes.SqlString"                                     : return FieldTypes.String;
                           case "System.DateTime"                                                                 : return FieldTypes.DateTime;
                           case "System.Decimal"                                                                  : return FieldTypes.Float;
                           case "System.Double"                                                                   : return FieldTypes.Float;
                           case "System.Enum"                                                                     : return FieldTypes.unknown;
                           case "System.Guid"                                                                     : return FieldTypes.Integer;
                           case "System.Int16"                                                                    : return FieldTypes.Integer;
                           case "System.Int32"                                                                    : return FieldTypes.Integer;
                           case "System.Int64"                                                                    : return FieldTypes.Integer;
                           case "System.IntPtr"                                                                   : return FieldTypes.Integer;
                           case "System.SByte"                                                                    : return FieldTypes.Integer;
                           case "System.String"                                                                   : return FieldTypes.String;
                           case "System.Single"                                                                   : return FieldTypes.Float;
                           case "System.TimeSpan"                                                                 : return FieldTypes.DateTime;
                           case "System.TypedReference"                                                     : return FieldTypes.unknown;
                           case "System.UInt16"                                                                   : return FieldTypes.Integer;
                           case "System.UInt32"                                                                   : return FieldTypes.Integer;
                           case "System.UInt64"                                                                   : return FieldTypes.Integer;
                           case "System.UIntPtr"                                                                  : return FieldTypes.Integer;
                           case "System.Void"                                                                     : return FieldTypes.unknown;
                           case "Integer"                                                                                : return FieldTypes.Integer;
                           case "DateTime"                                                                               : return FieldTypes.DateTime;
                           case "Float"                                                                           : return FieldTypes.Float;
                           case "Boolean"                                                                                : return FieldTypes.Boolean;
                           case "Blob"                                                                                   : return FieldTypes.Blob;
                           case "String"                                                                          : return FieldTypes.String;
                          
                           default: return FieldTypes.unknown;
                    }
              }
             public static string DataTypeintToString(int dataType)
             {
                    switch (dataType)
                    {
                           case 2 : return "Integer";
                           case 3 : return "Integer";
                           case 5 : return "Float";
                           case 6 : return "Float";
                           case 7 : return "DateTime";
                           case 11 : return "Boolean";
                           case 128 : return "Blob";
                           case 130 : return "String";
                    }                                             
                    return "";
             }
             public static FieldTypes GetColumnDataType(OleDbConnection conn,string tableName,string columnName)
             {
                    ArrayList    al = new ArrayList();
                    FieldTypes  ret = FieldTypes.unknown;
                    bool open=false;
                    if(conn.State==System.Data.ConnectionState.Closed)
                    {
                           conn.Open();
                           open=true;
                    }

                    DataTable schemaColumns = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
                           new object[] {null, null, tableName, columnName});
                    if (schemaColumns.Rows.Count > 0)
                           foreach(DataRow myRow in schemaColumns.Rows)
                           {
                                  object obj = myRow["DATA_TYPE"];
                                  ret = TypeToFieldTypes(DataTypeintToString((System.Int32)obj));                              
                                  break;
                           }
                    if(open)
                           conn.Close();
                    return ret;
             }
       }

}
Postar um comentário