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