private void GetDBObjectNames(string name, List <string> list) { DataTable schema = null; using (DbConnection connection = GetConnection()) using (OleDbCommandBuilder builder = new OleDbCommandBuilder()) { connection.Open(); schema = connection.GetSchema("Tables", new string[] { null, null, null, name }); foreach (DataRow row in schema.Rows) { string tableName = row["TABLE_NAME"].ToString(); string schemaName = row["TABLE_SCHEMA"].ToString(); if (String.IsNullOrEmpty(schemaName)) { list.Add(tableName); } else { list.Add(schemaName + "." + builder.QuoteIdentifier(tableName, connection as OleDbConnection)); } } } }
public void QuoteUnquote_CustomPrefixSuffix_Success() { RunTest((command, tableName) => { using (var cmd = (OleDbCommand)OleDbFactory.Instance.CreateCommand()) { cmd.Transaction = transaction; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = @"SELECT * FROM " + tableName; cmd.Connection = (OleDbConnection)OleDbFactory.Instance.CreateConnection(); cmd.Connection.ConnectionString = connection.ConnectionString; using (var adapter = new OleDbDataAdapter(cmd.CommandText, connection)) using (var builder = new OleDbCommandBuilder(adapter)) { // Custom prefix & suffix builder.QuotePrefix = "'"; builder.QuoteSuffix = "'"; Assert.Equal(adapter, builder.DataAdapter); Assert.Equal("'Test'", builder.QuoteIdentifier("Test", connection)); Assert.Equal("'Te''st'", builder.QuoteIdentifier("Te'st", connection)); Assert.Equal("Test", builder.UnquoteIdentifier("'Test'", connection)); Assert.Equal("Te'st", builder.UnquoteIdentifier("'Te''st'", connection)); // Ensure we don't need active connection: Assert.Equal("'Test'", builder.QuoteIdentifier("Test", null)); Assert.Equal("Test", builder.UnquoteIdentifier("'Test'", null)); builder.QuotePrefix = string.Empty; string quoteErrMsg = $"{nameof(builder.QuoteIdentifier)} requires open connection when the quote prefix has not been set."; string unquoteErrMsg = $"{nameof(builder.UnquoteIdentifier)} requires open connection when the quote prefix has not been set."; Assert.Equal("`Test`", builder.QuoteIdentifier("Test", connection)); Assert.Equal("Test", builder.UnquoteIdentifier("`Test`", connection)); Assert.NotNull(adapter.SelectCommand.Connection); Assert.Equal("`Test`", builder.QuoteIdentifier("Test")); Assert.Equal("Test", builder.UnquoteIdentifier("`Test`")); adapter.SelectCommand.Connection = null; AssertExtensions.Throws <InvalidOperationException>(() => builder.QuoteIdentifier("Test"), quoteErrMsg); AssertExtensions.Throws <InvalidOperationException>(() => builder.UnquoteIdentifier("'Test'"), unquoteErrMsg); } } }); }
static string Quote(string unquoted) { OleDbCommandBuilder quoteBuilder = new OleDbCommandBuilder(); quoteBuilder.QuotePrefix = "'"; quoteBuilder.QuoteSuffix = "'"; return(quoteBuilder.QuoteIdentifier(unquoted)); }
/// <inheritdoc/> public override string QuoteIdentifier(string value, DbConnection connection) { // already quoted? if (value.EndsWith("\"") || value.EndsWith("]") || value.EndsWith("'") || value.EndsWith("`")) { return(value); } // OleDb is universal connection, so we need quoting dependent on used database type using (OleDbCommandBuilder builder = new OleDbCommandBuilder()) { return(builder.QuoteIdentifier(value, connection as OleDbConnection)); } }
private static void Test_Command_QuoteIdentifier() { string query = "select id,name from t"; OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, conn); OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter); string sql_quote_conn = commandBuilder.QuoteIdentifier("select id,name from t", conn); Assert.AreEqual(sql_quote_conn, "\"select id,name from t\""); string sql_unquote_conn = commandBuilder.UnquoteIdentifier("\"select id,name from t\"", conn); Assert.AreEqual(sql_unquote_conn, "select id,name from t"); }
public DataTable ReadTable(string tableName, IEnumerable columns, int maxRows) { // We specifically use OleDb to read a CSV file... WriteDiagnostics("ReadTable: {0}", tableName); WriteDiagnostics("Current Directory: {0}", Directory.GetCurrentDirectory()); // We better work with a full path, if nothing else, errors become easier to report string fullPath = this.FixPath(this.fileName) ?? Path.GetFullPath(this.fileName); // We can map simplified CSVs to an OLEDB/Text connection, then proceed as normal using (OleDbConnection connection = new OleDbConnection()) using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter()) using (OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder()) using (OleDbCommand command = new OleDbCommand()) { // We have to use the name of the folder which contains the CSV file in the connection string // If target platform is x64, then use CsvConnectionTemplate64 connection string. if (IntPtr.Size == 8) { connection.ConnectionString = string.Format(CultureInfo.InvariantCulture, CsvConnectionTemplate64, Path.GetDirectoryName(fullPath)); } else { connection.ConnectionString = string.Format(CultureInfo.InvariantCulture, CsvConnectionTemplate, Path.GetDirectoryName(fullPath)); } WriteDiagnostics("Connection String: {0}", connection.ConnectionString); // We have to open the connection now, before we try to quote // the table name, otherwise QuoteIdentifier fails (for OleDb, go figure!) // The connection will get closed when we dispose of it connection.Open(); string quotedTableName = commandBuilder.QuoteIdentifier(tableName, connection); command.Connection = connection; string topClause; if (maxRows >= 0) { topClause = string.Format(CultureInfo.InvariantCulture, " top {0}", maxRows.ToString(NumberFormatInfo.InvariantInfo)); } else { topClause = string.Empty; } string columnsClause; if (columns != null) { StringBuilder builder = new StringBuilder(); foreach (string columnName in columns) { if (builder.Length > 0) { builder.Append(','); } builder.Append(commandBuilder.QuoteIdentifier(columnName, connection)); } columnsClause = builder.ToString(); if (columnsClause.Length == 0) { columnsClause = "*"; } } else { columnsClause = "*"; } command.CommandText = string.Format(CultureInfo.InvariantCulture, "select {0} {1} from {2}", topClause, columnsClause, quotedTableName); WriteDiagnostics("Query: " + command.CommandText); dataAdapter.SelectCommand = command; DataTable table = new DataTable(); table.Locale = CultureInfo.InvariantCulture; dataAdapter.Fill(table); return(table); } }