private static void ProcessQuery(string query)
{
var cmd = new OdbcCommand(query, _cn);
//OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
DataSet dataSet = new DataSet();
//adapter.Fill(dataSet);
OdbcDataReader reader = cmd.ExecuteReader();
DataTable schemaTable = reader.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
foreach (DataColumn column in schemaTable.Columns)
{
Console.WriteLine(String.Format("{0} = {1}",
column.ColumnName, row[column]));
}
}
if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine("{0}\t{1}", reader.GetString(0),
reader.GetString(1));
}
}
else
{
Console.WriteLine("No rows found.");
}
reader.Close();
}
public DataTable GetSchemaTable(string sqlstr, params IDbDataParameter[] args)
{
error = null;
using (OdbcConnection myConn = new OdbcConnection(this.ConnStr))
{
OdbcCommand myCmd = new OdbcCommand(sqlstr, myConn);
myCmd.CommandTimeout = CommandTimeout;
try
{
myConn.Open();
foreach (IDbDataParameter arg in args)
{
if (myCmd.CommandType != CommandType.StoredProcedure)
{
myCmd.CommandType = CommandType.StoredProcedure;
}
myCmd.Parameters.Add(arg);
}
OdbcDataReader myReader = myCmd.ExecuteReader(CommandBehavior.KeyInfo);
return(myReader.GetSchemaTable());
}
catch (OdbcException ex)
{
error = ex;
return(null);
}
finally
{
myConn.Close();
myCmd.Dispose();
myConn.Dispose();
}
}
}
private void mostraCampos(string tabela)
{
OdbcCommand cmd = conOle.CreateCommand();
cmd.CommandText = "SELECT * FROM [" + tabela + "] WHERE 0=1";
OdbcDataReader dr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
DataTable tbCampos = dr.GetSchemaTable();
dgvCampos.DataSource = tbCampos;
}
public DataTable GetDataTableFromReader(string queryString)
{
OdbcConnection dbConnection = new OdbcConnection(_connectionString);
DataTable dt = new DataTable();
try {
dbConnection.Open();
OdbcCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = queryString;
OdbcDataReader dbReader = dbCommand.ExecuteReader();
DataTable dtSchema = dbReader.GetSchemaTable();
// You can also use an ArrayList instead of List<>
List <DataColumn> listCols = new List <DataColumn>();
if (dtSchema != null)
{
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = System.Convert.ToString(drow["ColumnName"]);
DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
if (column.DataType == System.Type.GetType("System.Byte[]"))
{
continue;
}
column.Unique = (bool)drow["IsUnique"];
column.AllowDBNull = (bool)drow["AllowDBNull"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
listCols.Add(column);
dt.Columns.Add(column);
}
}
// Read rows from DataReader and populate the DataTable
while (dbReader.Read())
{
DataRow dataRow = dt.NewRow();
for (int i = 0; i < listCols.Count; i++)
{
dataRow[((DataColumn)listCols[i])] = dbReader[i];
}
dt.Rows.Add(dataRow);
}
dbReader.Close();
dbCommand.Dispose();
dbConnection.Close();
return(dt);
}
finally {
dbConnection.Close();
}
}
private bool GetColumns()
{
bool successful = false;
dataColumns = null;
connectionString = string.Format("Driver={0}; Server={1}; Database={2}; Uid={3};Pwd={4};", textBoxDriver.Text, textBoxServer.Text, textBoxDatabase.Text, textBoxUsername.Text, textBoxPassword.Text);
string queryString = "";
if (radioButtonTable.Checked)
{
table = comboBoxTables.SelectedItem.ToString();
queryString = "select * from " + table;
}
else if (radioButtonQuery.Checked)
{
// remove the first part of the query string
int fromLocation = textBoxQuery.Text.IndexOf("FROM", StringComparison.InvariantCultureIgnoreCase);
if (fromLocation >= 0)
{
table = textBoxQuery.Text.Substring(fromLocation + 4);
queryString = "select * from " + table;
textBoxQuery.Text = queryString;
}
}
// try and get the column information
using (OdbcConnection myConnection = new OdbcConnection(connectionString))
using (OdbcCommand com = new OdbcCommand(queryString, myConnection))
{
myConnection.Open();
using (OdbcDataReader reader = com.ExecuteReader(System.Data.CommandBehavior.SchemaOnly))
{
dataColumns = reader.GetSchemaTable();
// TODO - how should we deal with ambiguous column names?
// Add an extra row with a blank column name
DataRow blankRow = dataColumns.NewRow();
blankRow["ColumnName"] = "";
dataColumns.Rows.InsertAt(blankRow, 0);
successful = true;
}
myConnection.Close();
}
return(successful);
}
public void InsertOne(OdbcDataReader reader, string tableName)
{
DataTable dt = reader.GetSchemaTable();
if (dt != null)
{
//string colstr = helper.CreateColumnStringBySqlReaderSchema(資料表名稱[no], dt);
string colstr = CreateColumnStringBySqlReaderSchema(dt);
CreateTable(tableName, colstr);
}
bool first = true;
using (var cn = new SQLiteConnection(ConnectionString))
{
cn.Open();
using (var transaction = cn.BeginTransaction())
{
using (var cmd = cn.CreateCommand())
{
while (reader.Read())
{
Object[] values = new Object[reader.FieldCount];
int fieldCounts = reader.GetValues(values);
if (first)
{
cmd.CommandText = GetInsertString(tableName);
for (int i = 0; i < fieldCounts; i++)
{
cmd.Parameters.AddWithValue(ParameterList[i], values[i]);
}
first = false;
}
else
{
for (int i = 0; i < fieldCounts; i++)
{
cmd.Parameters[i].Value = values[i];
}
}
cmd.ExecuteNonQuery();
values = null;
}
}
transaction.Commit();
}
}
}
public void ToSqlite(string sqliteDirectory, string sqliteFileName)
{
string odbcString = $"select * from {dbfPath}";
using (OdbcConnection conn = new OdbcConnection(Connection))
{
using (OdbcCommand cmd = new OdbcCommand(odbcString, conn))
{
conn.Open();
OdbcDataReader reader = cmd.ExecuteReader();
var schema = reader.GetSchemaTable();
SqliteHelper sqliteHelper = new SqliteHelper(sqliteDirectory, sqliteFileName);
sqliteHelper.InsertOne(reader, dbfTableName);
reader.Close();
}
}
}
public void data_backup(string tablename, OdbcDataReader dr)
{
if (data_type == null)
{
data_type = new Hashtable();
}
if (data_columnsize == null)
{
data_columnsize = new Hashtable();
}
for (int i = 0; i < dr.FieldCount; i++)
{
string key = tablename + "." + dr.GetName(i);
if (!data_type.ContainsKey(key))
{
data_type.Add(key, dr.GetDataTypeName(i));
}
if (!data_columnsize.ContainsKey(key))
{
DataRow[] rows = dr.GetSchemaTable().Select(string.Format("ColumnName='{0}'", dr.GetName(i)));
data_columnsize.Add(key, rows[0]["ColumnSize"].ToString());
}
if (data_type[key].ToString() == "date")
{
if (dr.HasRows)
{
data_value.Add(key, string.Format("{0:yyyy/MM/dd}", dr[i]));
}
else
{
data_value.Add(key, "");
}
}
else
{
if (dr.HasRows)
{
data_value.Add(key, dr[i].ToString());
}
else
{
data_value.Add(key, "");
}
}
}
}
public DataTable executeQuery(string query)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
OdbcCommand dc = new OdbcCommand(query, conn);
OdbcDataReader reader = dc.ExecuteReader();
schematab = reader.GetSchemaTable();
DataTable dt = new DataTable();
dt.Load(reader);
reader.Close();
return(dt);
}
public int Read(string table)
{
int cnt = 0;
string strQuery = "select * from " + table;
// try
{
OdbcCommand cmd = new OdbcCommand(strQuery, con);
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
using (OdbcDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
using (StreamWriter sw = new StreamWriter(path + "\\" + table + ".txt", false))
{
string Title = "\t";
DataTable schema = reader.GetSchemaTable();
for (int i = 0; i < schema.Rows.Count; i++)
{
Title += schema.Rows[i][0].ToString() + "\t";
}
sw.WriteLine(Title);
while (reader.Read())
{
string str = cnt++.ToString();
sw.WriteLine(str);
}
}
}
}
// catch( Exception ex )
// {
// MessageBox.Show( ex.ToString() );
// }
return(cnt);
}
/*
* Imports data to the database with SqlBulkCopy.
* This method doesn't use a temporary dataset, it loads
* data immediately from the ODBC connection
*/
private void SaveToDatabaseDirectly()
{
try
{
if (fileCheck())
{
// select format, encoding, and write the schema file
Format();
Encoding();
writeSchema();
// Creates and opens an ODBC connection
string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + this.dirCSV.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
string sql_select;
OdbcConnection conn;
conn = new OdbcConnection(strConnString.Trim());
conn.Open();
//Counts the row number in csv file - with an sql query
OdbcCommand commandRowCount = new OdbcCommand("SELECT COUNT(*) FROM [" + this.FileNevCSV.Trim() + "]", conn);
this.rowCount = System.Convert.ToInt32(commandRowCount.ExecuteScalar());
// Creates the ODBC command
sql_select = "select * from [" + this.FileNevCSV.Trim() + "]";
OdbcCommand commandSourceData = new OdbcCommand(sql_select, conn);
// Makes on OdbcDataReader for reading data from CSV
OdbcDataReader dataReader = commandSourceData.ExecuteReader();
// Creates schema table. It gives column names for create table command.
DataTable dt;
dt = dataReader.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(dataReader, this.txtOwner.Text, this.txtTableName.Text, prop.sqlConnString);
// Copies all rows to the database from the data reader.
/*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(dataReader);
*
* // 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 - SaveToDatabaseDirectly", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public IList <Column> GetTableDetails(Table table, string owner)
{
var columns = new List <Column>();
var conn = new OdbcConnection(connectionStr);
conn.Open();
try
{
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "select top 10 * from " + owner + "." + table;
OdbcDataReader reader = cmd.ExecuteReader((CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly));
DataTable details = reader.GetSchemaTable();
string[] rest = { null, null, table.Name, null };
DataTable schema = conn.GetSchema("Indexes", rest);
foreach (DataRow row in schema.Rows)
{
}
// get extended schema
OdbcCommand cmdGetSchema = conn.CreateCommand();
cmdGetSchema.CommandText = $"select * from sysprogress.SYSCOLUMNS_FULL where TBL = '{table}'";
OdbcDataReader rdrGetSchema = cmdGetSchema.ExecuteReader();
var dataTableScheme = new DataTable();
dataTableScheme.Load(rdrGetSchema);
foreach (DataRow r in dataTableScheme.Rows)
{
int? colSize = (int)r["WIDTH"];
string colType = r["COLTYPE"].ToString();
if (colType == "varchar")
{
var m = Regex.Match((string)r["FORMAT"], @"\d+");
if (m.Success && !string.IsNullOrWhiteSpace(m.Value))
{
colSize = Convert.ToInt32(m.Value);
}
}
var c = new Column();
c.Name = r["COL"].ToString();
c.DataType = r["COLTYPE"].ToString();
c.IsNullable = r["NULLFLAG"] != DBNull.Value && (string)r["NULLFLAG"] == "Y";
c.IsIdentity = false; //(bool) r["IsAutoIncrement"],
c.IsPrimaryKey = false; //(bool) r["IsKey"],
c.IsForeignKey = false;
c.IsUnique = false; //(bool) r["IsUnique"],
c.MappedDataType = r["COLTYPE"].ToString();
c.DataLength = colSize;
c.DataScale = r["SCALE"] == DBNull.Value ? null : (int?)r["SCALE"];
c.Format = (string)r["FORMAT"];
c.DataPrecision = colSize;
c.ConstraintName = "";
c.Description = (string)r["DESCRIPTION"];
columns.Add(c);
}
foreach (DataRow row in details.Rows)
{
Column c = columns.FirstOrDefault(r => r.Name == row["ColumnName"].ToString());
if (c == null)
{
continue;
}
// c.Name = row["ColumnName"].ToString();
// c.DataType = row["DataType"].ToString();
c.IsNullable = (bool)row["AllowDBNull"];
c.IsIdentity = (bool)row["IsAutoIncrement"];
c.IsPrimaryKey = (bool)row["IsKey"];
// c.IsForeignKey = false;
c.IsUnique = (bool)row["IsUnique"];
// c.MappedDataType = row["DataType"].ToString();
// c.DataLength = (int)row["ColumnSize"];
// c.DataScale = (short)row["NumericScale"];
// c.DataPrecision = (int)row["ColumnSize"];
// c.ConstraintName = "";
}
//var m = new DataTypeMapper();
//foreach (DataRow row in details.Rows)
//{
// Column c = new Column();
// c.Name = row["ColumnName"].ToString();
// c.DataType = row["DataType"].ToString();
// c.IsNullable = (bool)row["AllowDBNull"];
// c.IsIdentity = (bool)row["IsAutoIncrement"];
// c.IsPrimaryKey = (bool)row["IsKey"];
// c.IsForeignKey = false;
// c.IsUnique = (bool)row["IsUnique"];
// c.MappedDataType = row["DataType"].ToString();
// c.DataLength = (int)row["ColumnSize"];
// c.DataScale = (short)row["NumericScale"];
// c.DataPrecision = (int)row["ColumnSize"];
// c.ConstraintName = "";
// columns.Add(c);
//}
table.Columns = columns;
//table.Owner = owner;
table.PrimaryKey = DeterminePrimaryKeys(table);
// Need to find the table name associated with the FK
foreach (var c in table.Columns)
{
if (c.IsForeignKey)
{
string referencedTableName;
string referencedColumnName;
GetForeignKeyReferenceDetails(c.ConstraintName, out referencedTableName, out referencedColumnName);
c.ForeignKeyTableName = referencedTableName;
c.ForeignKeyColumnName = referencedColumnName;
}
}
table.ForeignKeys = DetermineForeignKeyReferences(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return(columns);
}
public override void GetMetadata()
{
if (SqlStatement == null)
{
throw new InvalidOperationException();
}
if (Connection is OleDBConnection oledbConnection)
{
// HACK. How should this be handled instead? Maybe we should rebuild the connection string from scratch,
// with confirmed required parameters.
string connectionString = oledbConnection.ConnectionString.Replace("Provider=MSOLEDBSQL.1;", "").Replace("Auto Translate=True", "");
using (SqlConnection connection = new(connectionString))
{
connection.Open();
string sql = SqlStatement;
DataTable schema;
using (SqlCommand command = new(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
{
schema = reader.GetSchemaTable();
}
}
List <Column> suggestedColumns = GetSqlList(schema);
Dictionary <string, Column> suggestedColumnDict = new();
foreach (Column column in suggestedColumns)
{
suggestedColumnDict.Add(column.Name, column);
}
UpdateTables(suggestedColumnDict);
}
}
else if (Connection is Connections.OdbcConnection odbcConnection)
{
string connectionString = odbcConnection.ConnectionString;
using (System.Data.Odbc.OdbcConnection connection = new(connectionString))
{
connection.Open();
string sql = SqlStatement;
DataTable?schema;
using (OdbcCommand command = new(sql, connection))
using (OdbcDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
{
schema = reader.GetSchemaTable();
}
if (schema != null)
{
List <Column> suggestedColumns = GetOdbcList(schema);
Dictionary <string, Column> suggestedColumnDict = new();
foreach (Column column in suggestedColumns)
{
suggestedColumnDict.Add(column.Name, column);
}
UpdateTables(suggestedColumnDict);
}
else
{
throw new ArgumentNullException($"Failed to retrieve schema from connection {odbcConnection.Name}.");
}
}
}
}
public DataTable GetSchemaTable(IDataReader dataReader)
{
OdbcDataReader odbcDataReader = (OdbcDataReader)dataReader;
return(odbcDataReader.GetSchemaTable());
}
public DataTable GetSchemaTable()
{
return(_reader.GetSchemaTable());
}
private const string _options = @"Trusted_Connection=Yes"; // Any additional connection options
static void Main(string[] args)
{
// Parse command line
bool show_help = false;
OptionSet p = new OptionSet()
.Add(
"s=|server=|S=|Server=",
"The SQL Server instance to connect to.",
v => _server = v
)
.Add(
"d=|database=|D=|Database=",
"The SQL Server database to connect to.",
v => _database = v
)
.Add(
"t=|table=|T=|Table=",
"The SQL Server table whose columns will be returned.",
v => _table = v
)
.Add(
"v",
"Increase debug message verbosity.",
v => { if (v != null)
{
++_verbosity;
}
}
)
.Add(
"?|h|help",
"Display this help message.",
v => show_help = v != null
);
List <string> extra;
try
{
extra = p.Parse(args);
}
catch (OptionException e)
{
Console.Write("GetColumnNames: ");
Console.WriteLine(e.Message);
Console.WriteLine("Try 'GetColumnNames --help' for more information.");
return;
}
if (show_help || (_server == null || _database == null || _table == null))
{
ShowHelp(p);
return;
}
// Main processing
List <string> columns = new List <string>();
try
{
using (OdbcConnection connection = new OdbcConnection(GetConnectionString()))
{
// Connect to the database.
Debug("Connection String: {0}", connection.ConnectionString);
connection.Open();
using (OdbcCommand cmd = new OdbcCommand(GetQueryString(), connection))
{
Debug("Query String: {0}", cmd.CommandText);
using (OdbcDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo)) // must use KeyInfo, not SchemaOnly
{
// Retrieve column schema into a DataTable.
DataTable schemaTable = reader.GetSchemaTable(); // similar to INFORMATION_SCHEMA.columns
// For each row in the table...
foreach (DataRow row in schemaTable.Rows)
{
// Load the list with the column names
columns.Add(row["ColumnName"].ToString());
}
// Print debug information
DebugFields(schemaTable);
}
}
}
PrintColumnNames(columns);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
/// <summary>
/// Search method
/// </summary>
/// <param name="directory">Search settings<seealso cref="DirectoryType"/></param>
/// <returns>A dataset conform to the FieldFormatters specified<seealso cref="FieldFormatter"/></returns>
public static DataSet Search(DirectoryType directory)
{
DataSet results = new DataSet();
DataTable dt = results.Tables.Add();
OdbcConnection odbc = new OdbcConnection();
OdbcDataReader reader = null;
OdbcCommand command = null;
try
{
string dsn = "DSN=";
dsn += ((SqlDatasourceType)directory.Item).dsn;
dsn += ";Uid=";
dsn += ((SqlDatasourceType)directory.Item).uid;
dsn += ";Pwd=";
dsn += ((SqlDatasourceType)directory.Item).pwd;
odbc.ConnectionString = dsn;
log.Debug("Opening ODBC connection...");
odbc.Open();
string sql = ((SqlDatasourceType)directory.Item).command + " " + ((SqlDatasourceType)directory.Item).sqlFilter;
log.Debug("Initializing ODBC command: " + sql);
command = odbc.CreateCommand();
command.CommandText = sql;
log.Debug("Loading data in memory...");
reader = command.ExecuteReader();
DataTable schema = reader.GetSchemaTable();
foreach (DataRow dr in schema.Rows)
{
dt.Columns.Add((string)dr[0], System.Type.GetType(((Type)dr[5]).FullName));
}
object[] values = new object[dt.Columns.Count];
while (reader.Read())
{
reader.GetValues(values);
dt.Rows.Add(values);
}
return(results);
}
catch (Exception e)
{
throw new Exception("Request failed!", e);
}
finally
{
if (reader != null)
{
reader.Close();
reader.Dispose();
}
if (command != null)
{
command.Dispose();
}
if (odbc != null)
{
odbc.Close();
odbc.Dispose();
}
}
}
public IList <Column> GetTableDetails(Table table, string owner)
{
var columns = new List <Column>();
var conn = new OdbcConnection(connectionStr);
conn.Open();
//try
{
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "select top 10 * from " + owner + "." + table;
OdbcDataReader reader = cmd.ExecuteReader((CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly));
DataTable details = reader.GetSchemaTable();
string[] rest = { null, null, table.Name, null };
DataTable schema = conn.GetSchema("Indexes", rest);
foreach (DataRow row in schema.Rows)
{
}
var m = new DataTypeMapper();
foreach (DataRow row in details.Rows)
{
Column c = new Column();
c.Name = row["ColumnName"].ToString();
c.DataType = row["DataType"].ToString();
c.IsNullable = (bool)row["AllowDBNull"];
c.IsIdentity = (bool)row["IsAutoIncrement"];
c.IsPrimaryKey = (bool)row["IsKey"];
c.IsForeignKey = false;
c.IsUnique = (bool)row["IsUnique"];
c.MappedDataType = row["DataType"].ToString();
c.DataLength = (int)row["ColumnSize"];
c.DataScale = (short)row["NumericScale"];
c.DataPrecision = (int)row["ColumnSize"];
c.ConstraintName = "";
columns.Add(c);
}
table.Columns = columns;
//table.Owner = owner;
table.PrimaryKey = DeterminePrimaryKeys(table);
// Need to find the table name associated with the FK
foreach (var c in table.Columns)
{
if (c.IsForeignKey)
{
string referencedTableName;
string referencedColumnName;
GetForeignKeyReferenceDetails(c.ConstraintName, out referencedTableName, out referencedColumnName);
c.ForeignKeyTableName = referencedTableName;
c.ForeignKeyColumnName = referencedColumnName;
}
}
table.ForeignKeys = DetermineForeignKeyReferences(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
//finally
{
conn.Close();
}
return(columns);
}
public void RunQuery(ODBC odbc, string _sqlQ)
{
string ConectStreng;
ConectStreng = "DSN=BZDSNT;UID=z6fia;Pwd=xxxxxx";
using (var conn = new OdbcConnection(ConectStreng))
{
try
{
OdbcCommand cmd = new OdbcCommand(_sqlQ, conn);
cmd.CommandTimeout = 1000 * 60; // 5 minutter
conn.Open();
var schema = conn.GetSchema();
var datasource = conn.DataSource;
using (OdbcDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
//SqlResult res = new SqlResult();
//result.Add(res);
//Kolloner col = new Kolloner();
for (int i = 0; i < reader.FieldCount; i++)
{
string value;
//string dataType;
switch (reader.GetValue(i).GetType().Name)
{
case "DateTime":
//dt = reader.GetDateTime(i);
//value = reader.GetDateTime(i).ToString("yyyy-MM-dd HH:mm:ss.ffffff");
switch ((Int32)reader.GetSchemaTable().Rows[i]["ColumnSize"])
{
case 16:
value = reader.GetDateTime(i).ToString("yyyy-MM-dd HH:mm:ss.ffffff");
break;
case 6:
value = reader.GetDateTime(i).ToString("yyyy-MM-dd");
break;
default:
throw new Exception("Wrong Date Handling");
}
break;
case "String":
value = reader.GetValue(i).ToString();
break;
case "Decimal":
if (conn.Driver == "DB2CLI.DLL")
{
// scale er antallet af decimaler
NumberFormatInfo nfi = new NumberFormatInfo()
{
NumberDecimalSeparator = ",", NumberGroupSeparator = "."
};
var scale = Double.Parse(reader.GetSchemaTable().Rows[i]["NumericScale"].ToString());
value = (Convert.ToDouble(reader.GetDecimal(i)) / Math.Pow(10D, scale)).ToString("0." + new String('0', (int)scale), nfi);
}
else
{ // SQLSRV32.DLL
value = reader.GetValue(i).ToString();
}
break;
default:
value = reader.GetValue(i).ToString();
break;
} // end switch
//res.Addkol(value);
}
}
}
}
catch (OdbcException ex)
{
if (ex.Message.Contains("USERNAME AND"))
{
MessageBox.Show("Formentlig password fejl. Der indhentes nyt ved næste SQL opslag." + "\n" + ex.Message);
}
else
{
MessageBox.Show(ex.Message);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
}
private static (HashSet <string>, HashSet <string>) GetUniqueColumnNamesForFile(string filePath)
{
var connectionStr = "Driver={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=" + filePath + "; Uid = Admin; Pwd =;";
// Console.WriteLine("Connection string is: " + connectionStr);
Console.WriteLine("Current access file: " + filePath);
var tableNames = GetTableNames(connectionStr);
// var columnSet = new HashSet<string>();
// var numuneBolgeYerCombos = new HashSet<string>();
foreach (var tblname in tableNames)
{
// Console.WriteLine(tblname);
if (tblname.StartsWith("~TMP") || tblname.Contains('$') || tblname.Contains("Copy of"))
{
// There is a problem with tables starting with ~TMP, they shouldn't even exist! TODO
// The same is true for table names that contain $.
continue;
}
// A small optimization to not call everytime we read data.
StringBuilder sb = new StringBuilder();
using (OdbcConnection connection = new OdbcConnection(connectionStr))
{
connection.Open();
var query = "select * from " + tblname + ""; // Get all data from the table.
try
{
OdbcCommand command = new OdbcCommand(query, connection);
OdbcDataReader data = command.ExecuteReader();
System.Data.DataTable dataTable = data.GetSchemaTable();
// Schema for tables:
// ColumnName (System.String), ColumnOrdinal (System.Int32), ColumnSize (System.Int32),
// NumericPrecision (System.Int16), NumericScale (System.Int16), DataType (System.Object),
// ProviderType (System.Int32), IsLong (System.Boolean), AllowDBNull (System.Boolean),
// IsReadOnly (System.Boolean), IsRowVersion (System.Boolean), IsUnique (System.Boolean),
// IsKey (System.Boolean), IsAutoIncrement (System.Boolean),
// BaseSchemaName (System.String), BaseCatalogName (System.String), BaseTableName (System.String), BaseColumnName (System.String)
var set = new HashSet <(string, string)>();
foreach (DataRow row in dataTable.Rows)
{
string name = row["ColumnName"].ToString();
string type = row["DataType"].ToString();
set.Add((name, type));
}
uint rowCount = 0;
while (data.Read())
{
foreach (var(colName, colType) in set)
{
var content = "";
try
{
content = data[colName].ToString();
}
catch (System.Exception)
{
Console.WriteLine("Error: No " + colName + " field.");
}
content.Replace('\r', ' '); // We don't want newlines in the content
content.Replace('\n', ' ');
content = content.Trim(); // Some rows contain a lot of whitespace and no content. We forgot to check for those!
if (content != "" && content != "-" && colName != "No" && colName != "Kimlik")
{
var toPrint = filePath + "\t" + tblname + "\t(" + rowCount + ")\t" + colName + "\t" + colType + "\t" + content;
sb.Append(toPrint);
sb.AppendLine();
}
// No need to log the empty stuff anymore.
/* else
* {
* empty_writer.WriteLine(toPrint);
* }
*/
}
rowCount += 1;
}
// HashSet<string> numuneBolgeYer = NumuneBolgeYer(data);
// numuneBolgeYerCombos.UnionWith(numuneBolgeYer);
}
catch (System.Exception e)
{
Console.WriteLine("Error while executing query: " + query);
Console.WriteLine(e);
}
}
writer.Write(sb.ToString()); // The optimization
}
return(null, null); //(columnSet, numuneBolgeYerCombos);
}
public static DataTable RetrieveSchema(ConnectionType connectionType, string connectionString, string sqlString)
{
DataTable dataSet = new DataTable();
switch (connectionType)
{
case ConnectionType.SqlServer:
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlString, connectionString))
{
sqlDataAdapter.FillSchema(dataSet, SchemaType.Source);
}
break;
case ConnectionType.Odbc:
using (OdbcDataAdapter odbcDataAdapter = new OdbcDataAdapter(sqlString, connectionString))
{
odbcDataAdapter.FillSchema(dataSet, SchemaType.Source);
}
if (dataSet.Columns.Count == 0 && dataSet.Rows.Count == 0)
{
OdbcCommand myOdbcCommand = null;
try
{
myOdbcCommand = new OdbcCommand(sqlString);
myOdbcCommand.Connection = new OdbcConnection(connectionString);
myOdbcCommand.Connection.Open();
OdbcDataReader dataReader = myOdbcCommand.ExecuteReader();
DataTable schema = dataReader.GetSchemaTable();
foreach (DataRow row in schema.Rows)
{
Type runtimeType = row[5].GetType();
PropertyInfo propInfo = runtimeType.GetProperty("UnderlyingSystemType");
dataSet.Columns.Add(row[0].ToString(), (Type)propInfo.GetValue(row[5], null));
}
}
finally
{
myOdbcCommand.Connection.Close();
}
}
break;
case ConnectionType.Oracle:
using (OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(sqlString, connectionString))
{
try
{
oracleDataAdapter.FillSchema(dataSet, SchemaType.Source);
}
catch (Exception exc)
{
if (exc.Message == "TTCExecuteSql:ReceiveExecuteResponse - Unexpected Packet received.")
{
oracleDataAdapter.FillSchema(dataSet, SchemaType.Source);
}
else
{
throw exc;
}
}
}
break;
case ConnectionType.OleDb:
using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(sqlString, connectionString))
{
oleDbDataAdapter.FillSchema(dataSet, SchemaType.Source);
}
break;
}
return(dataSet);
}
