public void TestExceptionIfSchemaChanges()
{
DataTable table = new DataTable();
table.Columns.Add("col1");
DataTableReader rdr = table.CreateDataReader();
Assert.AreEqual(1, rdr.GetSchemaTable().Rows.Count, "#1");
table.Columns [0].ColumnName = "newcol1";
try {
rdr.GetSchemaTable();
Assert.Fail("#0");
} catch (InvalidOperationException e) {
// Never premise English.
//Assert.AreEqual ("Schema of current DataTable '" + table.TableName +
// "' in DataTableReader has changed, DataTableReader is invalid.", e.Message, "#1");
}
rdr = table.CreateDataReader();
rdr.GetSchemaTable(); //no exception
table.Columns.Add("col2");
try {
rdr.GetSchemaTable();
Assert.Fail("#1");
} catch (InvalidOperationException e) {
// Never premise English.
//Assert.AreEqual ("Schema of current DataTable '" + table.TableName +
// "' in DataTableReader has changed, DataTableReader is invalid.", e.Message, "#1");
}
}
public void SchemaTest()
{
DataTable another = new DataTable("another");
another.Columns.Add("x", typeof(string));
another.Rows.Add(new object[] { "test 1" });
another.Rows.Add(new object[] { "test 2" });
another.Rows.Add(new object[] { "test 3" });
DataTableReader reader = new DataTableReader(new DataTable[] { _dt, another });
try
{
DataTable schema = reader.GetSchemaTable();
Assert.Equal(_dt.Columns.Count, schema.Rows.Count);
Assert.Equal(_dt.Columns[1].DataType.ToString(), schema.Rows[1]["DataType"].ToString());
reader.NextResult(); //schema should change here
schema = reader.GetSchemaTable();
Assert.Equal(another.Columns.Count, schema.Rows.Count);
Assert.Equal(another.Columns[0].DataType.ToString(), schema.Rows[0]["DataType"].ToString());
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
}
public DataTable fnGetBaseColumns(ref DataTable dtSchema)
{
try
{
DataTable dt = new DataTable();
string strSql = "";
if (Common.iDBType == (int)Enums.DBType.Oracle)
{
strSql = "select * from ETS_TRE_BASE2 where ROWNUM <= 2";
dt = ((OraDBManager)Common.dbMgr).ExecuteDataTable(CommandType.Text, strSql);
}
else
{
strSql = "select top 1 * from ETS_TRE_BASE2";
dt = ((DBManager)Common.dbMgr).ExecuteDataTable(CommandType.Text, strSql);
}
DataTableReader dr = new DataTableReader(dt);
dtSchema = dr.GetSchemaTable();
dr.Close();
dr = null;
return(dt);
//ets_tre_base
}
catch (Exception ex)
{
throw ex;
}
}
void bindingExpressionEditor(int iExpressionType)
{
try
{
clsDataSource clsDSOBJ = new clsDataSource();
// DataTable dt = clsDSOBJ.fnGetTreDetails(Common.strTableName);
DataTable dt = clsDSOBJ.fnGetTreDetails("Tre_Random");
DataTableReader dr = new DataTableReader(dt);
DataTable dtSchema = dr.GetSchemaTable();
using (var frm = new frmExpressEditor(iExpressionType, "Tre_Random", strPtnlFilter))
{
frm._fieldDict = Common.GetDict(dt);
frm.AvailableFields = frm._fieldDict.ToList <KeyValuePair <string, Type> >();
frm.dtSource = dtSchema;
var res = frm.ShowDialog();
if (res == System.Windows.Forms.DialogResult.OK)
{
strPtnlFilter = frm.strExpression;
Common.strPtnlFilter = strPtnlFilter;
}
}
}
catch (Exception ex)
{
Telerik.WinControls.RadMessageBox.Show(this, ex.Message, ex.TargetSite.Name.ToString(), MessageBoxButtons.OK, RadMessageIcon.Error, MessageBoxDefaultButton.Button1);
}
}
void bindingExpressionEditor(int iExpressionType)
{
try
{
// DataTable dt = clsDSOBJ.fnGetTreDetails("ETS_TRE_BASE3");
string tblQuery;
DataTable dt = clsObjcampaign.fnGetEffectiveTable(Common.iProjectID, out tblQuery, Common.strTableName);
DataTableReader dr = new DataTableReader(dt);
DataTable dtSchema = dr.GetSchemaTable();
// string strExpression = "";
using (var frm = new frmExpressEditor(iExpressionType, "(" + tblQuery + ")", strEligibility))
{
frm._fieldDict = Common.GetDict(dt);
frm.AvailableFields = frm._fieldDict.ToList <KeyValuePair <string, Type> >();
frm.dtSource = dtSchema;
var res = frm.ShowDialog();
if (res == System.Windows.Forms.DialogResult.OK)
{
Common.strfiltertxt = frm.strExpression;
}
}
}
catch (Exception ex)
{
Telerik.WinControls.RadMessageBox.Show(this, ex.Message, ex.TargetSite.Name.ToString(), MessageBoxButtons.OK, RadMessageIcon.Error, MessageBoxDefaultButton.Button1);
}
}
/// <summary>
/// Prints out the column header and rows of the data table.
/// </summary>
/// <param name="dt">A data table.</param>
public static void PrintDataTableToConsole(DataTable dt)
{
using (DataTableReader reader = new DataTableReader(dt))
{
PrintColumnNamesToConsole(reader.GetSchemaTable());
PrintDataReaderToConsole(reader);
}
}
/// <summary>
/// Read columns schema from database
/// </summary>
private List <DbColumn> ReadColumns(string tableName)
{
var result = new List <DbColumn>();
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
using (var adapter = new SQLiteDataAdapter(String.Format("SELECT * FROM {0} LIMIT 1 ", tableName), _dbConnection)) {
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataTable columnsSchema;
// Jjust to avoid stupid "Failed to enable constraints" error!
using (DataSet tempDs = new DataSet()) {
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
using (DataTable columnsList = new DataTable()) {
tempDs.Tables.Add(columnsList);
// Get from db
adapter.Fill(columnsList);
// Get schema
using (var reader = new DataTableReader(columnsList))
columnsSchema = reader.GetSchemaTable();
}
}
// Used to get columns Sql DataType
using (DataTable columnsDbTypeTable = _dbConnection.GetSchema("COLUMNS")) {
// Fetch the rows
foreach (DataRow dr in columnsSchema.Rows)
{
string columnName = dr["ColumnName"].ToString();
DbColumn column = new DbColumn(columnName, columnName)
{
DataTypeDotNet = dr["DataType"].ToString(),
Length = Convert.ToInt32(dr["ColumnSize"]),
PrimaryKey = Convert.ToBoolean(dr["IsKey"]),
AutoIncrement = Convert.ToBoolean(dr["IsAutoIncrement"]),
AllowNull = Convert.ToBoolean(dr["AllowDBNull"]),
ColumnOrdinal = Convert.ToInt32(dr["ColumnOrdinal"]),
};
column.FieldNameSchema = DbSchemaNames.FieldName_RemoveInvalidChars(column.FieldNameSchema);
// Columns which needs additional fetch
FillColumnAdditionalInfo(column, columnsDbTypeTable, tableName, columnName);
// Add to result
result.Add(column);
}
}
}
return(result);
}
public override DataTable GetTableInfo(string tableName = null)
{
try
{
String[] columnRestrictions = new String[4];
if (tableName == null)
{
columnRestrictions[2] = SelectedTable;
}
else
{
columnRestrictions[2] = tableName;
}
DataTable departmentIDSchemaTable = connection.GetSchema("Columns", columnRestrictions);
TableColumns.Clear();
DataView dv = departmentIDSchemaTable.DefaultView;
departmentIDSchemaTable.DefaultView.Sort = "ORDINAL_POSITION Asc";
var SortedView = dv.ToTable();
foreach (DataRow row in SortedView.Rows)
{
TableColumns.Add(new Column(row, DbType));
}
/////////
string _table = (tableName == null) ? SelectedTable : tableName;
SqlDataAdapter adapter = new
SqlDataAdapter("SELECT TOP 1 * FROM " + _table,
(connection as SqlConnection));
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataTable table = new DataTable();
adapter.Fill(table);
using (DataTableReader reader = new DataTableReader(table))
{
int ordinal = 0;
DataTable schemaTable = reader.GetSchemaTable();
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
if ((bool)schemaTable.Rows[i].ItemArray[12])
{
TableColumns[i].ISKey = true;
}
}
}
///////////////
return(departmentIDSchemaTable);
}
catch (Exception ex)
{
return(null);
}
}
public DataTable GetSchemaTable()
{
checkDisposed();
if (_schemaTable == null)
{
DataTableReader reader = new DataTableReader(_table);
_schemaTable = reader.GetSchemaTable();
}
return(_schemaTable);
}
public void TestExceptionIfSchemaChanges()
{
DataTable table = new DataTable();
table.Columns.Add("col1");
DataTableReader rdr = table.CreateDataReader();
Assert.Equal(1, rdr.GetSchemaTable().Rows.Count);
table.Columns[0].ColumnName = "newcol1";
// Schema of current DataTable '' in DataTableReader has changed, DataTableReader is invalid.
Assert.Throws <InvalidOperationException>(() => rdr.GetSchemaTable());
rdr = table.CreateDataReader();
rdr.GetSchemaTable(); //no exception
table.Columns.Add("col2");
// Schema of current DataTable '' in DataTableReader has changed, DataTableReader is invalid.
Assert.Throws <InvalidOperationException>(() => rdr.GetSchemaTable());
}
public static bool ExportExcelFile(DataTableReader reader, bool firstTime)
{
int c = 0;
DataTable ResultsData = new DataTable();
//Get the Columns names, types, this will help
//when we need to format the cells in the excel sheet.
DataTable dtSchema = reader.GetSchemaTable();
var listCols = new List <DataColumn>();
if (dtSchema != null)
{
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = Convert.ToString(drow["ColumnName"]);
var column = new DataColumn(columnName, (Type)(drow["DataType"]));
column.Unique = (bool)drow["IsUnique"];
column.AllowDBNull = (bool)drow["AllowDBNull"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
listCols.Add(column);
ResultsData.Columns.Add(column);
}
}
// Call Read before accessing data.
while (reader.Read())
{
DataRow dataRow = ResultsData.NewRow();
for (int i = 0; i < listCols.Count; i++)
{
dataRow[(listCols[i])] = reader[i];
}
ResultsData.Rows.Add(dataRow);
c++;
if (c == rowsPerSheet)
{
c = 0;
ExportToOxml(firstTime, ResultsData);
ResultsData.Clear();
firstTime = false;
}
}
if (ResultsData.Rows.Count > 0)
{
ExportToOxml(firstTime, ResultsData);
ResultsData.Clear();
}
// Call Close when done reading.
reader.Close();
return(firstTime);
}
private IEnumerable <FieldDefinition> GetFields(DataTableReader reader)
{
var result = new List <FieldDefinition>();
var schema = reader.GetSchemaTable();
var table = schema.TableName;
var idx = 0;
foreach (DataRow row in schema.Rows)
{
var field = new FieldDefinition();
field.TableName = table;
if (table.Contains("."))
{
var splits = table.Split('.');
field.TableName = splits[1];
field.ClassName = GetFieldName(splits[1]);
field.TableSchema = splits[0];
}
field.DbFieldName = row["ColumnName"].ToString();
field.Length = (int)row["ColumnSize"];
field.AllowsNull = (bool)row["AllowDBNull"];
if (field.DbFieldName.ToUpper().EndsWith("_ID") && idx == 0)
{
//this is the PK
field.Name = "Id";
}
else
{
field.Name = GetFieldName(field.DbFieldName);
}
field.Type = GetDataType(row["DataType"].ToString(), field.AllowsNull);
if (field.Type.StartsWith("short"))
{
if (MessageBox.Show(string.Format("[{0}] is this field intended to be used as a bool?", field.Name),
"Possible Bool Value",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question) == DialogResult.Yes)
{
field.IsShortBool = true;
}
}
result.Add(field);
idx++;
}
return(result);
}
/// <summary>
/// Test DataTableReader GetSchemaTable() method
/// </summary>
private static void Test_GetSchemaTable()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
string sql = "select * from athlete order by `code`";
CUBRIDDataAdapter adapter = new CUBRIDDataAdapter(sql, conn);
DataTable table = new DataTable();
//To retrieve the AlolowDBNull, IsUnique, IsKey, IsAutoIncrement and BaseTableName values from the Database Server
//you must use the FillSchema() method.
adapter.FillSchema(table, SchemaType.Source);
using (DataTableReader reader = new DataTableReader(table))
{
DataTable schemaTable = reader.GetSchemaTable();
DataRow row = schemaTable.Rows[0];
Debug.Assert(row["ColumnName"].ToString() == "code");
Debug.Assert(row["ColumnOrdinal"].ToString() == "0");
Debug.Assert(row["ColumnSize"].ToString() == "-1");
Debug.Assert(row["NumericPrecision"].ToString() == "");
Debug.Assert(row["NumericScale"].ToString() == "");
Debug.Assert(row["IsUnique"].ToString() == "True");
Debug.Assert(row["IsKey"].ToString() == "True");
Debug.Assert(row["BaseTableNamespace"].ToString() == "");
Debug.Assert(row["BaseColumnNamespace"].ToString() == "");
Debug.Assert(row["BaseCatalogName"].ToString() == "");
Debug.Assert(row["BaseColumnName"].ToString() == "code");
Debug.Assert(row["BaseSchemaName"].ToString() == "");
Debug.Assert(row["BaseTableName"].ToString() == "athlete");
Debug.Assert(row["DataType"].ToString() == "System.Int32");
Debug.Assert(row["AllowDBNull"].ToString() == "False");
Debug.Assert(row["ProviderType"].ToString() == "");
Debug.Assert(row["Expression"].ToString() == "");
Debug.Assert(row["AutoIncrementSeed"].ToString() == "0");
Debug.Assert(row["AutoincrementStep"].ToString() == "1");
Debug.Assert(row["IsAutoIncrement"].ToString() == "True");
Debug.Assert(row["IsRowVersion"].ToString() == "False");
Debug.Assert(row["IsLong"].ToString() == "False");
Debug.Assert(row["IsReadOnly"].ToString() == "False");
Debug.Assert(row["ColumnMapping"].ToString() == "1");
Debug.Assert(row["DefaultValue"].ToString() == "");
}
}
}
DataTable GetSchemaTable(SqlCommand cmd)
{
DataTable table = new DataTable();
table.Columns.Add("ColumnName", typeof(string));
table.Columns.Add("DataTypeName", typeof(string));
table.Columns.Add("Size", typeof(string));
table.Columns.Add("IsKey", typeof(bool));
table.Columns.Add("AllowDBNull", typeof(bool));
DataTable rawInfoTable = null;
using (var reader = cmd.ExecuteReader()) {
rawInfoTable = reader.GetSchemaTable();
foreach (DataRow row in rawInfoTable.Rows)
{
var newrow = table.NewRow();
table.Rows.Add(newrow);
newrow["ColumnName"] = row["ColumnName"];
newrow["DataTypeName"] = row["DataTypeName"];
newrow["Size"] = IsSizedColumn((string)row["DataTypeName"]) ? GetColumnSize((int)row["ColumnSize"]) : string.Empty;
}
}
table.AcceptChanges();
DataTable resolvedInfoTable = null;
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)) {
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataTable tempDataTable = new DataTable();
adapter.Fill(tempDataTable);
using (var reader = new DataTableReader(tempDataTable)) {
resolvedInfoTable = reader.GetSchemaTable();
foreach (DataRow row in resolvedInfoTable.Rows)
{
string select = string.Format("ColumnName = '{0}'", row["ColumnName"]);
DataRow resultRow = table.Select(select)[0];
resultRow["IsKey"] = row["IsKey"];
resultRow["AllowDBNull"] = row["AllowDBNull"];
}
}
}
return(table);
}
private static void DisplaySchemaTableInfo(
DataTableReader reader, int ordinal)
{
// Given a DataTableReader, display schema
// information about a particular column.
try
{
DataTable schemaTable = reader.GetSchemaTable();
DataRow row = schemaTable.Rows[ordinal];
foreach (DataColumn col in schemaTable.Columns)
{
Console.WriteLine("{0}: {1}",
col.ColumnName, row[col.Ordinal]);
}
}
catch (IndexOutOfRangeException ex)
{
Console.WriteLine("{0} is an invalid column number.",
ordinal);
}
}
private List <HeaderModel> GetColumnsInfo(DataTableReader reader)
{
var columnsInfo = new List <HeaderModel>();
var schema = reader.GetSchemaTable();
foreach (DataRow columnInfo in schema.Rows)
{
var columnName = columnInfo.ItemArray[0].ToString();
var columnType = columnInfo.ItemArray[5] as Type;
var columnTypeName = columnType == typeof(string) ? "string" :
columnType == typeof(float) ? "float" :
columnType == typeof(bool) ? "bool" :
columnType == typeof(DateTime) ? "date" : "int";
columnsInfo.Add(new HeaderModel
{
Name = columnName,
Type = columnTypeName
});
}
return(columnsInfo);
}
public Task <IList <Column> > GetTableDetails(Table table, string owner)
{
IList <Column> columns = new List <Column>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
var schema = new CUBRIDSchemaProvider(conn);
DataTable dt_fk = schema.GetForeignKeys(new[] { table.Name.ToLower() });
string sqlInfo = String.Format("select * from [{0}] limit 1", table.Name.ToLower());
var adapter = new CUBRIDDataAdapter(sqlInfo, conn);
var tableInfo = new DataTable();
adapter.FillSchema(tableInfo, SchemaType.Source);
using (var reader = new DataTableReader(tableInfo))
{
DataTable schemaTable = reader.GetSchemaTable();
for (var k = 0; k < schemaTable.Rows.Count; k++)
{
string columnName = schemaTable.Rows[k]["ColumnName"].ToString().ToLower();
var isUnique = (bool)schemaTable.Rows[k]["IsUnique"];
var isNullable = (bool)schemaTable.Rows[k]["AllowDBNull"];
var isPrimaryKey = (bool)schemaTable.Rows[k]["IsKey"];
var isIdentity = (bool)schemaTable.Rows[k]["IsAutoIncrement"];
var dataLength = (int)schemaTable.Rows[k]["ColumnSize"];
int dataPrecision = 0;
if (schemaTable.Rows[k]["NumericPrecision"].ToString() != String.Empty)
{
dataPrecision = (int)schemaTable.Rows[k]["NumericPrecision"];
}
int dataScale = 0;
if (schemaTable.Rows[k]["NumericScale"].ToString() != String.Empty)
{
dataScale = (int)schemaTable.Rows[k]["NumericScale"];
}
bool isForeignKey = false;
string fkTableName = "";
string constraintName = "";
for (var i_fk = 0; i_fk < dt_fk.Rows.Count; i_fk++)
{
if (dt_fk.Rows[i_fk]["FKCOLUMN_NAME"].ToString().ToLower() == columnName)
{
isForeignKey = true;
fkTableName = dt_fk.Rows[i_fk]["PKTABLE_NAME"].ToString();
constraintName = dt_fk.Rows[i_fk]["FK_NAME"].ToString();
break;
}
}
string dataType;
using (var cmd = new CUBRIDCommand(sqlInfo, conn))
{
using (var CUBRIDReader = (CUBRIDDataReader)cmd.ExecuteReader())
{
CUBRIDReader.Read();
dataType = CUBRIDReader.GetColumnTypeName(k);
}
}
var m = new DataTypeMapper();
columns.Add(new Column
{
Name = columnName,
DataType = dataType,
IsNullable = isNullable,
IsUnique = isUnique,
IsPrimaryKey = isPrimaryKey,
IsForeignKey = isForeignKey,
IsIdentity = isIdentity,
DataLength = dataLength,
DataPrecision = dataPrecision,
DataScale = dataScale,
ForeignKeyTableName = fkTableName,
ConstraintName = constraintName,
MappedDataType = m.MapFromDBType(ServerType.CUBRID, dataType, null, null, null),
});
}
}
}
table.Columns = columns;
table.Owner = owner;
table.PrimaryKey = DeterminePrimaryKeys(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
finally
{
conn.Close();
}
return(Task.FromResult(columns));
}
//private static void WaitForTaskPollingForCancellation(CancellationTokenSource cancellationTokenSource, Task task)
//{
// // poll every 1 second to see if the Cancel button has been clicked
// while (!task.Wait(1000))
// {
// if (CancelRequested)
// {
// cancellationTokenSource.Cancel();
// try
// {
// task.Wait();
// }
// catch (AggregateException ex)
// {
// Console.WriteLine(ex.InnerException.Message);
// Console.WriteLine("WriteToServer Canceled");
// break;
// }
// }
// if (task.IsCompleted || task.IsCompleted || task.IsFaulted) { break; }
// }
//}
private static void EnsureSQLTableExists(SqlConnection conn, string sqlTableName, DataTableReader reader, bool dropTables)
{
var strColumns = new StringBuilder();
var schemaTable = reader.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
var colName = row.Field <string>("ColumnName");
var regEx = System.Text.RegularExpressions.Regex.Match(colName, @".+\[(.+)\]");
if (regEx.Success)
{
colName = regEx.Groups[1].Value;
}
colName.Replace('|', '_');
var sqlType = ConvertDotNetToSQLType(row);
strColumns.AppendLine($",[{colName}] {sqlType} NULL");
}
string cmdText;
if (dropTables)
{
cmdText = @"
declare @sqlCmd nvarchar(max)
IF object_id(@tableName, 'U') is not null
BEGIN
raiserror('Droping Table ""%s""', 1, 1, @tableName)
set @sqlCmd = 'drop table if exists ' + @tableName + char(13)
exec sp_executesql @sqlCmd
END";
using (var cmd = new SqlCommand(cmdText, conn))
{
cmd.Parameters.AddWithValue("@tableName", sqlTableName);
cmd.Parameters.AddWithValue("@columns", strColumns.ToString().TrimStart(','));
cmd.ExecuteNonQuery();
}
}
cmdText = @"
declare @sqlCmd nvarchar(max)
IF object_id(@tableName, 'U') is null
BEGIN
declare @schemaName varchar(20)
set @sqlCmd = ''
set @schemaName = parsename(@tableName, 2)
IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = @schemaName)
BEGIN
set @sqlCmd = 'CREATE SCHEMA ' + @schemaName + char(13)
END
set @sqlCmd = @sqlCmd + 'CREATE TABLE ' + @tableName + '(' + @columns + ');'
raiserror('Creating Table ""%s""', 1, 1, @tableName)
exec sp_executesql @sqlCmd
END
ELSE
BEGIN
raiserror('Table ""%s"" already exists', 1, 1, @tableName)
END
";
using (var cmd = new SqlCommand(cmdText, conn))
{
cmd.Parameters.AddWithValue("@tableName", sqlTableName);
cmd.Parameters.AddWithValue("@columns", strColumns.ToString().TrimStart(','));
cmd.ExecuteNonQuery();
}
}
void bindingExpressionEditor(int iExpressionType, string CalaculatedColumnValue = "", string strColname = "")
{
try
{
clsDataSource clsObj = new clsDataSource();
DataTable dtSource = clsObj.fnGetColMappingData(Common.iProjectID);
DataTable dt = clsDSOBJ.fnGetTreDetails(Common.strTableName);
DataTableReader dr = new DataTableReader(dt);
DataTable dtSchema = dr.GetSchemaTable();
string strExpression = "";
if (iExpressionType == (int)Enums.ExpressionType.Filter)
{
strExpression = clsObj.fnselectFilterCondition(Common.iProjectID);
}
else
{
strExpression = "";
}
if (iExpressionType == (int)Enums.ExpressionType.CalaculatedColumn)
{
strExpression = CalaculatedColumnValue;
}
using (var frm = new frmExpressEditor(iExpressionType, Common.strTableName, strExpression))
{
frm._fieldDict = Common.GetDict(dt);
frm.AvailableFields = frm._fieldDict.ToList <KeyValuePair <string, Type> >();
frm.dtSource = dtSchema;
var res = frm.ShowDialog();
if (res == System.Windows.Forms.DialogResult.OK)
{
if (iExpressionType == (int)Enums.ExpressionType.Filter)
{
clsObj.fnInserFilter(frm.strExpression, Common.iProjectID);
}
else if (iExpressionType == (int)Enums.ExpressionType.AddColumn)
{
// Common.strfiltertxt = "";
string strColName = frm.strColName;
string strMsg = "";
strExpression = frm.strExpression;
if (!clsObj.fnAddCalaculatedColumn(Common.strTableName, strColName, strExpression, ref strMsg, Common.iProjectID))
{
if (strMsg != "")
{
Telerik.WinControls.RadMessageBox.Show(this, strMsg, "Information", MessageBoxButtons.OK, RadMessageIcon.Error, MessageBoxDefaultButton.Button1);
}
return;
}
}
else if (iExpressionType == (int)Enums.ExpressionType.CalaculatedColumn)
{
if (!clsObj.fnUpdateCalaculatedColumn(Common.strTableName, strColname, frm.strExpression, Common.iProjectID))
{
}
}
dtmain = (DataTable)dataschemaGrid.DataSource;
dataschemaGridbinding();
}
}
}
catch (Exception ex)
{
Telerik.WinControls.RadMessageBox.Show(this, ex.Message, ex.TargetSite.Name.ToString(), MessageBoxButtons.OK, RadMessageIcon.Error, MessageBoxDefaultButton.Button1);
}
}
/// <summary>
/// Read columns schema from database
/// </summary>
private List <DbColumn> ReadColumns(string tableName, string ownerName)
{
List <DbColumn> result = new List <DbColumn>();
using (SqlDataAdapter adapter = new SqlDataAdapter(String.Format("SELECT TOP 1 * FROM [{0}].[{1}]", ownerName, tableName), (SqlConnection)_dbConnection))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataTable columnsSchema;
// Jjust to avoid stupid "Failed to enable constraints" error!
using (DataSet tempDs = new DataSet())
{
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
using (DataTable columnsList = new DataTable())
{
tempDs.Tables.Add(columnsList);
// Get from db
adapter.Fill(columnsList);
// Get schema
using (DataTableReader reader = new DataTableReader(columnsList))
columnsSchema = reader.GetSchemaTable();
}
}
// Used to get columns Sql DataType
using (DataTable columnsDbTypeTable = _dbConnection.GetSchema("COLUMNS"))
{
// Fetch the rows
foreach (DataRow dr in columnsSchema.Rows)
{
string columnName = dr["ColumnName"].ToString();
DbColumn column = new DbColumn(columnName, "[" + columnName + "]")
{
DataTypeDotNet = ((Type)dr["DataType"]).AliasOrName(),
Length = Convert.ToInt32(dr["ColumnSize"]),
PrimaryKey = Convert.ToBoolean(dr["IsKey"]),
AutoIncrement = Convert.ToBoolean(dr["IsAutoIncrement"]),
AllowNull = Convert.ToBoolean(dr["AllowDBNull"]),
ColumnOrdinal = Convert.ToInt32(dr["ColumnOrdinal"]),
};
column.FieldNameSchema = DbSchemaNames.FieldName_RemoveInvalidChars(column.FieldNameSchema);
// Primary keys that aren't a valid source of dapper access patterns aren't valid
if (column.PrimaryKey)
{
if (column.DataTypeDotNet == "System.DateTime")
{
column.PrimaryKey = false;
}
}
// Columns which needs additional fetch
var succeed = FillColumnAdditionalInfo(column, columnsDbTypeTable, tableName, columnName);
// if additional info readin is failed, don't add it to the list
if (succeed)
{
// Add to result
result.Add(column);
}
else
{
// TODO: inform the user
}
}
}
}
return(result);
}
public DataTable GetSchemaTable()
{
return(_reader.GetSchemaTable());
}
public void SchemaTest()
{
DataTable another = new DataTable("another");
another.Columns.Add("x", typeof(string));
another.Rows.Add(new object[] { "test 1" });
another.Rows.Add(new object[] { "test 2" });
another.Rows.Add(new object[] { "test 3" });
DataTableReader reader = new DataTableReader(new DataTable[] { _dt, another });
try
{
DataTable schema = reader.GetSchemaTable();
Assert.Equal(_dt.Columns.Count, schema.Rows.Count);
Assert.Equal(_dt.Columns[1].DataType.ToString(), schema.Rows[1]["DataType"].ToString());
reader.NextResult(); //schema should change here
schema = reader.GetSchemaTable();
Assert.Equal(another.Columns.Count, schema.Rows.Count);
Assert.Equal(another.Columns[0].DataType.ToString(), schema.Rows[0]["DataType"].ToString());
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
}
}
private void WriteDataSetToExcelFile(DataSet ds, String ExcelFile)
{
DataTableReader dtr = null;
System.Data.DataTable dt = null;
DataTableReader dsr = null;
Application excelApplication = null;
Workbook excelWorkbook = null;
Worksheet excelWorksheet = null;
try
{
dtr = ds.CreateDataReader();
dt = dtr.GetSchemaTable();
dsr = dt.CreateDataReader();
Int32 ColumnSize = dtr.VisibleFieldCount;
String[] ColumnNames = new String[ColumnSize];
Int32 index = 0;
while (dsr.Read())
{
ColumnNames[index] = dsr.GetString(0);
index++;
}
excelApplication = new Application();
excelApplication.DisplayAlerts = false;
//excelApplication.Visible = true;
excelWorkbook = excelApplication.Workbooks.Add(Type.Missing);
excelWorksheet = (Worksheet)excelWorkbook.Sheets[1];
excelApplication.Calculation = XlCalculation.xlCalculationManual;
Int32 ColIdx = 1;
Int32 RowIdx = 1;
foreach (String ColumnName in ColumnNames)
{
excelWorksheet.Cells[RowIdx, ColIdx] = ColumnName;
ColIdx++;
}
ColIdx = 1;
RowIdx = 2;
Int32 Maxrows = ds.Tables[0].Rows.Count;
if (dtr.Read())
{
for (ColIdx = 1; ColIdx <= ColumnSize; ColIdx++)
{
if (dtr.GetFieldType(ColIdx - 1) == typeof(String))
{
((Range)excelWorksheet.Cells[RowIdx, ColIdx]).EntireColumn.NumberFormat = "@";
}
else if (dtr.GetFieldType(ColIdx - 1) == typeof(Decimal))
{
((Range)excelWorksheet.Cells[RowIdx, ColIdx]).EntireColumn.NumberFormat = "#,##0.00_);(#,##0.00)";
}
else if (dtr.GetFieldType(ColIdx - 1) == typeof(DateTime))
{
((Range)excelWorksheet.Cells[RowIdx, ColIdx]).EntireColumn.NumberFormat = "m/d/yyyy";
}
else
{
((Range)excelWorksheet.Cells[RowIdx, ColIdx]).EntireColumn.NumberFormat = "General";
}
excelWorksheet.Cells[RowIdx, ColIdx] = dtr.GetValue(ColIdx - 1);
}
RowIdx++;
}
while (dtr.Read())
{
for (ColIdx = 1; ColIdx <= ColumnSize; ColIdx++)
{
excelWorksheet.Cells[RowIdx, ColIdx] = dtr.GetValue(ColIdx - 1);
}
RowIdx++;
}
excelApplication.Calculation = XlCalculation.xlCalculationAutomatic;
excelWorkbook.SaveAs(ExcelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception e)
{
throw e;
}
finally
{
if (excelWorkbook != null)
{
excelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
excelWorkbook = null;
}
if (excelApplication != null)
{
excelApplication.DisplayAlerts = true;
excelApplication.Quit();
excelApplication = null;
}
if (dsr != null)
{
dsr.Close();
dsr.Dispose();
dsr = null;
}
if (dt != null)
{
dt.Dispose();
dt = null;
}
if (dtr != null)
{
dtr.Close();
dtr.Dispose();
dtr = null;
}
}
}
/// <summary>
/// Read columns schema from database
/// </summary>
private List <DbColumn> ReadColumns(String tableName, string ownerName)
{
var result = new List <DbColumn>();
using (var adapter = new MySqlDataAdapter(String.Format("SELECT * FROM {0}.{1} LIMIT 1", ownerName, tableName), _dbConnection))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataTable columnsSchema;
// Jjust to avoid stupid "Failed to enable constraints" error!
using (var tempDs = new DataSet())
{
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
using (var columnsList = new DataTable())
{
tempDs.Tables.Add(columnsList);
// Get from db
adapter.Fill(columnsList);
// Get schema
using (var reader = new DataTableReader(columnsList))
columnsSchema = reader.GetSchemaTable();
}
}
// Used to get columns MySql DataType
using (DataTable columnsDbTypeTable = _dbConnection.GetSchema("Columns"))
{
// Fetch the rows
foreach (DataRow dr in columnsSchema.Rows)
{
var columnName = dr["ColumnName"].ToString();
var column = new DbColumn(columnName, "`" + columnName + "`")
{
DataTypeDotNet = dr["DataType"].ToString(),
Length = Convert.ToInt32(dr["ColumnSize"]),
PrimaryKey = Convert.ToBoolean(dr["IsKey"]),
AutoIncrement = Convert.ToBoolean(dr["IsAutoIncrement"]),
AllowNull = Convert.ToBoolean(dr["AllowDBNull"]),
ColumnOrdinal = Convert.ToInt32(dr["ColumnOrdinal"]),
DataTypeMaxLength = Convert.ToInt32(dr["ColumnSize"]),
};
// Fixup MySqlDateTime - dapper automatically converts that object
if (column.DataTypeDotNet.IndexOf("MySql.Data.Types.MySqlDateTime") >= 0)
{
column.DataTypeDotNet = "DateTime";
}
column.FieldNameSchema = DbSchemaNames.FieldName_RemoveInvalidChars(column.FieldNameSchema);
// Columns which needs additional fetch
var succeed = FillColumnAdditionalInfo(column, columnsDbTypeTable, tableName, columnName);
// if additional info readin is failed, don't add it to the list
if (succeed)
{
// Add to result
result.Add(column);
}
else
{
// TODO: inform the user
}
}
}
}
return(result);
}
/*
* Loads the CSV file to a dataset, and imports data
* to the database with SqlBulkCopy.
*/
private void SaveToDatabase_withDataSet()
{
try
{
if (fileCheck())
{
// select format, encoding, and write the schema file
Format();
Encoding();
writeSchema();
// loads all rows from from csv file
DataSet ds = LoadCSV(-1);
// gets the number of rows
this.rowCount = ds.Tables[0].Rows.Count;
// Makes a DataTableReader, which reads data from data set.
// It is nececery for bulk copy operation.
DataTableReader dtr = ds.Tables[0].CreateDataReader();
// Creates schema table. It gives column names for create table command.
DataTable dt;
dt = dtr.GetSchemaTable();
// You can view that schema table if you want:
//this.dataGridView_preView.DataSource = dt;
// Creates a new and empty table in the sql database
//CreateTableInDatabase(dt, this.txtOwner.Text, this.txtTableName.Text, prop.sqlConnString);
// Copies all rows to the database from the dataset.
using (SqlBulkCopy bc = new SqlBulkCopy(prop.sqlConnString))
{
// Destination table with owner - this example doesn't
// check the owner and table names!
bc.DestinationTableName = "[" + this.txtOwner.Text + "].[" + this.txtTableName.Text + "]";
// User notification with the SqlRowsCopied event
bc.NotifyAfter = 100;
bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
// Starts the bulk copy.
bc.WriteToServer(ds.Tables[0]);
// Closes the SqlBulkCopy instance
bc.Close();
}
// Writes the number of imported rows to the form
this.lblProgress.Text = "Imported: " + this.rowCount.ToString() + "/" + this.rowCount.ToString() + " row(s)";
this.lblProgress.Refresh();
// Notifies user
MessageBox.Show("ready");
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Error - SaveToDatabase_withDataSet", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}