using System;
using System.Text;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Text.RegularExpressions;
using Microsoft.Win32;
namespace QueryDesigner
{
/// <summary>
/// Represents types of data tables.
/// </summary>
internal enum TableType
{
Table,
View,
Procedure
};
/// <summary>
/// DataSet that knows how to retrieve a schema (tables, columns, views,
/// sprocs, constraints, relations) based on a connection string.
/// </summary>
internal class OleDbSchema : DataSet
{
//----------------------------------------------------------------
#region ** fields
// connection string
string _connString = string.Empty;
// constants
const string TABLE = "TABLE";
const string VIEW = "VIEW";
const string LINK = "LINK";
const string PARAMETERS = "PARAMETERS";
const string TABLE_NAME = "TABLE_NAME";
const string TABLE_TYPE = "TABLE_TYPE";
const string TABLE_DEFINITION = "TABLE_DEFINITION";
const string TABLE_SCHEMA = "TABLE_SCHEMA";
const string COLUMN_NAME = "COLUMN_NAME";
const string PK_TABLE_NAME = "PK_TABLE_NAME";
const string FK_TABLE_NAME = "FK_TABLE_NAME";
const string PK_COLUMN_NAME = "PK_COLUMN_NAME";
const string FK_COLUMN_NAME = "FK_COLUMN_NAME";
const string PROCEDURE_NAME = "PROCEDURE_NAME";
const string PROCEDURE_SCHEMA = "PROCEDURE_SCHEMA";
const string PARAMETER_NAME = "PARAMETER_NAME";
const string PARAMETER_DEFAULT = "PARAMETER_DEFAULT";
const string PROCEDURE_PARAMETERS = "PROCEDURE_PARAMETERS";
const string PROCEDURE_DEFINITION = "PROCEDURE_DEFINITION";
const string DATA_TYPE = "DATA_TYPE";
const string RETURN_VALUE = "RETURN_VALUE";
const string TABLE_RETURN_VALUE = "TABLE_RETURN_VALUE";
#endregion
//----------------------------------------------------------------
#region ** object model
/// <summary>
/// Gets or sets the connection string used to fill this schema.
/// </summary>
public string ConnectionString
{
get { return _connString; }
set
{
if (value != _connString)
{
_connString = value;
GetSchema();
}
}
}
/// <summary>
/// Gets a <see cref="OleDbSchema"/> that contains the schema for a
/// given connection string.
/// </summary>
/// <param name="connString">OleDb connection string used to
/// initialize the new <see cref="OleDbSchema"/>.</param>
/// <returns>
/// A new <see cref="OleDbSchema"/> containing the schema for the
/// given <paramref name="connString"/> or null if the connection
/// string is invalid.
/// </returns>
public static OleDbSchema GetSchema(string connString)
{
// trivial test
connString = OleDbConnString.TranslateConnectionString(connString);
if (string.IsNullOrEmpty(connString) ||
connString.IndexOf("Provider=", StringComparison.OrdinalIgnoreCase) < 0)
{
return null;
}
// connString looks OK, try getting the schema
try
{
var ds = new OleDbSchema();
ds.ConnectionString = connString;
return ds;
}
catch
{
return null;
}
}
/// <summary>
/// Gets a select statement that can be used to retrieve data from a given table,
/// view, or stored procedure.
/// </summary>
/// <param name="table"><see cref="DataTable"/> that specifies the source table,
/// view, or stored procedure that contains the data.</param>
/// <returns>A select statement that can be used to retrieve the data.</returns>
public static string GetSelectStatement(DataTable table)
{
return GetTableType(table) == TableType.Procedure
? string.Format("EXEC {0}{1}", GetFullTableName(table), GetProcedureParameters(table))
: string.Format("SELECT * from {0}", GetFullTableName(table));
}
/// <summary>
/// Gets the table's underlying type (Table, View, or Stored Procedure).
/// </summary>
/// <param name="table"><see cref="DataTable"/> whose type will be returned.</param>
/// <returns>The table type.</returns>
public static TableType GetTableType(DataTable table)
{
// use TABLE_TYPE property
switch (table.ExtendedProperties[TABLE_TYPE] as string)
{
case TABLE:
return TableType.Table;
case LINK: // ?
case VIEW:
return TableType.View;
}
// no type, this is a stored procedure
return TableType.Procedure;
}
/// <summary>
/// Gets the full table name (e.g. 'dbo.[Order Details]).
/// </summary>
/// <param name="table"><see cref="DataTable"/> whose name will be returned.</param>
/// <returns>The full table name, including table schema and brackets when needed.</returns>
public static string GetFullTableName(DataTable table)
{
// build table name
var sb = new StringBuilder();
// get schema name
var schema = table.ExtendedProperties[TABLE_SCHEMA] as string;
if (schema != null)
{
sb.AppendFormat("{0}.", schema);
}
// append actual table name
sb.Append(BracketName(table.TableName));
// done
return sb.ToString();
}
/// <summary>
/// Encloses a name in square brackets.
/// </summary>
/// <param name="name">Name to enclose in brackets.</param>
/// <returns>The name enclosed in brackets.</returns>
public static string BracketName(string name)
{
// already bracketed
if (name.Length > 1 && name[0] == '[' && name[name.Length - 1] == ']')
{
return name;
}
// see if brackets are needed (never bracket expressions)
bool needsBrackets = false; // force
if (!IsExpression(name))
{
for (int i = 0; i < name.Length && !needsBrackets; i++)
{
char c = name[i];
needsBrackets = i == 0
? !char.IsLetter(c)
: !char.IsLetterOrDigit(c) && c != '_';
}
}
// done
return needsBrackets
? string.Format("[{0}]", name)
: name;
}
static char[] _expressionChars = "(),*".ToCharArray();
static bool IsExpression(string name)
{
return name.IndexOfAny(_expressionChars) > -1;
}
/// <summary>
/// Gets the parameters required by a stored procedure.
/// </summary>
/// <param name="table">DataTable that contains the schema for the stored procedure.</param>
/// <returns>A list of <see cref="OleDbParameter"/> objects.</returns>
public static List<OleDbParameter> GetTableParameters(DataTable table)
{
return table.ExtendedProperties[PROCEDURE_PARAMETERS] as List<OleDbParameter>;
}
#endregion
//----------------------------------------------------------------
#region ** utilities to deal with types (.NET, OleDb, Access)
/// <summary>
/// Checks whether a given type is numeric.
/// </summary>
/// <param name="type"><see cref="Type"/> to check.</param>
/// <returns>True if the type is numeric; false otherwise.</returns>
public static bool IsNumeric(Type type)
{
// handle regular types
switch (Type.GetTypeCode(type))
{
case TypeCode.Decimal:
case TypeCode.Double:
case TypeCode.Single:
case TypeCode.Byte:
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.SByte:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
return true;
}
// handle nullable types
type = Nullable.GetUnderlyingType(type);
return type != null
? IsNumeric(type)
: false;
}
/// <summary>
/// Translates an <see cref="OleDbType"/> into a .NET type.
/// </summary>
/// <param name="oleDbType"><see cref="OleDbType"/> to translate.</param>
/// <returns>The corresponding .NET <see cref="Type"/>.</returns>
public static Type GetType(OleDbType oleDbType)
{
switch ((int)oleDbType)
{
case 0: return typeof(Nullable);
case 2: return typeof(short);
case 3: return typeof(int);
case 4: return typeof(float);
case 5: return typeof(double);
case 6: return typeof(decimal);
case 7: return typeof(DateTime);
case 8: return typeof(string);
case 9: return typeof(object);
case 10: return typeof(Exception);
case 11: return typeof(bool);
case 12: return typeof(object);
case 13: return typeof(object);
case 14: return typeof(decimal);
case 16: return typeof(sbyte);
case 17: return typeof(byte);
case 18: return typeof(ushort);
case 19: return typeof(uint);
case 20: return typeof(long);
case 21: return typeof(ulong);
case 64: return typeof(DateTime);
case 72: return typeof(Guid);
case 128: return typeof(byte[]);
case 129: return typeof(string);
case 130: return typeof(string);
case 131: return typeof(decimal);
case 133: return typeof(DateTime);
case 134: return typeof(TimeSpan);
case 135: return typeof(DateTime);
case 138: return typeof(object);
case 139: return typeof(decimal);
case 200: return typeof(string);
case 201: return typeof(string);
case 202: return typeof(string);
case 203: return typeof(string);
case 204: return typeof(byte[]);
case 205: return typeof(byte[]);
}
Debug.WriteLine("** unknown type: " + oleDbType.ToString());
return typeof(string);
}
/// <summary>
/// Translates an Access parameter type (in PARAMETERS clause) into an <see cref="OleDbType"/>.
/// </summary>
/// <param name="typeName">Access parameter type.</param>
/// <returns>The corresponding <see cref="OleDbType"/></returns>
public static OleDbType GetOleDbType(string typeName)
{
switch (typeName.ToLower())
{
case "Bit": return OleDbType.Boolean;
case "Byte": return OleDbType.TinyInt;
case "Short": return OleDbType.SmallInt;
case "Long": return OleDbType.BigInt;
case "Currency": return OleDbType.Currency;
case "IEEESingle": return OleDbType.Single;
case "IEEEDouble": return OleDbType.Double;
case "DateTime": return OleDbType.Date;
case "Text": return OleDbType.VarChar;
case "Decimal": return OleDbType.Decimal;
}
Debug.WriteLine("** unknown type: '{0}'", typeName);
return OleDbType.VarChar;
}
#endregion
//----------------------------------------------------------------
#region ** implementation
void GetSchema()
{
// translate ODBC requests into OleDb
string connString = OleDbConnString.TranslateConnectionString(_connString);
// initialize this DataSet
this.Reset();
// go get the schema
EnforceConstraints = false;
using (var conn = new OleDbConnection(connString))
{
conn.Open();
GetTables(conn);
GetRelations(conn);
GetConstraints(conn);
GetStoredProcedures(conn);
conn.Close();
}
}
void GetTables(OleDbConnection conn)
{
// add tables
var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow dr in dt.Rows)
{
// get type (table/view)
var type = (string)dr[TABLE_TYPE];
if (type != TABLE && type != VIEW && type != LINK)
{
continue;
}
// create table
var name = (string)dr[TABLE_NAME];
var table = new DataTable(name);
table.ExtendedProperties[TABLE_TYPE] = type;
// save definition in extended properties
foreach (DataColumn col in dt.Columns)
{
table.ExtendedProperties[col.ColumnName] = dr[col];
}
// get table schema and add to collection
try
{
var select = GetSelectStatement(table);
var da = new OleDbDataAdapter(select, conn);
da.FillSchema(table, SchemaType.Mapped);
Tables.Add(table);
}
catch { }
}
}
void GetStoredProcedures(OleDbConnection conn)
{
// add stored procedures
var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, null);
foreach (DataRow dr in dt.Rows)
{
// get the procedure name, skip system stuff
var name = dr[PROCEDURE_NAME] as string;
if (name.StartsWith("~") || name.StartsWith("dt_", StringComparison.OrdinalIgnoreCase))
{
continue;
}
var schema = dr[PROCEDURE_SCHEMA] as string;
if (object.Equals(schema, "sys"))
{
continue;
}
// trim number that comes after name in Sql databases
int pos = name.LastIndexOf(';');
if (pos > -1)
{
int i;
if (int.TryParse(name.Substring(pos + 1), out i))
{
name = name.Substring(0, pos);
}
}
// create table
var table = new DataTable(name);
// get parameters
List<OleDbParameter> parmList = new List<OleDbParameter>();
table.ExtendedProperties[PROCEDURE_PARAMETERS] = parmList;
if (!conn.Provider.Contains("SQLOLEDB"))
{
GetAccessParameters(dr, parmList);
}
else
{
var dtParms = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedure_Parameters, new object[] { null, null, name, null });
var returnsValue = GetSqlServerParameters(dtParms, parmList);
if (!returnsValue)
{
continue;
}
}
// get table schema
try
{
var select = GetSelectStatement(table);
var da = new OleDbDataAdapter(select, ConnectionString);
da.FillSchema(table, SchemaType.Mapped);
}
catch (Exception x)
{
Debug.WriteLine(x.Message);
}
// add to collection (with or without schema)
Tables.Add(table);
}
}
// gets parameters from "PARAMETERS" statement in procedure definition (Access)
void GetAccessParameters(DataRow dr, List<OleDbParameter> list)
{
var procDef = dr[PROCEDURE_DEFINITION] as string;
if (procDef != null && procDef.StartsWith(PARAMETERS, StringComparison.OrdinalIgnoreCase))
{
int pos = procDef.IndexOf(';');
if (pos > -1)
{
var parmDef = procDef.Substring(11, pos - 11);
foreach (string parm in parmDef.Split(','))
{
pos = parm.LastIndexOf(' ');
if (pos > -1)
{
var p = new OleDbParameter();
p.ParameterName = parm.Substring(0, pos).Trim();
p.OleDbType = GetOleDbType(parm.Substring(pos + 1));
list.Add(p);
}
}
}
}
}
// get parameters from the parameters table (SqlServer)
bool GetSqlServerParameters(DataTable dtParms, List<OleDbParameter> list)
{
bool returnsValue = false;
foreach (DataRow parm in dtParms.Rows)
{
// get parameter name
var name = ((string)parm[PARAMETER_NAME]).Substring(1);
if (name == RETURN_VALUE || name == TABLE_RETURN_VALUE)
{
returnsValue = true;
continue;
}
// save parameter
var p = new OleDbParameter(name, (OleDbType)parm[DATA_TYPE]);
p.Value = parm[PARAMETER_DEFAULT] as string;
if (p.Value == null)
{
p.Value = string.Empty;
}
list.Add(p);
}
// done
return returnsValue;
}
// get relations from schema
void GetRelations(OleDbConnection conn)
{
var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
foreach (DataRow dr in dt.Rows)
{
// get primary/foreign table and column names
string pkTableName = (string)dr[PK_TABLE_NAME];
string fkTableName = (string)dr[FK_TABLE_NAME];
string pkColumnName = (string)dr[PK_COLUMN_NAME];
string fkColumnName = (string)dr[FK_COLUMN_NAME];
// make sure both tables are in our DataSet
if (Tables.Contains(pkTableName) && Tables.Contains(fkTableName))
{
// make sure tables are different
if (pkTableName != fkTableName)
{
// get unique relation name
string relationName = pkTableName + '_' + fkTableName;
if (Relations.Contains(relationName))
{
relationName += Relations.Count.ToString();
}
// add to collection
DataColumn pkColumn = Tables[pkTableName].Columns[pkColumnName];
DataColumn fkColumn = Tables[fkTableName].Columns[fkColumnName];
Relations.Add(relationName, pkColumn, fkColumn, true);
}
}
}
}
// get constraints from schema
void GetConstraints(OleDbConnection conn)
{
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);
var uniqueTables = new Dictionary<string, string>();
foreach (DataRow dr in dt.Rows)
{
// get primary key info
string tableName = dr[TABLE_NAME].ToString();
string columnName = dr[COLUMN_NAME].ToString();
// make sure this table is in our DataSet
if (Tables.Contains(tableName))
{
// make sure it's unique
if (uniqueTables.ContainsKey(tableName))
{
uniqueTables.Remove(tableName);
continue;
}
// save and move on
uniqueTables[tableName] = columnName;
}
}
// built unique list, now set up primary key columns
foreach (string tableName in uniqueTables.Keys)
{
// set up column
var columnName = (string)uniqueTables[tableName];
var table = Tables[tableName];
var pk = table.Columns[columnName];
pk.Unique = true;
pk.AllowDBNull = false;
// try setting auto increment
if (pk.DataType != typeof(string) && pk.DataType != typeof(byte))
{
try
{
pk.AutoIncrement = true;
pk.ReadOnly = true;
}
catch { }
}
// set primary key on parent table
Tables[tableName].PrimaryKey = new DataColumn[] { pk };
}
}
// returns a string containing stored procedure parameters
static string GetProcedureParameters(DataTable table)
{
var sb = new StringBuilder();
// get Sql server type parameters
var parms = table.ExtendedProperties[PROCEDURE_PARAMETERS] as List<OleDbParameter>;
if (parms != null)
{
foreach (OleDbParameter parm in parms)
{
if (sb.Length > 0)
{
sb.Append(", ");
}
var value = parm.Value as string;
sb.AppendFormat("'{0}'", value.Replace("'", "''"));
}
}
return sb.ToString();
}
#endregion
}
}