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); }