public override string GetNativeDbTypeName(IDataParameter para)
{
OleDbType type = ((OleDbParameter)para).OleDbType;
if (type == OleDbType.VarWChar)
{
type = OleDbType.VarChar;
}
else if (type == OleDbType.DBDate)
{
type = OleDbType.Date;
}
else if (type == OleDbType.DBTimeStamp) //2014.5.14 增加，感谢网友 广州-晓伟 发现此问题
{
type = OleDbType.Date;
}
else if (type == OleDbType.Boolean)
{
return("YESNO");
}
else if (type == OleDbType.BigInt)
{
return("Long");
}
return(type.ToString());
}
public static string GetOleDBTypeNameFromID(int oleDbTypeID)
{
try
{
OleDbType oleTyp = (OleDbType)oleDbTypeID;
return(oleTyp.ToString());
}
catch (Exception)
{
}
return(null);
}
public override string GetNativeDbTypeName(IDataParameter para)
{
OleDbType type = ((OleDbParameter)para).OleDbType;
if (type == OleDbType.VarWChar)
{
type = OleDbType.VarChar;
}
else if (type == OleDbType.DBDate)
{
type = OleDbType.Date;
}
return(type.ToString());
}
public override string GetCommand(Column column, string parameterName, System.Data.ParameterDirection parameterDirection, DataRowVersion dataRowVersion, bool isNullable)
{
OleDbType oleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), column.OriginalSQLType);
return(string.Format("System.Data.OleDb.OleDbParameter(\"{0}\", System.Data.OleDb.OleDbType.{1}, {2}, System.Data.ParameterDirection.{3}, {4}, (System.Byte)({5}), (System.Byte)({6}), \"{7}\", System.Data.DataRowVersion.{8}, null)",
parameterName, /*The name of the parameter*/
oleDbType.ToString(), /*One of the OleDbType values*/
column.Length.ToString(), /*The length of the parameter*/
parameterDirection.ToString(), /*One of the ParameterDirection values*/
isNullable.ToString(),
column.Precision.ToString(), /*The total number of digits to the left and right of the decimal point to which Value is resolved*/
column.Scale.ToString(), /*The total number of decimal places to which Value is resolved*/
column.Name, /*The name of the source column*/
dataRowVersion.ToString()
));
}
public Dictionary <String, OleDbColumnDefinition> GetSchema(String connectionString, String tableName)
{
Dictionary <String, OleDbColumnDefinition> schema = new Dictionary <String, OleDbColumnDefinition>();
connectionString = new VfpConnectionStringBuilder(connectionString).ConnectionString;
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
String[] restrictions = new String[] { null, null, tableName, null };
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, restrictions);
foreach (DataRow row in schemaTable.Rows)
{
String name = row[Constants.OleDbSchemaColumnNames.Column].ToString();
OleDbType dbType = (OleDbType)row[Constants.OleDbSchemaColumnNames.DataType];
//if (name.ToUpper().StartsWith("PERCENT"))
//{
// object np = row[15];
// object ns = row[16];
//}
int? numericPrecision;
short?numericScale;
if (dbType.ToString() == Constants.OleDbTypeNames.Numeric)
{
numericPrecision = (int?)row[Constants.OleDbSchemaColumnNames.NumericPrecision];
numericScale = (short?)row[Constants.OleDbSchemaColumnNames.NumericScale];
}
else
{
numericPrecision = null;
numericScale = null;
}
OleDbColumnDefinition colDef = new OleDbColumnDefinition()
{
Name = name, Type = dbType, NumericPrecision = numericPrecision, NumericScale = numericScale
};
schema.Add(colDef.Name, colDef);
}
conn.Close();
}
return(schema);
}
private void BtnLoadFoxColumn_Click(object sender, RoutedEventArgs e)
{
try
{
if (CbTableFox.SelectedIndex >= 0)
{
string root = TxPathFoxPro.Text;
string strCon = @"Provider=VFPOLEDB.1;Data Source=" + root + ";Collating Sequence=MACHINE;Connection Timeout=20;Exclusive=NO;DELETED=True;EXACT=False";
if (!TestConnectionFox(TxPathFoxPro.Text))
{
return;
}
DataTable dt = new DataTable();
string table = CbTableFox.SelectedValue.ToString().Trim();
OleDbConnection con = new OleDbConnection(strCon);
con.Open();
DataTable dtCols = con.GetSchema("Columns");
DataRow[] d = dtCols.Select("TABLE_NAME='" + table + "' ");
DataTable dt1 = d.CopyToDataTable();
dt1.Columns.Add("TYPE_FOX");
foreach (DataRow item in dt1.Rows)
{
OleDbType columnType = (OleDbType)item["DATA_TYPE"];
item.BeginEdit();
item["TYPE_FOX"] = columnType.ToString();
item.EndEdit();
}
dt_colfox.Clear(); dt_colfox = dt1;
GridFoxPro.ItemsSource = dt1.Rows.Count > 0 ? dt1.DefaultView : null;
TxTotalFox.Text = dt_colfox.Rows.Count.ToString();
con.Close();
}
else
{
MessageBox.Show("Load the tables and select one of them", "alert", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
catch (Exception)
{
MessageBox.Show("error load columns", "alert", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public static TOM.DataType ToTOMDataType(OleDbType AMODataType)
{
try
{
switch (AMODataType)
{
case OleDbType.WChar:
return(TOM.DataType.String);
case OleDbType.Binary:
return(TOM.DataType.Binary);
case OleDbType.Boolean:
return(TOM.DataType.Boolean);
case OleDbType.Date:
return(TOM.DataType.DateTime);
case OleDbType.Decimal:
return(TOM.DataType.Decimal);
case OleDbType.Double:
return(TOM.DataType.Double);
case OleDbType.BigInt:
return(TOM.DataType.Int64);
case OleDbType.Variant:
return(TOM.DataType.Variant);
case OleDbType.Empty:
return(TOM.DataType.Unknown);
default:
throw new Exception("The following type is unhandled: " + AMODataType.ToString());
}
}
catch (Exception e)
{
throw new Exception("Error mapping AMO DataType: " + e.Message);
}
}
/// <summary>
/// Este método preenche os dados das colunas da table.
/// </summary>
private void GetColumnData(TableMap map)
{
DataTable dt = GetColumns(map.TableName);
foreach (DataRow row in dt.Rows)
{
string columnName = (string)row["COLUMN_NAME"];
FieldMap fm = map.GetFieldMapFromColumn(columnName);
if (fm == null)
{
fm = new FieldMap(map, columnName);
map.Fields.Add(fm);
}
fm.IsNullable = Convert.ToBoolean(row["IS_NULLABLE"]);
OleDbType dbType = (OleDbType)row["DATA_TYPE"];
fm.SetDbType((long)dbType);
fm.DbTypeName = dbType.ToString();
if (dbType == OleDbType.Decimal || dbType == OleDbType.Numeric || dbType == OleDbType.VarNumeric)
{
fm.Size = Convert.ToInt32(row["NUMERIC_PRECISION"]);
}
else if (dbType == OleDbType.LongVarBinary || dbType == OleDbType.LongVarChar || dbType == OleDbType.LongVarWChar || dbType == OleDbType.VarBinary || dbType == OleDbType.VarChar || dbType == OleDbType.VarWChar || dbType == OleDbType.WChar || dbType == OleDbType.Char || dbType == OleDbType.BSTR || dbType == OleDbType.Binary)
{
fm.Size = Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]);
}
int columnFlags = Convert.ToInt32(row["COLUMN_FLAGS"]);
int flags = (int)DBCOLUMNFLAGS.ISNULLABLE + (int)DBCOLUMNFLAGS.MAYBENULL;
bool isNullableFlag = (columnFlags & flags) != 0;
flags = (int)DBCOLUMNFLAGS.WRITE + (int)DBCOLUMNFLAGS.WRITEUNKNOWN;
bool isReadOnly = (columnFlags & flags) == 0;
fm.IsReadOnly = isReadOnly;
if (row["DESCRIPTION"] != DBNull.Value && row["DESCRIPTION"] is string)
{
fm.Comment = row["DESCRIPTION"].ToString();
}
}
}
/// <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);
}
static internal Exception UninitializedParameters(int index, OleDbType dbtype) {
return ADP.InvalidOperation(Res.GetString(Res.OleDb_UninitializedParameters, index.ToString(CultureInfo.InvariantCulture), dbtype.ToString()));
}
public static void CreateExcel(System.Data.DataTable dt, List <ObjectItem <string, Type, int> > listColumns, string fileName = null, string idColumn = null)
{
if (String.IsNullOrEmpty(fileName))
{
fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
}
if (!string.IsNullOrEmpty(fileName))
{
string extName = Path.GetExtension(fileName);
string connString = string.Empty;
if (extName.Equals(".xlsx"))
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0 XML;Data Source=" + fileName + ";";
}
else
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + fileName + ";";
}
OleDbConnection conn = new OleDbConnection(connString);
try
{
conn.Open();
int col = dt.Columns.Count;
if (col > 3)
{
if (!String.IsNullOrWhiteSpace(idColumn))
{
var rr = dt.Select(idColumn + " IS NULL");
foreach (DataRow r in rr)
{
dt.Rows.Remove(r);
}
}
string tabName = "Test";
List <string> list1 = new List <string>();
List <string> list2 = new List <string>();
List <string> list3 = new List <string>();
OleDbDataAdapter ada = new OleDbDataAdapter();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
ada.InsertCommand = cmd;
foreach (var oi in listColumns)
{
string colName = oi.Id;
if (dt.Columns.Contains(colName))
{
if (!cmd.Parameters.Contains("@" + colName))
{
OleDbType odType = GetOleDbType((oi.Name));
String typeStr = odType.ToString();
list1.Add("[" + colName + "]");
list2.Add("@" + colName);
list3.Add("[" + colName + "] " + GetOleDbTypeString(oi.Name, oi.Tag));
cmd.Parameters.Add("@" + colName, GetOleDbType(oi.Name), oi.Tag, colName);
}
}
}
cmd.CommandText = string.Format("CREATE TABLE [{0}]({1})", tabName, string.Join(",", list3.ToArray()));
cmd.ExecuteNonQuery();
cmd.CommandText = string.Format("INSERT INTO [{0}] ({1}) VALUES({2})", tabName, string.Join(",", list1.ToArray()), string.Join(",", list2.ToArray()));
cmd.UpdatedRowSource = UpdateRowSource.None;
int count = ada.Update(dt);
if (count > 0)
{
log.WriteLine("成功");
}
else
{
log.WriteLine("失败");
}
}
}
catch (Exception ex)
{
log.WriteLine(ex.Message);
}
finally
{
conn.Close();
}
}
}
private static CodeExpression BuildNewOleDbParameterStatement(DesignParameter parameter)
{
OleDbParameter parameter2 = new OleDbParameter();
OleDbType oleDbType = OleDbType.Char;
bool flag = false;
if ((parameter.ProviderType != null) && (parameter.ProviderType.Length > 0))
{
try
{
oleDbType = (OleDbType)Enum.Parse(typeof(OleDbType), parameter.ProviderType);
flag = true;
}
catch
{
}
}
if (!flag)
{
parameter2.DbType = parameter.DbType;
oleDbType = parameter2.OleDbType;
}
return(NewParameter(parameter, typeof(OleDbParameter), typeof(OleDbType), oleDbType.ToString()));
}
public override DataDomain BuildDomain()
{
DataDomain domain = new DataDomain(this.Driver.DomainUrl);
using (IDbConnection connection = this.Driver.OpenConnection())
{
DataTable schemaTable = ((OleDbConnection)connection).GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[0]);
DataColumn tableTypeColumn = schemaTable.Columns["TABLE_TYPE"];
DataColumn tableNameColumn = schemaTable.Columns["TABLE_NAME"];
foreach (DataRow schemaRowTable in schemaTable.Rows)
{
if (string.Compare(schemaRowTable[tableTypeColumn].ToString(), "TABLE") == 0)
{
string tableName = schemaRowTable[tableNameColumn].ToString();
Table table = new Table(tableName);
DataTable schemaColumns = ((OleDbConnection)connection).GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName });
DataColumn ordinalPosition = schemaColumns.Columns["ORDINAL_POSITION"];
DataColumn dataType = schemaColumns.Columns["DATA_TYPE"];
DataColumn columnNameColumn = schemaColumns.Columns["COLUMN_NAME"];
DataColumn numericPrecisionColumn = schemaColumns.Columns["NUMERIC_PRECISION"];
DataColumn allowDBNull = schemaColumns.Columns["IS_NULLABLE"];
DataColumn columnSize = schemaColumns.Columns["CHARACTER_MAXIMUM_LENGTH"];
DataColumn numericScale = schemaColumns.Columns["NUMERIC_SCALE"];
DataColumn descriptionColumn = schemaColumns.Columns["DESCRIPTION"];
schemaColumns.DefaultView.Sort = ordinalPosition.ColumnName;
foreach (DataRowView schemaRowColumn in schemaColumns.DefaultView)
{
string columnName = schemaRowColumn[columnNameColumn.Ordinal].ToString();
Column column = new Column(columnName);
column.IsNullable = (bool)schemaRowColumn[allowDBNull.Ordinal];
OleDbType oleDbType = (OleDbType)schemaRowColumn[dataType.Ordinal];
column.OriginalSQLType = oleDbType.ToString();
if (_sqlTypesDictionary.Contains(oleDbType))
{
column.SqlType = (SqlType)_sqlTypesDictionary[oleDbType];
}
else
{
column.SqlType = SqlType.Unknown;
}
if (_netDataTypes.ContainsKey(column.SqlType))
{
column.NetDataType = (string)_netDataTypes[column.SqlType];
column.ActionScriptType = TypeMapper.GetActionScriptType(_netDataTypes[column.SqlType] as string);
}
else
{
column.NetDataType = "unknown";
}
if (schemaRowColumn[numericPrecisionColumn.Ordinal] != DBNull.Value)
{
column.Length = Convert.ToInt32(schemaRowColumn[numericPrecisionColumn.Ordinal]);
}
if ((column.SqlType == SqlType.VarChar) || (column.SqlType == SqlType.VarBinary) || (column.SqlType == SqlType.Binary))
{
column.Length = Convert.ToInt32(schemaRowColumn[columnSize.Ordinal]);
if (column.SqlType == SqlType.VarChar)
{
column.SqlType = SqlType.Text;
//column.Length = 0;
}
else
{
column.IsBlob = true;
}
}
else if (column.SqlType == SqlType.Decimal)
{
column.Length = Convert.ToInt32(schemaRowColumn[numericPrecisionColumn.Ordinal]);
column.Scale = Convert.ToInt32(schemaRowColumn[numericScale.Ordinal]);
}
if (schemaRowColumn[descriptionColumn.Ordinal] != DBNull.Value)
{
string description = schemaRowColumn[descriptionColumn.Ordinal] as string;
if (description == "AutoNumber")
{
column.IsIdentity = true;
}
}
table.AddColumn(column);
}
DataTable schemaPrimaryKeys = ((OleDbConnection)connection).GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object [] { null, null, tableName });
DataColumn pkColumnNameColumn = schemaColumns.Columns["COLUMN_NAME"];
foreach (DataRowView schemaRowPK in schemaPrimaryKeys.DefaultView)
{
string columnName = (string)schemaRowPK[pkColumnNameColumn.Ordinal];
Column column = table[columnName];
if (column != null)
{
column.IsPrimaryKey = true;
}
}
domain.AddTable(table);
}
}
foreach (DataRow schemaRowTable in schemaTable.Rows)
{
if (string.Compare(schemaRowTable[tableTypeColumn].ToString(), "TABLE") == 0)
{
string tableName = schemaRowTable[tableNameColumn].ToString();
DataTable schemaForeignKeys = ((OleDbConnection)connection).GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[0]);
DataColumn constraintName = schemaForeignKeys.Columns["FK_NAME"];
DataColumn columnOrdinal = schemaForeignKeys.Columns["ORDINAL"];
DataColumn childTableName = schemaForeignKeys.Columns["FK_TABLE_NAME"];
DataColumn parentColumnName = schemaForeignKeys.Columns["FK_COLUMN_NAME"];
DataColumn updateRule = schemaForeignKeys.Columns["UPDATE_RULE"];
DataColumn deleteRule = schemaForeignKeys.Columns["DELETE_RULE"];
DataColumn parentTableName = schemaForeignKeys.Columns["PK_TABLE_NAME"];
DataColumn childColumnName = schemaForeignKeys.Columns["PK_COLUMN_NAME"];
schemaForeignKeys.DefaultView.Sort = constraintName + "," + columnOrdinal.ColumnName;
schemaForeignKeys.DefaultView.RowFilter = childTableName.ColumnName + " = '" + tableName + "'";
foreach (DataRowView schemaRowFK in schemaForeignKeys.DefaultView)
{
string parentTable = schemaRowFK[parentTableName.Ordinal].ToString();
string primaryKeyColumnName = schemaRowFK[childColumnName.Ordinal].ToString();
}
}
}
}
return(domain);
}
internal static Exception UninitializedParameters(int index, OleDbType dbtype)
{
return ADP.InvalidOperation(Res.GetString("OleDb_UninitializedParameters", new object[] { index.ToString(CultureInfo.InvariantCulture), dbtype.ToString() }));
}
/// <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));
}
protected void btnGo_Click(object sender, EventArgs e)
{
// create a Dictionary of types using the common DBType, add more if required
Dictionary <OleDbType, dbTypesDef> _ColDBTypes = new Dictionary <OleDbType, dbTypesDef>();
_ColDBTypes.Add(OleDbType.Binary, new dbTypesDef {
typeName = "bool", typeNil = "false", typeConvert = "Convert.ToBoolean"
});
_ColDBTypes.Add(OleDbType.Boolean, new dbTypesDef {
typeName = "bool", typeNil = "false", typeConvert = "Convert.ToBoolean"
});
_ColDBTypes.Add(OleDbType.BigInt, new dbTypesDef {
typeName = "long", typeNil = "0", typeConvert = "Convert.ToInt64"
});
_ColDBTypes.Add(OleDbType.UnsignedBigInt, new dbTypesDef {
typeName = "long", typeNil = "0", typeConvert = "Convert.ToInt64"
});
_ColDBTypes.Add(OleDbType.UnsignedTinyInt, new dbTypesDef {
typeName = "byte", typeNil = "0", typeConvert = "Convert.ToInt64"
});
_ColDBTypes.Add(OleDbType.TinyInt, new dbTypesDef {
typeName = "int16", typeNil = "0", typeConvert = "Convert.ToInt16"
});
_ColDBTypes.Add(OleDbType.Integer, new dbTypesDef {
typeName = "int", typeNil = "0", typeConvert = "Convert.ToInt32"
});
_ColDBTypes.Add(OleDbType.Currency, new dbTypesDef {
typeName = "double", typeNil = "0.0", typeConvert = "Convert.ToDouble"
});
_ColDBTypes.Add(OleDbType.Date, new dbTypesDef {
typeName = "DateTime", typeNil = "System.DateTime.Now", typeConvert = "Convert.ToDateTime"
});
_ColDBTypes.Add(OleDbType.DBDate, new dbTypesDef {
typeName = "DateTime", typeNil = "System.DateTime.Now", typeConvert = "Convert.ToDateTime"
});
_ColDBTypes.Add(OleDbType.DBTime, new dbTypesDef {
typeName = "DateTime", typeNil = "System.DateTime.Now", typeConvert = "Convert.ToDateTime"
});
_ColDBTypes.Add(OleDbType.Double, new dbTypesDef {
typeName = "double", typeNil = "0.0", typeConvert = "Convert.ToDouble"
});
_ColDBTypes.Add(OleDbType.Guid, new dbTypesDef {
typeName = "long", typeNil = "0", typeConvert = "Convert.ToInt64"
});
_ColDBTypes.Add(OleDbType.Char, new dbTypesDef {
typeName = "string", typeNil = "string.Empty", typeConvert = ""
});
_ColDBTypes.Add(OleDbType.WChar, new dbTypesDef {
typeName = "string", typeNil = "string.Empty", typeConvert = ""
});
_ColDBTypes.Add(OleDbType.VarChar, new dbTypesDef {
typeName = "string", typeNil = "string.Empty", typeConvert = ""
});
_ColDBTypes.Add(OleDbType.LongVarChar, new dbTypesDef {
typeName = "string", typeNil = "string.Empty", typeConvert = ""
});
_ColDBTypes.Add(OleDbType.LongVarWChar, new dbTypesDef {
typeName = "string", typeNil = "string.Empty", typeConvert = ""
});
_ColDBTypes.Add(OleDbType.Single, new dbTypesDef {
typeName = "double", typeNil = "0.0", typeConvert = "Convert.ToDouble"
});
_ColDBTypes.Add(OleDbType.SmallInt, new dbTypesDef {
typeName = "int16", typeNil = "0", typeConvert = "Convert.ToInt16"
});
_ColDBTypes.Add(OleDbType.Numeric, new dbTypesDef {
typeName = "double", typeNil = "0.0", typeConvert = "Convert.ToDouble"
});
_ColDBTypes.Add(OleDbType.Decimal, new dbTypesDef {
typeName = "double", typeNil = "0.0", typeConvert = "Convert.ToDouble"
});
_ColDBTypes.Add(OleDbType.IUnknown, new dbTypesDef {
typeName = "var", typeNil = "null", typeConvert = ""
});
_ColDBTypes.Add(OleDbType.Empty, new dbTypesDef {
typeName = "var", typeNil = "null", typeConvert = ""
});
// open a file
string _fName = "c:\\temp\\" + tbxORMClassFileName.Text;
StreamWriter _ColsStream = new StreamWriter(_fName, false); // create new file
// first Write Header information
_ColsStream.WriteLine("/// --- auto generated class for table: " + ddlTables.SelectedValue);
_ColsStream.WriteLine("using System; // for DateTime variables");
_ColsStream.WriteLine("using System.Collections.Generic; // for data stuff");
_ColsStream.WriteLine("using System.Data.OleDb;");
_ColsStream.WriteLine("using System.Configuration;");
_ColsStream.WriteLine();
_ColsStream.WriteLine("namespace TrackerDotNet.classes"); // modify this if you gonna store in different location
_ColsStream.WriteLine("{");
_ColsStream.WriteLine(" public class " + ddlTables.SelectedValue + "Data");
_ColsStream.WriteLine(" {");
_ColsStream.WriteLine(" // internal variable declarations");
// ExportTableColumns to file
OleDbConnection _ColsConn = OpenTrackerOleDBConnection();
try
{
_ColsConn.Open();
DataTable _ColsSchema = _ColsConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, ddlTables.SelectedValue, null });
// sort the schema ordinally so that the column names are in the same order as they appear in the database
DataRow[] _rows = _ColsSchema.Select(null, "ORDINAL_POSITION", DataViewRowState.CurrentRows);
foreach (DataRow _row in _rows)
{
// for do the private definitions
OleDbType _thisType = GetOleDBType(_row["DATA_TYPE"].ToString());
_ColsStream.Write(" private ");
if (_ColDBTypes.ContainsKey(_thisType))
{
_ColsStream.Write(_ColDBTypes[_thisType].typeName);
}
else
{
_ColsStream.Write(_thisType.ToString());
}
_ColsStream.WriteLine(" _" + _row["COLUMN_NAME"].ToString() + ";");
}
// now define the class initializer
_ColsStream.WriteLine(" // class definition");
_ColsStream.WriteLine(" public " + ddlTables.SelectedValue + "Data()");
_ColsStream.WriteLine(" {");
// now do the intialization class;
foreach (DataRow _row in _rows)
{
// for do the private definitions
OleDbType _thisType = GetOleDBType(_row["DATA_TYPE"].ToString());
if (_ColDBTypes.ContainsKey(_thisType))
{
_ColsStream.WriteLine(String.Format(" _{0} = {1};", _row["COLUMN_NAME"].ToString(), _ColDBTypes[_thisType].typeNil));
}
else
{
_ColsStream.WriteLine(String.Format(" _{0} = {1};", _row["COLUMN_NAME"].ToString(), "1"));
}
}
_ColsStream.WriteLine(" }");
// now each get and set
_ColsStream.WriteLine(" // get and sets of public");
foreach (DataRow _row in _rows)
{
// for do the private definitions
OleDbType _thisType = GetOleDBType(_row["DATA_TYPE"].ToString());
_ColsStream.Write(" public ");
if (_ColDBTypes.ContainsKey(_thisType))
{
_ColsStream.Write(_ColDBTypes[_thisType].typeName);
}
else
{
_ColsStream.Write(_thisType.ToString());
}
_ColsStream.Write(" " + _row["COLUMN_NAME"].ToString());
_ColsStream.Write(" { get { return _");
_ColsStream.Write(_row["COLUMN_NAME"].ToString());
_ColsStream.Write(";} set { _");
_ColsStream.Write(_row["COLUMN_NAME"].ToString());
_ColsStream.WriteLine(" = value;} }");
}
// close class
_ColsStream.WriteLine(" }");
// now a list all class with constants defining the SQL
_ColsStream.WriteLine(" public class " + ddlTables.SelectedValue + "DAL");
_ColsStream.WriteLine(" {");
_ColsStream.WriteLine(" #region ConstantDeclarations");
_ColsStream.WriteLine(" const string CONST_CONSTRING = \"" + CONST_CONSTRING + "\";");
_ColsStream.Write(" const string CONST_SQL_SELECT = \"SELECT ");
// add each line
string _selectRows = "";
foreach (DataRow _row in _rows)
{
_selectRows += _row["COLUMN_NAME"].ToString() + ", ";
}
_selectRows = _selectRows.Remove(_selectRows.Length - 2, 2);
_selectRows += " FROM " + ddlTables.SelectedValue + "\";";
_ColsStream.WriteLine(_selectRows);
_ColsStream.WriteLine(" #endregion");
_ColsStream.WriteLine();
_ColsStream.WriteLine(" public List<" + ddlTables.SelectedValue + "Data> GetAll(string SortBy)");
_ColsStream.WriteLine(" {");
_ColsStream.WriteLine(" List<" + ddlTables.SelectedValue + "Data> _DataItems = new List<" + ddlTables.SelectedValue + "Data>();");
_ColsStream.WriteLine(" string _connectionStr = ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString;");
_ColsStream.WriteLine();
_ColsStream.WriteLine(" using (OleDbConnection _conn = new OleDbConnection(_connectionStr))");
_ColsStream.WriteLine(" {");
_ColsStream.WriteLine(" string _sqlCmd = CONST_SQL_SELECT;");
_ColsStream.WriteLine(" if (!String.IsNullOrEmpty(SortBy)) _sqlCmd += \" ORDER BY \" + SortBy; // Add order by string");
_ColsStream.WriteLine(" OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the qurey we have built");
_ColsStream.WriteLine(" _conn.Open();");
_ColsStream.WriteLine(" OleDbDataReader _DataReader = _cmd.ExecuteReader();");
_ColsStream.WriteLine(" while (_DataReader.Read())");
_ColsStream.WriteLine(" {");
_ColsStream.WriteLine(" " + ddlTables.SelectedValue + "Data _DataItem = new " + ddlTables.SelectedValue + "Data();");
_ColsStream.WriteLine();
// for each item assign the value
foreach (DataRow _row in _rows)
{
OleDbType _thisType = GetOleDBType(_row["DATA_TYPE"].ToString());
_ColsStream.Write(" _DataItem." + _row["COLUMN_NAME"].ToString() + " = (_DataReader[\"" + _row["COLUMN_NAME"].ToString() + "\"");
_ColsStream.Write("] == DBNull.Value) ? " + _ColDBTypes[_thisType].typeNil + " : ");
if (String.IsNullOrEmpty(_ColDBTypes[_thisType].typeConvert))
{
_ColsStream.WriteLine("_DataReader[\"" + _row["COLUMN_NAME"].ToString() + "\"].ToString();");
}
else
{
_ColsStream.WriteLine(_ColDBTypes[_thisType].typeConvert + "(_DataReader[\"" + _row["COLUMN_NAME"].ToString() + "\"]);");
}
}
_ColsStream.WriteLine(" _DataItems.Add(_DataItem);");
//// ---- change Items _DataItems
_ColsStream.WriteLine(" }");
_ColsStream.WriteLine(" }");
_ColsStream.WriteLine(" return _DataItems;");
_ColsStream.WriteLine(" }");
_ColsStream.WriteLine(" }");
_ColsStream.WriteLine("}");
_ColsStream.Close();
}
catch (Exception ex)
{
throw new Exception("Error: " + ex.Message);
}
finally
{
_ColsConn.Close();
}
}
protected void btnGo_Click(object sender, EventArgs e)
{
// create a Dictionary of types using the common DBType, add more if required
//Dictionary<OleDbType, dbTypesDef>
_ColDBTypes = GetColDBTypes(); // allocate the values to the private variable
// open a file
string _fName = "c:\\temp\\" + tbxORMClassFileName.Text;
_ColsStream = new StreamWriter(_fName, false); // create new file
// first Write Header information
_ColsStream.WriteLine("/// --- auto generated class for table: " + ddlTables.SelectedValue);
_ColsStream.WriteLine("using System; // for DateTime variables");
_ColsStream.WriteLine("using System.Collections.Generic; // for data stuff");
// _ColsStream.WriteLine("using System.Data.OleDb;");
_ColsStream.WriteLine("using System.Data; // for IDataReader and DbType");
_ColsStream.WriteLine("using QOnT.classes; // TrackerDot classes used for DB access");
_ColsStream.WriteLine();
_ColsStream.WriteLine("namespace " + CONST_NAMESPACE); // modify this if you gonna store in different location
_ColsStream.WriteLine("{");
_ColsStream.WriteLine(" public class " + ddlTables.SelectedValue);
_ColsStream.WriteLine(" {");
_ColsStream.WriteLine(" #region InternalVariableDeclarations");
// ExportTableColumns to file
DataRow[] _rows = GetDBRowDefinitions();
foreach (DataRow _row in _rows)
{
// for do the private definitions
OleDbType _thisType = GetOleDBType(_row["DATA_TYPE"].ToString());
_ColsStream.Write(" private ");
if (_ColDBTypes.ContainsKey(_thisType))
{
_ColsStream.Write(_ColDBTypes[_thisType].typeName);
}
else
{
_ColsStream.Write(_thisType.ToString());
}
_ColsStream.WriteLine(" _" + _row["COLUMN_NAME"].ToString() + ";");
}
// now define the class initializer
_ColsStream.WriteLine(" #endregion");
_ColsStream.WriteLine();
_ColsStream.WriteLine(" // class definition");
_ColsStream.WriteLine(" public " + ddlTables.SelectedValue + "()");
_ColsStream.WriteLine(" {");
// now do the intialization class;
foreach (DataRow _row in _rows)
{
// for do the private definitions
OleDbType _thisType = GetOleDBType(_row["DATA_TYPE"].ToString());
if (_ColDBTypes.ContainsKey(_thisType))
{
_ColsStream.WriteLine(String.Format(" _{0} = {1};", _row["COLUMN_NAME"].ToString(), _ColDBTypes[_thisType].typeNil));
}
else
{
_ColsStream.WriteLine(String.Format(" _{0} = {1};", _row["COLUMN_NAME"].ToString(), "1"));
}
}
_ColsStream.WriteLine(" }");
// now each get and set
_ColsStream.WriteLine(" #region PublicVariableDeclarations");
foreach (DataRow _row in _rows)
{
// for do the private definitions
OleDbType _thisType = GetOleDBType(_row["DATA_TYPE"].ToString());
_ColsStream.Write(" public ");
if (_ColDBTypes.ContainsKey(_thisType))
{
_ColsStream.Write(_ColDBTypes[_thisType].typeName);
}
else
{
_ColsStream.Write(_thisType.ToString());
}
_ColsStream.Write(" " + _row["COLUMN_NAME"].ToString());
_ColsStream.Write(" { get { return _");
_ColsStream.Write(_row["COLUMN_NAME"].ToString());
_ColsStream.Write(";} set { _");
_ColsStream.Write(_row["COLUMN_NAME"].ToString());
_ColsStream.WriteLine(" = value;} }");
}
_ColsStream.WriteLine(" #endregion");
_ColsStream.WriteLine();
// close class - decided to do everything in the one class.
//_ColsStream.WriteLine(" }");
//// now a list all class with constants defining the SQL
//_ColsStream.WriteLine(" public class " + ddlTables.SelectedValue + "DAL");
//_ColsStream.WriteLine(" {");
_ColsStream.WriteLine(" #region ConstantDeclarations");
// _ColsStream.WriteLine(" const string CONST_CONSTRING = \"" + CONST_CONSTRING + "\";");
_ColsStream.WriteLine(GetSELECTstring(_rows));
_ColsStream.WriteLine(GetINSERTstring(_rows));
_ColsStream.WriteLine(GetUPDATEstring(_rows));
_ColsStream.WriteLine(GetDELETEstring(_rows));
_ColsStream.WriteLine(" #endregion");
_ColsStream.WriteLine();
WriteGetAllProc(_rows);
WriteInsertProc(_rows);
WriteUpdateProc(_rows);
WriteDeleteProc(_rows);
_ColsStream.WriteLine(" }");
_ColsStream.WriteLine("}");
_ColsStream.Close();
}