private void CreateTable(DataTable schemaTable)
{
var worksheets = _excelPackage.Workbook.Worksheets;
var tableName = $"Table{worksheets.Count + 1}";
_excelWorksheet = worksheets.Add(tableName);
var cells = _excelWorksheet.Cells;
var columnIndex = 1;
foreach (DataRow schemaRow in schemaTable.Rows)
{
var dataColumnSchema = FoundationDbColumnFactory.Create(schemaRow);
var columnName = dataColumnSchema.ColumnName;
var columnSize = dataColumnSchema.ColumnSize;
var dataType = _provider.GetColumnType(dataColumnSchema);
var cell = cells[1, columnIndex];
cell.Value = columnName;
cell.Style.Font.Bold = true;
columnIndex++;
}
_excelWorksheet.View.FreezePanes(2, 1);
_rowCount = 2;
}
string IProvider.GetColumnTypeName(IProvider sourceProvider, DataRow sourceSchemaRow, string sourceDataTypeName)
{
var schemaRow = FoundationDbColumnFactory.Create(sourceSchemaRow);
var columnSize = schemaRow.ColumnSize;
var allowDbNull = schemaRow.AllowDbNull;
var dataType = schemaRow.DataType;
var typeCode = Type.GetTypeCode(dataType);
string typeName;
switch (typeCode)
{
case TypeCode.Int32:
typeName = SqlDataTypeName.Int;
break;
case TypeCode.DateTime:
typeName = SqlDataTypeName.DateTime;
break;
case TypeCode.Double:
typeName = SqlDataTypeName.Float;
break;
case TypeCode.String:
typeName = $"{SqlDataTypeName.NVarChar}({columnSize})";
break;
default:
// TODO
typeName = $"'{typeCode}'";
break;
}
return(typeName);
}
void IResultWriter.WriteTableBegin(DataTable schemaTable)
{
_logResultWriter.WriteTableBegin(schemaTable);
if (isJsonAuto)
{
var column = FoundationDbColumnFactory.Create(schemaTable.Rows[0]);
isJsonAuto = column.DataType == typeof(string);
}
if (isJsonAuto)
{
Assert.IsNull(_path);
var identifier = LocalTime.Default.Now.ToString("yyyy-MM-dd HHmmss.fff");
var tempPath = Path.GetTempPath();
_path = Path.Combine(tempPath, identifier + ".json");
_textWriter = new StreamWriter(_path, false, Encoding.UTF8);
_formattedPath = Path.Combine(tempPath, identifier + "formatted.json");
_addInfoMessage(InfoMessageFactory.Create(InfoMessageSeverity.Information, null, $"Formatted JSON file: {_formattedPath}"));
}
else
{
_addInfoMessage(InfoMessageFactory.Create(InfoMessageSeverity.Error, null, "This is not FOR JSON AUTO"));
}
}
private void CreateTable(DataTable schemaTable)
{
var tableIndex = DataSet.Tables.Count;
var tableName = schemaTable.TableName;
if (tableName == "SchemaTable")
{
tableName = $"Table {tableIndex}";
}
if (_showShemaTable)
{
schemaTable.TableName = $"Schema {tableIndex}";
DataSet.Tables.Add(schemaTable);
}
_dataTable = DataSet.Tables.Add();
if (!string.IsNullOrEmpty(tableName))
{
_dataTable.TableName = tableName;
}
foreach (DataRow schemaRow in schemaTable.Rows)
{
var dataColumnSchema = FoundationDbColumnFactory.Create(schemaRow);
var columnName = dataColumnSchema.ColumnName;
var columnSize = dataColumnSchema.ColumnSize;
var dataType = _provider.GetColumnType(dataColumnSchema);
DataColumn dataColumn;
var n = 2;
var columnName2 = columnName;
while (true)
{
if (_dataTable.Columns.Contains(columnName2))
{
columnName2 = columnName + n;
n++;
}
else
{
columnName = columnName2;
if (dataType != null)
{
dataColumn = _dataTable.Columns.Add(columnName, dataType);
}
else
{
dataColumn = _dataTable.Columns.Add(columnName);
}
dataColumn.ExtendedProperties.Add("ColumnName", dataColumnSchema.ColumnName);
//dataColumn.AllowDBNull = sr.AllowDBNull == true;
//dataColumn.Unique = sr.IsUnique == true; // TFS provider does not support this column
dataColumn.ExtendedProperties.Add(0, schemaRow["DataType"]);
break;
}
}
}
}
public SqlCeDataReaderHelper(SqlCeDataReader dataReader)
{
this._dataReader = dataReader;
var schemaTable = dataReader.GetSchemaTable();
if (schemaTable != null)
{
var schemaRows = schemaTable.Rows;
var count = schemaRows.Count;
_dataFieldReaders = new IDataFieldReader[count];
for (var i = 0; i < count; i++)
{
var schemaRow = FoundationDbColumnFactory.Create(schemaRows[i]);
var sqlCeType = (SqlCeType)schemaRows[i][SchemaTableColumn.ProviderType];
var sqlDbType = sqlCeType.SqlDbType;
IDataFieldReader dataFieldReader;
switch (sqlDbType)
{
case SqlDbType.Decimal:
dataFieldReader = new SqlDecimalFieldReader(dataReader, i);
break;
default:
dataFieldReader = new DefaultDataFieldReader(dataReader, i);
break;
}
_dataFieldReaders[i] = dataFieldReader;
}
}
}
DataTable IProvider.GetSchemaTable(IDataReader dataReader)
{
DataTable table = null;
var schemaTable = dataReader.GetSchemaTable();
if (schemaTable != null)
{
table = new DataTable("SchemaTable");
var columns = table.Columns;
columns.Add(" ", typeof(int));
columns.Add(" ", typeof(string));
columns.Add("Name", typeof(string));
columns.Add("Size", typeof(int));
columns.Add("DbType", typeof(string));
columns.Add("ProviderType", typeof(DbType));
columns.Add("DataType", typeof(Type));
for (var i = 0; i < schemaTable.Rows.Count; i++)
{
var row = schemaTable.Rows[i];
var dataColumnSchema = FoundationDbColumnFactory.Create(row);
var columnOrdinal = dataColumnSchema.ColumnOrdinal + 1;
var isKey = row.GetValueOrDefault <bool>("isKey");
var pk = string.Empty;
if (isKey)
{
pk = "PKEY";
}
var columnSize = dataColumnSchema.ColumnSize;
var dbType = (DbType)row["ProviderType"];
var allowDbNull = (bool)row["AllowDBNull"];
var sb = new StringBuilder();
var dataTypeName = dataReader.GetDataTypeName(i);
sb.Append(dataTypeName);
if (!allowDbNull)
{
sb.Append(" NOT NULL");
}
table.Rows.Add(new[]
{
columnOrdinal,
pk,
row[SchemaTableColumn.ColumnName],
columnSize,
sb.ToString(),
dbType,
row["DataType"],
});
}
}
return(table);
}
private static DataGridViewColumn ToDataGridViewColumn(DataRow schemaDataRow)
{
var schema = FoundationDbColumnFactory.Create(schemaDataRow);
var column = new DataGridViewTextBoxColumn()
{
AutoSizeMode = DataGridViewAutoSizeColumnMode.None,
Name = schema.ColumnName
};
return(column);
}
public SqlDataReaderHelper(IDataReader dataReader)
{
_sqlDataReader = (SqlDataReader)dataReader;
var schemaTable = dataReader.GetSchemaTable();
if (schemaTable != null)
{
var rows = schemaTable.Rows;
var count = rows.Count;
_dataFieldReaders = new IDataFieldReader[count];
for (var i = 0; i < count; ++i)
{
_dataFieldReaders[i] = CreateDataFieldReader(dataReader, FoundationDbColumnFactory.Create(rows[i]));
}
}
}
string IProvider.GetColumnTypeName(IProvider sourceProvider, DataRow sourceSchemaRow, string sourceDataTypeName)
{
var schemaRow = FoundationDbColumnFactory.Create(sourceSchemaRow);
var columnSize = schemaRow.ColumnSize;
var allowDbNull = schemaRow.AllowDbNull;
string typeName;
switch (sourceDataTypeName.ToLower())
{
case SqlDataTypeName.Char:
case SqlDataTypeName.NChar:
case SqlDataTypeName.VarChar:
case SqlDataTypeName.NVarChar:
typeName = $"{sourceDataTypeName}({columnSize})";
break;
case SqlDataTypeName.Decimal:
var precision = schemaRow.NumericPrecision.Value;
var scale = schemaRow.NumericScale.Value;
if (scale == 0)
{
typeName = $"decimal({precision})";
}
else
{
typeName = $"decimal({precision},{scale})";
}
break;
case SqlDataTypeName.Xml:
typeName = $"nvarchar({int.MaxValue})";
break;
default:
typeName = sourceDataTypeName;
break;
}
return(typeName);
}
string IProvider.GetColumnTypeName(IProvider sourceProvider, DataRow sourceSchemaRow, string sourceDataTypeName)
{
var schemaRow = FoundationDbColumnFactory.Create(sourceSchemaRow);
var columnSize = schemaRow.ColumnSize;
var allowDbNull = schemaRow.AllowDbNull;
var dataType = schemaRow.DataType;
var typeCode = Type.GetTypeCode(dataType);
string typeName;
switch (typeCode)
{
case TypeCode.Boolean:
typeName = SqlDataTypeName.Bit;
break;
case TypeCode.Byte:
typeName = SqlDataTypeName.TinyInt;
break;
case TypeCode.DateTime:
typeName = "datetime";
break;
case TypeCode.Decimal:
var precision = schemaRow.NumericPrecision.Value;
var scale = schemaRow.NumericScale.Value;
if (precision > 38)
{
precision = 38;
}
if (scale > 38)
{
scale = 38;
}
if (precision < scale)
{
precision = scale;
}
if (scale == 0)
{
typeName = $"decimal({precision})";
}
else
{
typeName = $"decimal({precision},{scale})";
}
break;
case TypeCode.Double:
typeName = "float";
break;
case TypeCode.Int16:
typeName = "smallint";
break;
case TypeCode.Int32:
typeName = "int";
break;
case TypeCode.Int64:
typeName = "bigint";
break;
case TypeCode.Object:
if (sourceDataTypeName.ToLower() == SqlDataTypeName.Timestamp)
{
typeName = SqlDataTypeName.Timestamp;
}
else if (dataType == typeof(Guid))
{
typeName = "uniqueidentifier";
}
else if (dataType == typeof(byte[]))
{
if (columnSize <= 8000)
{
typeName = $"varbinary({columnSize})";
}
else
{
typeName = "image";
}
}
else
{
throw new NotImplementedException();
}
break;
case TypeCode.Single:
typeName = "real";
break;
case TypeCode.String:
bool isFixedLength;
var dataTypeNameUpper = sourceDataTypeName.ToUpper();
switch (sourceDataTypeName)
{
case "CHAR":
case "NCHAR":
isFixedLength = true;
break;
case "VARCHAR":
case "NVARCHAR":
case "VARCHAR2":
default:
isFixedLength = false;
break;
}
if (sourceProvider.Name == "System.Data.OracleClient")
{
columnSize /= 4;
}
if (columnSize <= 4000)
{
if (isFixedLength)
{
typeName = $"nchar({columnSize})";
}
else
{
typeName = $"nvarchar({columnSize})";
}
}
else
{
typeName = "ntext";
}
break;
default:
throw new NotImplementedException();
}
return(typeName);
}
DataTable IProvider.GetSchemaTable(IDataReader dataReader)
{
DataTable table = null;
var schemaTable = dataReader.GetSchemaTable();
if (schemaTable != null)
{
Log.Trace(CallerInformation.Get(), schemaTable.ToStringTableString());
Log.Trace(CallerInformation.Get(), "{0}", schemaTable.TableName);
table = new DataTable("SchemaTable");
var columns = table.Columns;
columns.Add(" ", typeof(int));
columns.Add(" ", typeof(string));
columns.Add("Name", typeof(string));
columns.Add("Size", typeof(int));
columns.Add("DbType", typeof(string));
columns.Add("DataType", typeof(Type));
var columnIndex = 0;
int?columnOrdinalAddition = null;
foreach (DataRow dataRow in schemaTable.Rows)
{
var dataColumnSchema = FoundationDbColumnFactory.Create(dataRow);
var columnOrdinal = dataColumnSchema.ColumnOrdinal;
if (columnOrdinalAddition == null)
{
if (columnOrdinal == 0)
{
columnOrdinalAddition = 1;
}
else
{
columnOrdinalAddition = 0;
}
}
var pk = string.Empty;
if (dataColumnSchema.IsKey == true)
{
pk = "PKEY";
}
if (dataColumnSchema.IsIdentity == true)
{
if (pk.Length > 0)
{
pk += ',';
}
pk += "IDENTITY";
}
var columnSize = dataColumnSchema.ColumnSize;
var dbType = (SqlDbType)dataColumnSchema.ProviderType;
var dataTypeName = dataReader.GetDataTypeName(columnIndex);
var sb = new StringBuilder();
sb.Append(dataTypeName);
switch (dbType)
{
case SqlDbType.Char:
case SqlDbType.VarChar:
case SqlDbType.NChar:
case SqlDbType.NVarChar:
case SqlDbType.Binary:
case SqlDbType.VarBinary:
string columnSizeString;
if (columnSize == int.MaxValue)
{
columnSizeString = "max";
}
else
{
columnSizeString = columnSize.ToString();
}
sb.AppendFormat("({0})", columnSizeString);
break;
case SqlDbType.Decimal:
var precision = dataColumnSchema.NumericPrecision.GetValueOrDefault();
var scale = dataColumnSchema.NumericScale.GetValueOrDefault();
if (scale == 0)
{
sb.AppendFormat("({0})", precision);
}
else
{
sb.AppendFormat("({0},{1})", precision, scale);
}
if (precision <= 9)
{
columnSize = 5;
}
else if (precision <= 19)
{
columnSize = 9;
}
else if (precision <= 28)
{
columnSize = 13;
}
else
{
columnSize = 17;
}
break;
}
var allowDbNull = dataColumnSchema.AllowDbNull.GetValueOrDefault();
if (!allowDbNull)
{
sb.Append(" not null");
}
table.Rows.Add(columnOrdinal + columnOrdinalAddition, pk, dataColumnSchema.ColumnName, columnSize, sb.ToString(),
dataColumnSchema.DataType);
columnIndex++;
}
}
return(table);
}
void IResultWriter.WriteTableBegin(DataTable schemaTable)
{
var createTable = new StringBuilder();
createTable.AppendFormat("create table [{0}]\r\n(\r\n", _tableName);
var insertInto = new StringBuilder();
insertInto.AppendFormat("insert into [{0}](", _tableName);
var values = new StringBuilder();
values.Append("values(");
var stringTable = new StringTable(3);
_insertCommand = _connection.CreateCommand();
var last = schemaTable.Rows.Count - 1;
for (var i = 0; i <= last; i++)
{
var dataRow = schemaTable.Rows[i];
var schemaRow = FoundationDbColumnFactory.Create(dataRow);
var columnName = schemaRow.ColumnName;
var columnSize = schemaRow.ColumnSize;
var allowDbNull = schemaRow.AllowDbNull;
var dataType = schemaRow.DataType;
var dataTypeName = "???";
var typeCode = Type.GetTypeCode(dataType);
string typeName;
SqlDbType sqlDbType;
switch (typeCode)
{
case TypeCode.Boolean:
typeName = "bit";
sqlDbType = SqlDbType.Bit;
break;
case TypeCode.DateTime:
typeName = "datetime";
sqlDbType = SqlDbType.DateTime;
break;
case TypeCode.Decimal:
sqlDbType = SqlDbType.Decimal;
var precision = schemaRow.NumericPrecision.Value;
var scale = schemaRow.NumericScale.Value;
if (precision > 38)
{
precision = 38;
}
if (scale > 38)
{
scale = 38;
}
if (precision < scale)
{
precision = scale;
}
if (scale == 0)
{
typeName = $"decimal({precision})";
}
else
{
typeName = $"decimal({precision},{scale})";
}
break;
case TypeCode.Double:
typeName = "float";
sqlDbType = SqlDbType.Float;
break;
case TypeCode.Int16:
typeName = "smallint";
sqlDbType = SqlDbType.SmallInt;
break;
case TypeCode.Int32:
typeName = "int";
sqlDbType = SqlDbType.Int;
break;
case TypeCode.Int64:
typeName = "bigint";
sqlDbType = SqlDbType.BigInt;
break;
case TypeCode.Single:
typeName = "real";
sqlDbType = SqlDbType.Real;
break;
case TypeCode.String:
bool isFixedLength;
var dataTypeNameUpper = dataTypeName.ToUpper();
switch (dataTypeName)
{
case "CHAR":
case "NCHAR":
isFixedLength = true;
break;
case "VARCHAR":
case "NVARCHAR":
case "VARCHAR2":
default:
isFixedLength = false;
break;
}
if (columnSize <= 4000)
{
if (isFixedLength)
{
typeName = $"nchar({columnSize})";
sqlDbType = SqlDbType.NChar;
}
else
{
typeName = $"nvarchar({columnSize})";
sqlDbType = SqlDbType.NVarChar;
}
}
else
{
typeName = "ntext";
sqlDbType = SqlDbType.NText;
}
break;
default:
throw new NotImplementedException();
}
var row = stringTable.NewRow();
row[1] = columnName;
row[2] = typeName;
if (allowDbNull != null && !allowDbNull.Value)
{
row[2] += " not null";
}
insertInto.Append(columnName);
values.Append('?');
if (i < last)
{
row[2] += ',';
insertInto.Append(',');
values.Append(',');
}
stringTable.Rows.Add(row);
var parameter = new SqlCeParameter(null, sqlDbType);
_insertCommand.Parameters.Add(parameter);
}
createTable.AppendLine(stringTable.ToString(4));
createTable.Append(')');
var commandText = createTable.ToString();
_messageWriter.WriteLine(commandText);
var executor = _connection.CreateCommandExecutor();
executor.ExecuteNonQuery(new CreateCommandRequest(commandText));
insertInto.Append(") ");
values.Append(')');
insertInto.Append(values);
var insertCommandText = insertInto.ToString();
_messageWriter.WriteLine(insertCommandText);
_insertCommand.CommandText = insertInto.ToString();
}
void IProvider.CreateInsertCommand(
DataTable sourceSchemaTable,
string[] sourceDataTypeNames,
IDbConnection destinationConnection,
string destinationTableName,
out IDbCommand insertCommand,
out Converter <object, object>[] converters)
{
DataTable schemaTable;
string[] dataTypeNames;
int count;
var sourceColumnNames =
from sourceSchemaRow in sourceSchemaTable.AsEnumerable()
select FoundationDbColumnFactory.Create(sourceSchemaRow).ColumnName;
using (var command = destinationConnection.CreateCommand())
{
command.CommandText = $"select {string.Join(",", sourceColumnNames)} from {destinationTableName}";
command.CommandType = CommandType.Text;
using (var dataReader = command.ExecuteReader(CommandBehavior.SchemaOnly))
{
schemaTable = dataReader.GetSchemaTable();
count = dataReader.FieldCount;
dataTypeNames = new string[count];
for (var i = 0; i < count; i++)
{
dataTypeNames[i] = dataReader.GetDataTypeName(i);
}
}
}
var insertInto = new StringBuilder();
insertInto.AppendFormat("insert into [{0}](", destinationTableName);
var values = new StringBuilder();
values.Append("values(");
var schemaRows = schemaTable.Rows;
count = schemaRows.Count;
converters = new Converter <object, object> [count];
insertCommand = destinationConnection.CreateCommand();
for (var i = 0; i < count; i++)
{
if (i > 0)
{
insertInto.Append(',');
values.Append(',');
}
var columnSchema = FoundationDbColumnFactory.Create(schemaRows[i]);
insertInto.AppendFormat("[{0}]", columnSchema.ColumnName);
values.AppendFormat("@p{0}", i);
var columnSize = columnSchema.ColumnSize;
var providerType = columnSchema.ProviderType;
var dbType = (DbType)providerType;
var parameter = new SqlParameter();
parameter.ParameterName = $"@p{i}";
//parameter.DbType = dbType;
insertCommand.Parameters.Add(parameter);
switch (dataTypeNames[i].ToLower())
{
case SqlDataTypeName.BigInt:
parameter.SqlDbType = SqlDbType.BigInt;
break;
case SqlDataTypeName.Bit:
parameter.SqlDbType = SqlDbType.Bit;
break;
case SqlDataTypeName.DateTime:
parameter.SqlDbType = SqlDbType.DateTime;
break;
case SqlDataTypeName.Float:
parameter.SqlDbType = SqlDbType.Float;
break;
case SqlDataTypeName.Int:
parameter.SqlDbType = SqlDbType.Int;
break;
case SqlDataTypeName.SmallDateTime:
parameter.SqlDbType = SqlDbType.SmallDateTime;
break;
case SqlDataTypeName.SmallInt:
parameter.SqlDbType = SqlDbType.SmallInt;
break;
case SqlDataTypeName.TinyInt:
parameter.SqlDbType = SqlDbType.TinyInt;
break;
case SqlDataTypeName.VarChar:
case SqlDataTypeName.NVarChar:
case SqlDataTypeName.Char:
case SqlDataTypeName.NChar:
parameter.Size = columnSchema.ColumnSize;
converters[i] = ConvertToString;
break;
case SqlDataTypeName.NText:
parameter.SqlDbType = SqlDbType.NText;
converters[i] = ConvertToString;
break;
case SqlDataTypeName.Decimal:
parameter.SqlDbType = SqlDbType.Decimal;
parameter.Precision = (byte)columnSchema.NumericPrecision.Value;
parameter.Scale = (byte)columnSchema.NumericScale.Value;
converters[i] = ConvertToDecimal;
break;
case SqlDataTypeName.Money:
parameter.SqlDbType = SqlDbType.Money;
converters[i] = ConvertToDecimal;
break;
case SqlDataTypeName.Xml:
parameter.SqlDbType = SqlDbType.Xml;
converters[i] = ConvertToString;
break;
}
}
insertInto.Append(") ");
values.Append(')');
insertInto.Append(values);
insertCommand.CommandText = insertInto.ToString();
}
DataTable IProvider.GetSchemaTable(IDataReader dataReader)
{
DataTable table = null;
var schemaTable = dataReader.GetSchemaTable();
if (schemaTable != null)
{
Log.Trace("\r\n" + schemaTable.ToStringTableString().ToString());
table = new DataTable("SchemaTable");
var columns = table.Columns;
columns.Add(" ", typeof(int));
columns.Add(" ", typeof(string));
columns.Add("Name", typeof(string));
columns.Add("Size", typeof(int));
columns.Add("DbType", typeof(string));
columns.Add("DataType", typeof(Type));
var columnIndex = 0;
int?columnOrdinalAddition = null;
foreach (DataRow dataRow in schemaTable.Rows)
{
var dataColumnSchema = FoundationDbColumnFactory.Create(dataRow);
var columnOrdinal = dataColumnSchema.ColumnOrdinal;
if (columnOrdinalAddition == null)
{
if (columnOrdinal == 0)
{
columnOrdinalAddition = 1;
}
else
{
columnOrdinalAddition = 0;
}
}
var pk = string.Empty;
if (dataColumnSchema.IsKey == true)
{
pk = "PKEY";
}
if (dataColumnSchema.IsIdentity == true)
{
if (pk.Length > 0)
{
pk += ',';
}
pk += "IDENTITY";
}
var columnSize = dataColumnSchema.ColumnSize;
var dbType = (MySqlDbType)dataColumnSchema.ProviderType;
var dataTypeName = dataReader.GetDataTypeName(columnIndex).ToLowerInvariant();
var sb = new StringBuilder();
sb.Append(dataTypeName);
switch (dbType)
{
case MySqlDbType.VarChar:
case MySqlDbType.Binary:
case MySqlDbType.VarBinary:
case MySqlDbType.String: // CHAR(n), enum
string columnSizeString;
if (columnSize == int.MaxValue)
{
columnSizeString = "max";
}
else
{
columnSizeString = columnSize.ToString();
}
sb.AppendFormat("({0})", columnSizeString);
break;
case MySqlDbType.Decimal:
var precision = dataColumnSchema.NumericPrecision.GetValueOrDefault();
var scale = dataColumnSchema.NumericScale.GetValueOrDefault();
if (scale == 0)
{
sb.AppendFormat("({0})", precision);
}
else
{
sb.AppendFormat("({0},{1})", precision, scale);
}
break;
case MySqlDbType.Byte:
case MySqlDbType.Int16:
case MySqlDbType.Int24:
case MySqlDbType.Int32:
case MySqlDbType.Int64:
sb.AppendFormat("({0})", columnSize);
break;
case MySqlDbType.UByte:
case MySqlDbType.UInt16:
case MySqlDbType.UInt24:
case MySqlDbType.UInt32:
case MySqlDbType.UInt64:
sb.AppendFormat("({0}) unsigned", columnSize);
break;
case MySqlDbType.Date:
break;
default:
break;
}
var allowDbNull = dataColumnSchema.AllowDbNull.GetValueOrDefault();
if (!allowDbNull)
{
sb.Append(" not null");
}
table.Rows.Add(new object[]
{
columnOrdinal + columnOrdinalAddition,
pk,
dataColumnSchema.ColumnName,
columnSize,
sb.ToString(),
dataColumnSchema.DataType
});
columnIndex++;
}
}
return(table);
}
void IProvider.CreateInsertCommand(
DataTable sourceSchemaTable,
string[] sourceDataTypeNames,
IDbConnection destinationconnection,
string destinationTableName,
out IDbCommand insertCommand,
out Converter <object, object>[] converters)
{
DataTable schemaTable;
string[] dataTypeNames;
int count;
using (var command = destinationconnection.CreateCommand())
{
command.CommandText = $"select * from {destinationTableName}";
//OracleDataAdapter adapter = new OracleDataAdapter( (OracleCommand)command );
//adapter.FillSchema(new DataSet(), SchemaType.Source);
//OracleCommandBuilder b = new OracleCommandBuilder( adapter );
//object o = b.GetInsertCommand();
using (var dataReader = command.ExecuteReader(CommandBehavior.SchemaOnly))
{
schemaTable = dataReader.GetSchemaTable();
count = dataReader.FieldCount;
dataTypeNames = new string[count];
for (var i = 0; i < count; i++)
{
dataTypeNames[i] = dataReader.GetDataTypeName(i);
}
}
}
var insertInto = new StringBuilder();
insertInto.AppendFormat("insert into {0}(", destinationTableName);
var values = new StringBuilder();
values.Append("values(");
var schemaRows = schemaTable.Rows;
count = schemaRows.Count;
converters = new Converter <object, object> [count];
insertCommand = destinationconnection.CreateCommand();
for (var i = 0; i < count; i++)
{
if (i > 0)
{
insertInto.Append(',');
values.Append(',');
}
var column = FoundationDbColumnFactory.Create(schemaRows[i]);
insertInto.Append(column.ColumnName);
values.AppendFormat(":p{0}", i + 1);
var columnSize = column.ColumnSize;
var oracleType = (OracleType)column.ProviderType;
var parameter = new OracleParameter($"p{i + 1}", oracleType);
insertCommand.Parameters.Add(parameter);
Converter <object, object> converter;
switch (oracleType)
{
case OracleType.Number:
converter = ConvertDecimalField;
break;
default:
converter = null;
break;
}
converters[i] = converter;
}
insertInto.Append(") ");
values.Append(')');
insertInto.Append(values);
insertCommand.CommandText = insertInto.ToString();
}
void IResultWriter.WriteTableBegin(DataTable schemaTable)
{
Trace.WriteLine(schemaTable.ToStringTableString());
var sb = new StringBuilder();
var schemaRows = schemaTable.Rows;
var schemaRowCount = schemaRows.Count;
string insertStatement = null;
var insertValues = new StringBuilder();
_insertCommand = new SQLiteCommand();
var st = new StringTable(3);
for (var i = 0; i < schemaRowCount; i++)
{
var schemaRow = FoundationDbColumnFactory.Create(schemaRows[i]);
var stringTableRow = st.NewRow();
if (i == 0)
{
var tableName = schemaRow.BaseTableName;
if (tableName != null)
{
_tableName = tableName;
}
_tableName = _tableName.Replace('.', '_');
_tableName = _tableName.Replace('[', '_');
_tableName = _tableName.Replace(']', '_');
sb.AppendFormat("CREATE TABLE {0}\r\n(\r\n", _tableName);
insertStatement = "INSERT INTO " + _tableName + '(';
insertValues.Append("VALUES(");
}
else
{
insertStatement += ',';
insertValues.Append(',');
}
var columnName = schemaRow.ColumnName;
stringTableRow[1] = columnName;
insertStatement += columnName;
insertValues.Append('?');
var columnSize = (int)schemaRow.ColumnSize;
var dataType = schemaRow.DataType;
var typeCode = Type.GetTypeCode(dataType);
string typeName;
DbType dbType;
switch (typeCode)
{
case TypeCode.Boolean:
typeName = "BOOLEAN";
dbType = DbType.Boolean;
break;
case TypeCode.DateTime:
typeName = "DATETIME";
dbType = DbType.DateTime;
break;
case TypeCode.Decimal:
var precision = schemaRow.NumericPrecision.Value;
var scale = schemaRow.NumericPrecision.Value;
if (precision <= 28 && scale <= 28)
{
typeName = $"DECIMAL({precision},{scale})";
dbType = DbType.Decimal;
}
else
{
typeName = $"-- DECIMAL({precision},{scale})";
dbType = DbType.Object;
}
break;
case TypeCode.Double:
typeName = "DOUBLE";
dbType = DbType.Double;
break;
case TypeCode.Int16:
typeName = "SMALLINT";
dbType = DbType.Int16;
break;
case TypeCode.Int32:
typeName = "INT";
dbType = DbType.Int32;
break;
case TypeCode.Int64:
typeName = "BIGINT";
dbType = DbType.Int64;
break;
case TypeCode.String:
typeName = $"VARCHAR({columnSize})";
dbType = DbType.String;
break;
default:
throw new NotImplementedException();
}
stringTableRow[2] = typeName;
st.Rows.Add(stringTableRow);
var allowDbNull = schemaRow.AllowDbNull.Value;
if (!allowDbNull)
{
stringTableRow[2] += " NOT NULL";
}
if (i < schemaRowCount - 1)
{
stringTableRow[2] += ',';
}
var parameter = new SQLiteParameter(dbType);
_insertCommand.Parameters.Add(parameter);
}
sb.Append(st.ToString(4));
sb.Append(')');
insertValues.Append(')');
insertStatement += ") " + insertValues;
var commandText = sb.ToString();
Trace.WriteLine(commandText);
Trace.WriteLine(insertStatement);
var executor = _connection.CreateCommandExecutor();
executor.ExecuteNonQuery(new CreateCommandRequest(commandText));
_transaction = _connection.BeginTransaction();
_insertCommand.Connection = _connection;
_insertCommand.Transaction = _transaction;
_insertCommand.CommandText = insertStatement;
}
void IProvider.CreateInsertCommand(
DataTable sourceSchemaTable,
string[] sourceDataTypeNames,
IDbConnection destinationconnection,
string destinationTableName,
out IDbCommand insertCommand,
out Converter <object, object>[] converters)
{
DataTable schemaTable;
string[] dataTypeNames;
int count;
using (var command = destinationconnection.CreateCommand())
{
command.CommandText = destinationTableName;
command.CommandType = CommandType.TableDirect;
using (var dataReader = command.ExecuteReader(CommandBehavior.SchemaOnly))
{
schemaTable = dataReader.GetSchemaTable();
count = dataReader.FieldCount;
dataTypeNames = new string[count];
for (var i = 0; i < count; i++)
{
dataTypeNames[i] = dataReader.GetDataTypeName(i);
}
}
}
var insertInto = new StringBuilder();
insertInto.AppendFormat("insert into [{0}](", destinationTableName);
var values = new StringBuilder();
values.Append("values(");
var schemaRows = schemaTable.Rows;
count = schemaRows.Count;
converters = new Converter <object, object> [count];
insertCommand = destinationconnection.CreateCommand();
for (var i = 0; i < count; i++)
{
if (i > 0)
{
insertInto.Append(',');
values.Append(',');
}
var columnSchema = FoundationDbColumnFactory.Create(schemaRows[i]);
insertInto.AppendFormat("[{0}]", columnSchema.ColumnName);
values.Append('?');
var columnSize = columnSchema.ColumnSize;
var sqlCeType = (SqlCeType)schemaRows[i]["ProviderType"];
var parameter = new SqlCeParameter(null, sqlCeType.SqlDbType);
insertCommand.Parameters.Add(parameter);
switch (dataTypeNames[i].ToLower())
{
case SqlDataTypeName.NText:
converters[i] = ConvertToString;
break;
case SqlDataTypeName.RowVersion:
converters[i] = ConvertToByteArray;
break;
default:
break;
}
}
insertInto.Append(") ");
values.Append(')');
insertInto.Append(values);
insertCommand.CommandText = insertInto.ToString();
}
public static string GetCreateTableStatement(DataTable schemaTable)
{
var sb = new StringBuilder();
sb.AppendFormat("create table {0}\r\n(\r\n", schemaTable.TableName);
var first = true;
foreach (DataRow schemaRow in schemaTable.Rows)
{
var dataColumnSchema = FoundationDbColumnFactory.Create(schemaRow);
if (first)
{
first = false;
}
else
{
sb.Append(",\r\n");
}
var columnSize = dataColumnSchema.ColumnSize;
var columnSizeString = columnSize == int.MaxValue ? "max" : columnSize.ToString();
var dataType = dataColumnSchema.DataType;
string dataTypeName;
var contains = schemaTable.Columns.Contains("DataTypeName");
if (contains)
{
dataTypeName = (string)schemaRow["DataTypeName"];
}
else
{
dataTypeName = GetDataTypeName(dataType);
}
switch (dataTypeName)
{
case SqlDataTypeName.Decimal:
dataTypeName += $"({dataColumnSchema.NumericPrecision},{dataColumnSchema.NumericScale})";
break;
case SqlDataTypeName.Char:
case SqlDataTypeName.NChar:
case SqlDataTypeName.NVarChar:
case SqlDataTypeName.VarChar:
dataTypeName += $"({columnSizeString})";
break;
default:
break;
}
sb.AppendFormat("\t{0} {1}", dataColumnSchema.ColumnName, dataTypeName);
if (dataColumnSchema.AllowDbNull == false)
{
sb.Append(" not null");
}
}
sb.Append("\r\n)");
return(sb.ToString());
}
void IProvider.CreateInsertCommand(
DataTable sourceSchemaTable,
string[] sourceDataTypeNames,
IDbConnection destinationconnection,
string destinationTableName,
out IDbCommand insertCommand,
out Converter <object, object>[] converters)
{
DataTable schemaTable;
string[] dataTypeNames;
int count;
using (var command = destinationconnection.CreateCommand())
{
command.CommandText = $"select * from {destinationTableName}";
command.CommandType = CommandType.Text;
using (var dataReader = command.ExecuteReader(CommandBehavior.SchemaOnly))
{
schemaTable = dataReader.GetSchemaTable();
count = dataReader.FieldCount;
dataTypeNames = new string[count];
for (var i = 0; i < count; i++)
{
var dataTypeName = dataReader.GetDataTypeName(i);
var index = dataTypeName.IndexOf('(');
if (index >= 0)
{
dataTypeName = dataTypeName.Substring(0, index);
}
dataTypeNames[i] = dataTypeName;
}
}
}
var insertInto = new StringBuilder();
insertInto.AppendFormat("insert into [{0}](", destinationTableName);
var values = new StringBuilder();
values.Append("values(");
var schemaRows = schemaTable.Rows;
count = schemaRows.Count;
converters = new Converter <object, object> [count];
insertCommand = destinationconnection.CreateCommand();
for (var i = 0; i < count; i++)
{
if (i > 0)
{
insertInto.Append(',');
values.Append(',');
}
var columnSchema = FoundationDbColumnFactory.Create(schemaRows[i]);
insertInto.AppendFormat("[{0}]", columnSchema.ColumnName);
values.Append('?');
var columnSize = columnSchema.ColumnSize;
var providerType = columnSchema.ProviderType;
var dbType = (DbType)providerType;
var parameter = new SQLiteParameter(dbType);
insertCommand.Parameters.Add(parameter);
switch (dataTypeNames[i].ToLower())
{
case SqlDataTypeName.VarChar:
case SqlDataTypeName.NVarChar:
case SqlDataTypeName.Char:
case SqlDataTypeName.NChar:
case SqlDataTypeName.NText:
converters[i] = ConvertToString;
break;
case SqlDataTypeName.Decimal:
case SqlDataTypeName.Money:
converters[i] = ConvertToDecimal;
break;
default:
break;
}
}
insertInto.Append(") ");
values.Append(')');
insertInto.Append(values);
insertCommand.CommandText = insertInto.ToString();
insertCommand.Prepare();
}
