public void ExecuteSql_CaseInsenitive()
{
Console.WriteLine("NationalLanguage: " + SqlUtility.NationalLanguage);
string table = GetRandomTableName();
var sqlExecuter = GetSqlExecuter();
sqlExecuter.ExecuteSql(new[] {
"CREATE TABLE " + table + " ( S NVARCHAR2(256) )",
"INSERT INTO " + table + " VALUES ('a')",
"INSERT INTO " + table + " VALUES ('A')",
"INSERT INTO " + table + " VALUES ('b')",
"INSERT INTO " + table + " VALUES ('B')",
});
var result = new List <string>();
sqlExecuter.ExecuteReader("SELECT * FROM " + table + " WHERE S LIKE 'a%'",
reader => result.Add(SqlUtility.EmptyNullString(reader, 0)));
Assert.AreEqual(
TestUtility.DumpSorted(new[] { "a", "A" }),
TestUtility.DumpSorted(result),
"Comparison will be case insensitive depending on SqlUtility.NationalLanguage (see NLS_SORT), provided in connection string ProviderName (for example Rhetos.Oracle.GENERIC_M_CI or Rhetos.Oracle.XGERMAN_CI).");
result.Clear();
sqlExecuter = new OracleSqlExecuter(SqlUtility.ConnectionString, new ConsoleLogProvider(), new NullUserInfo());
sqlExecuter.ExecuteReader("SELECT * FROM " + table + " WHERE S LIKE 'a%'",
reader => result.Add(SqlUtility.EmptyNullString(reader, 0)));
Assert.AreEqual(
TestUtility.DumpSorted(new[] { "a", "A" }),
TestUtility.DumpSorted(result),
"Using new instance of SqlExecuter.");
}
public void SendUserInfoInSqlContext_NoUser()
{
var sqlExecuter = new OracleSqlExecuter(SqlUtility.ConnectionString, new ConsoleLogProvider(), new NullUserInfo());
var result = new List <object>();
sqlExecuter.ExecuteReader("SELECT SYS_CONTEXT('USERENV','CLIENT_INFO') FROM DUAL", reader => result.Add(reader[0]));
Console.WriteLine(result.Single());
Assert.AreEqual(typeof(DBNull), result.Single().GetType());
}
public void ExecuteSql_LoginError()
{
string userId = "U" + Guid.NewGuid().ToString().Replace("-", "");
string password = "******" + Guid.NewGuid().ToString().Replace("-", "");
string dataSource = "localhost:1521/xe";
string connectionString = @"User Id=" + userId + ";Password="******";Data Source=" + dataSource + ";";
OracleSqlExecuter sqlExecuter = new OracleSqlExecuter(connectionString, new ConsoleLogProvider(), new NullUserInfo());
var ex = TestUtility.ShouldFail(() => sqlExecuter.ExecuteSql(new[] { "SELECT 123 FROM DUAL" }), userId, dataSource);
Assert.IsFalse(ex.ToString().Contains(password));
}
public void ExecuteSql_SaveLoadTest()
{
OracleSqlExecuter sqlExecuter = GetSqlExecuter();
string table = GetRandomTableName();
sqlExecuter.ExecuteSql(new[]
{
"CREATE TABLE " + table + " ( A INTEGER )",
"INSERT INTO " + table + " VALUES (123)"
});
int actual = 0;
sqlExecuter.ExecuteReader("SELECT * FROM " + table, dr => actual = dr.GetInt32(0));
Assert.AreEqual(123, actual);
}
public void SendUserInfoInSqlContext_ReadWithUser()
{
var testUser = new TestUserInfo
{
IsUserRecognized = true,
UserName = "******",
Workstation = "HAL9000"
};
var sqlExecuter = new OracleSqlExecuter(SqlUtility.ConnectionString, new ConsoleLogProvider(), testUser);
var result = new List <string>();
sqlExecuter.ExecuteReader(@"SELECT SYS_CONTEXT('USERENV','CLIENT_INFO') FROM DUAL", reader => result.Add(reader[0].ToString()));
string clientInfo = result.Single();
TestUtility.AssertContains(clientInfo, testUser.UserName, "CLIENT_INFO should contain username.");
TestUtility.AssertContains(clientInfo, testUser.Workstation, "CLIENT_INFO should contain client workstation.");
Assert.AreEqual(SqlUtility.UserContextInfoText(testUser), clientInfo);
}
public void ExecuteSql_CaseInsenitive()
{
Console.WriteLine("NationalLanguage: " + SqlUtility.NationalLanguage);
string table = GetRandomTableName();
var sqlExecuter = GetSqlExecuter();
sqlExecuter.ExecuteSql(new[] {
"CREATE TABLE " + table + " ( S NVARCHAR2(256) )",
"INSERT INTO " + table + " VALUES ('a')",
"INSERT INTO " + table + " VALUES ('A')",
"INSERT INTO " + table + " VALUES ('b')",
"INSERT INTO " + table + " VALUES ('B')",
});
var result = new List<string>();
sqlExecuter.ExecuteReader("SELECT * FROM " + table + " WHERE S LIKE 'a%'",
reader => result.Add(SqlUtility.EmptyNullString(reader, 0)));
Assert.AreEqual(
TestUtility.DumpSorted(new[] { "a", "A" }),
TestUtility.DumpSorted(result),
"Comparison will be case insensitive depending on SqlUtility.NationalLanguage (see NLS_SORT), provided in connection string ProviderName (for example Rhetos.Oracle.GENERIC_M_CI or Rhetos.Oracle.XGERMAN_CI).");
result.Clear();
sqlExecuter = new OracleSqlExecuter(SqlUtility.ConnectionString, new ConsoleLogProvider(), new NullUserInfo());
sqlExecuter.ExecuteReader("SELECT * FROM " + table + " WHERE S LIKE 'a%'",
reader => result.Add(SqlUtility.EmptyNullString(reader, 0)));
Assert.AreEqual(
TestUtility.DumpSorted(new[] { "a", "A" }),
TestUtility.DumpSorted(result),
"Using new instance of SqlExecuter.");
}
public void SendUserInfoInSqlContext_WriteWithUser()
{
var testUser = new TestUserInfo
{
IsUserRecognized = true,
UserName = "******",
Workstation = "HAL9000"
};
var sqlExecuter = new OracleSqlExecuter(SqlUtility.ConnectionString, new ConsoleLogProvider(), testUser);
string table = GetRandomTableName();
var result = new List<string>();
sqlExecuter.ExecuteSql(new [] { @"CREATE TABLE " + table + " AS SELECT SYS_CONTEXT('USERENV','CLIENT_INFO') ClientInfo FROM DUAL" });
sqlExecuter.ExecuteReader(@"SELECT * FROM " + table, reader => result.Add(reader[0].ToString()));
var clientInfo = result.Single();
TestUtility.AssertContains(clientInfo, testUser.UserName, "CLIENT_INFO should contain username.");
TestUtility.AssertContains(clientInfo, testUser.Workstation, "CLIENT_INFO should contain client workstation.");
Assert.AreEqual(SqlUtility.UserContextInfoText(testUser), clientInfo);
}
public void SendUserInfoInSqlContext_NoUser()
{
var sqlExecuter = new OracleSqlExecuter(SqlUtility.ConnectionString, new ConsoleLogProvider(), new NullUserInfo());
var result = new List<object>();
sqlExecuter.ExecuteReader("SELECT SYS_CONTEXT('USERENV','CLIENT_INFO') FROM DUAL", reader => result.Add(reader[0]));
Console.WriteLine(result.Single());
Assert.AreEqual(typeof(DBNull), result.Single().GetType());
}
public void ExecuteSql_LoginError()
{
string userId = "U" + Guid.NewGuid().ToString().Replace("-", "");
string password = "******" + Guid.NewGuid().ToString().Replace("-", "");
string dataSource = "localhost:1521/xe";
string connectionString = @"User Id=" + userId + ";Password="******";Data Source=" + dataSource + ";";
OracleSqlExecuter sqlExecuter = new OracleSqlExecuter(connectionString, new ConsoleLogProvider(), new NullUserInfo());
var ex = TestUtility.ShouldFail(() => sqlExecuter.ExecuteSql(new[] { "SELECT 123 FROM DUAL" }), userId, dataSource);
Assert.IsFalse(ex.ToString().Contains(password));
}
