public async Task ShouldIgnoreTables()
{
using (var command = new DB2Command("DROP TABLE IF EXISTS Foo; CREATE TABLE Foo (Value INT);", _connection))
{
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS Bar; CREATE TABLE Bar (Value INT);";
command.ExecuteNonQuery();
for (int i = 0; i < 100; i++)
{
command.Parameters.Add(new DB2Parameter("Value", i));
command.CommandText = "INSERT INTO Foo VALUES (?);";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Bar VALUES (?);";
command.ExecuteNonQuery();
command.Parameters.Clear();
}
var checkPoint = new Checkpoint()
{
DbAdapter = DbAdapter.Informix,
SchemasToInclude = new[] { "informix" },
TablesToIgnore = new[] { "foo" }
};
await checkPoint.Reset(_connection);
command.CommandText = "SELECT COUNT(1) FROM Foo";
command.ExecuteScalar().ShouldBe(100);
command.CommandText = "SELECT COUNT(1) FROM Bar";
command.ExecuteScalar().ShouldBe(0);
}
}
public async Task ShouldHandleRelationships()
{
using (var command = new DB2Command("DROP TABLE IF EXISTS Foo; CREATE TABLE Foo (Value INT PRIMARY KEY);", _connection))
{
command.ExecuteNonQuery();
command.CommandText = @"DROP TABLE IF EXISTS Bar;
CREATE TABLE Bar (
Value INT,
FooValue INT,
FOREIGN KEY (FooValue) REFERENCES Foo(Value)
);";
command.ExecuteNonQuery();
for (int i = 0; i < 100; i++)
{
command.Parameters.Add(new DB2Parameter("Value1", i));
command.Parameters.Add(new DB2Parameter("Value2", i));
command.CommandText = "INSERT INTO Foo VALUES (?);";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Bar VALUES (?, ?);";
command.ExecuteNonQuery();
command.Parameters.Clear();
}
command.CommandText = "SELECT COUNT(1) FROM Foo";
command.ExecuteScalar().ShouldBe(100);
command.CommandText = "SELECT COUNT(1) FROM Bar";
command.ExecuteScalar().ShouldBe(100);
var checkPoint = new Checkpoint
{
DbAdapter = DbAdapter.Informix,
SchemasToInclude = new[] { "informix" }
};
try
{
await checkPoint.Reset(_connection);
}
catch
{
_output.WriteLine(checkPoint.DeleteSql ?? string.Empty);
throw;
}
command.CommandText = "SELECT COUNT(1) FROM Foo";
command.ExecuteScalar().ShouldBe(0);
command.CommandText = "SELECT COUNT(1) FROM Bar";
command.ExecuteScalar().ShouldBe(0);
}
}
public static void Initialize()
{
var connectionString = ConfigurationManager.ConnectionStrings["TestDb"].ConnectionString;
var connectionBuilder = new DB2ConnectionStringBuilder(connectionString);
//connect to postgres database to create a new database
var databaseName = connectionBuilder.DBName;
connectionString = connectionBuilder.ToString();
using (var conn = new DB2Connection(connectionString))
{
conn.Open();
using (var cmd = new DB2Command())
{
cmd.CommandText = string.Format(@"
IF db_id('{0}') IS NULL
BEGIN
CREATE DATABASE {0}
END
", databaseName);
cmd.Connection = conn;
var result = cmd.ExecuteScalar();
}
DropTables(conn, databaseName);
}
}
/// <summary>
/// Returns the first column of the first row in the executed query. Additional rows and columns are ignored.
/// </summary>
/// <param name="command">Command (Text command or Stored Procedure)</param>
/// <param name="type">Type of command (text, stored procedure or table-direct)</param>
/// <returns></returns>
public object ExecuteScalar(string command, CommandType type)
{
object value = null;
try
{
using (DB2Connection connection = new DB2Connection(connectionString.ConnectionString))
{
using (DB2Command cmd = new DB2Command(command))
{
cmd.Connection = connection;
foreach (DB2Parameter parameter in Parameters)
{
cmd.Parameters.Add(parameter);
}
cmd.CommandType = type;
cmd.Connection.Open();
value = cmd.ExecuteScalar();
cmd.Connection.Close();
}
}
}
catch (Exception ex)
{
Error aError = new Error(ex.Source, ex.Message, GetCurrentMethod());
ErrorList.Add(aError);
}
return(value);
}
/// <summary>
/// 执行查询,返回查询结果的第一行第一列
/// </summary>
/// <typeparam name="T">返回值类型</typeparam>
/// <param name="sql">SQL查询语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>查询结果的第一行第一列</returns>
public override T ExecuteScalar <T>(string sql, KdtParameterCollection parameters)
{
try
{
T value = default(T);
// 执行SQL命令
using (DB2Command cmd = new DB2Command(ReplaceSqlText(ReplaceSqlText(sql, parameters), parameters), _db2Cn))
{
InitCommand(cmd); // 初始化
// 赋值参数
var hasConvertParams = ConvertToSqlParameter(parameters);
foreach (var item in hasConvertParams)
{
cmd.Parameters.Add(item.Value);
}
value = cmd.ExecuteScalar().Convert <T>();
cmd.Cancel();
cmd.Dispose();
}
return(value);
}
catch (Exception ex)
{
KdtLoger.Instance.Error(ex);
throw new DataException(string.Format("执行查询,返回查询结果的第一行第一列错误,原因为:{0}", ex.Message));
}
}
public async Task ShouldHandleSelfRelationships()
{
using (var command = new DB2Command(@"DROP TABLE IF EXISTS Foo;
CREATE TABLE Foo (
Id INT PRIMARY KEY,
ParentId INT NULL
);", _connection))
{
command.ExecuteNonQuery();
command.CommandText = "ALTER TABLE Foo ADD CONSTRAINT (FOREIGN KEY (ParentId) REFERENCES Foo (Id) CONSTRAINT FK_Parent1)";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Foo (Id) VALUES (?)";
command.Parameters.Add(new DB2Parameter("Value", 1));
command.ExecuteNonQuery();
command.Parameters.Clear();
for (int i = 1; i < 100; i++)
{
command.CommandText = "INSERT INTO Foo VALUES (?, ?)";
command.Parameters.Add(new DB2Parameter("Value1", i + 1));
command.Parameters.Add(new DB2Parameter("Value2", i));
command.ExecuteNonQuery();
command.Parameters.Clear();
}
command.CommandText = "SELECT COUNT(1) FROM Foo";
command.ExecuteScalar().ShouldBe(100);
var checkPoint = new Checkpoint
{
DbAdapter = DbAdapter.Informix,
SchemasToInclude = new[] { "informix" }
};
try
{
await checkPoint.Reset(_connection);
}
catch
{
_output.WriteLine(checkPoint.DeleteSql ?? string.Empty);
throw;
}
command.CommandText = "SELECT COUNT(1) FROM Foo";
command.ExecuteScalar().ShouldBe(0);
}
}
public async Task ShouldIncludeSchemas()
{
const string user_1 = "a";
const string user_2 = "b";
await ManageUser(user_1);
await ManageUser(user_2);
using (var command = new DB2Command($"DROP TABLE IF EXISTS {user_1}.Fooo; CREATE TABLE {user_1}.Fooo (Value INT)", _connection))
{
command.ExecuteNonQuery();
command.CommandText = $"DROP TABLE IF EXISTS {user_2}.Baar; CREATE TABLE {user_2}.Baar (Value INT)";
command.ExecuteNonQuery();
for (int i = 0; i < 100; i++)
{
command.Parameters.Add(new DB2Parameter("Value", i));
command.CommandText = $"INSERT INTO {user_1}.Fooo VALUES (?)";
command.ExecuteNonQuery();
command.CommandText = $"INSERT INTO {user_2}.Baar VALUES (?)";
command.ExecuteNonQuery();
command.Parameters.Clear();
}
var checkPoint = new Checkpoint
{
DbAdapter = DbAdapter.Informix,
SchemasToInclude = new[] { user_2 }
};
try
{
await checkPoint.Reset(_connection);
}
catch
{
_output.WriteLine(checkPoint.DeleteSql ?? string.Empty);
throw;
}
command.CommandText = $"SELECT COUNT(1) FROM {user_1}.Fooo";
command.ExecuteScalar().ShouldBe(100);
command.CommandText = $"SELECT COUNT(1) FROM {user_2}.Baar";
command.ExecuteScalar().ShouldBe(0);
}
}
/// <summary>
/// 执行一条SQL语句，返回一个结果对象
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(string connectionString, string cmdText, params DB2Parameter[] commandParameters)
{
using (DB2Connection connection = new DB2Connection(connectionString))
{
connection.Open();
DB2Command cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdText;
return(cmd.ExecuteScalar());
}
}
public string SingleDataGlobal(string sql)
{
string val;
DB2Command command = new DB2Command();
command.Connection = connection;
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
command.CommandText = sql;
val = Convert.ToString(command.ExecuteScalar());
return(val);
}
public Object ExecuteScalar(String sql)
{
DB2Command cmd = new DB2Command(sql, co);
Object o = null;
try
{
o = cmd.ExecuteScalar();
_lastError = "";
}
catch (Exception e)
{
_lastError = e.Message;
}
return(o);
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="sql">计算查询结果语句</param>
/// <param name="ctype">类型</param>
/// <param name="param">参数</param>
/// <returns>查询结果（object）</returns>
public object GetExecuteScalar(string sql, CommandType ctype, params IDataParameter[] param)
{
Open();
var cmd = new DB2Command();
try
{
PrepareCommand(cmd, _connSql, null, ctype, sql, param);
var obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
cmd.Dispose();
return(Equals(obj, null) || Equals(obj, DBNull.Value) ? null : obj);
}
finally
{
Close();
}
}
public string SingleData(string sql)
{
string val = "";
DB2Command command = new DB2Command();
DB2Connection con;
con = OpenConnection();
command.Connection = con;
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
command.CommandText = sql;
try
{
val = Convert.ToString(command.ExecuteScalar());
}
catch { }
CloseConnection(con);
return(val);
}
public string InvokeIbmDb2Query(string tableName)
{
var teamMembers = new List <string>();
using (var connection = new DB2Connection(Db2Configuration.Db2ConnectionString))
{
connection.Open();
using (var command = new DB2Command("SELECT LASTNAME FROM EMPLOYEE FETCH FIRST ROW ONLY", connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
teamMembers.Add(reader.GetString(reader.GetOrdinal("LASTNAME")));
}
}
}
var insertSql = string.Format(InsertHotelDB2Sql, tableName);
var countSql = string.Format(CountHotelDB2Sql, tableName);
var deleteSql = string.Format(DeleteHotelDB2Sql, tableName);
using (var command = new DB2Command(insertSql, connection))
{
var insertCount = command.ExecuteNonQuery();
}
using (var command = new DB2Command(countSql, connection))
{
var hotelCount = command.ExecuteScalar();
}
using (var command = new DB2Command(deleteSql, connection))
{
var deleteCount = command.ExecuteNonQuery();
}
}
return(string.Join(",", teamMembers));
}
public override object ExecuteScalar(string SQLString, CmdParameterCollection cmdParms)
{
return(this.ExecWithLog <object>(count =>
{
using (DB2Command cmd = new DB2Command())
{
PrepareCommand(cmd, _conn, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
count("0");
return null;
}
else
{
count("1");
return obj;
}
}
}, SQLString, cmdParms));
}
public void IfxConnection_ResultShouldBeOne()
{
/* Arrange */
object actualValue;
const string query = @"SELECT Id
FROM DummyTable";
const string connectionString = "Server=127.0.0.1:9089;Database=dummyifx;UID=informix;PWD=in4mix;Persist Security Info=True;Authentication=Server;";
/* Act */
using (var conn = new DB2Connection(connectionString))
{
conn.Open();
using (var command = new DB2Command(query, conn))
{
actualValue = command.ExecuteScalar();
}
}
/* Assert */
Assert.Equal(1, actualValue);
}
private static async Task ManageUser(string userName)
{
using (var connection = new DB2Connection("Server=127.0.0.1:9089;Database=dummyifx;UID=informix;PWD=in4mix;Persist Security Info=True;Authentication=Server;"))
{
await connection.OpenAsync();
using (var command = new DB2Command($@"SELECT username
FROM sysusers
WHERE username = '******';", connection))
{
if (command.ExecuteScalar() != null)
{
command.CommandText = $"DROP USER {userName};";
command.ExecuteNonQuery();
}
command.CommandText = $"CREATE USER {userName} WITH PROPERTIES USER ifxsurr;";
command.ExecuteNonQuery();
command.CommandText = $"GRANT DBA TO {userName}";
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// 查询符合条件的的记录数
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public int ExecuteCountQuery(string command)
{
int count = 0;
this.Open();
try
{
db2Cmd = new DB2Command(command, db2Conn);
count = Convert.ToInt32(db2Cmd.ExecuteScalar());
}
catch (Exception ex)
{
LogHelper.WriteLogException("Execute sql command error in Db2Operation.ExecuteCountQuery()", ex);
LogHelper.WriteLogError("The sql executed is", command);
throw;
}
finally
{
this.Close();
}
return(count);
}
public async Task ShouldHandleComplexCycles()
{
using (var command = new DB2Command("DROP TABLE IF EXISTS A; CREATE TABLE A (Id INT PRIMARY KEY, B_Id INT NULL)", _connection))
{
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS B; CREATE TABLE B (Id INT PRIMARY KEY, A_Id INT NULL, C_Id INT NULL, D_Id INT NULL)";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS C; CREATE TABLE C (Id INT PRIMARY KEY, D_Id INT NULL)";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS D; CREATE TABLE D (Id INT PRIMARY KEY)";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS E; CREATE TABLE E (Id INT PRIMARY KEY, A_Id INT NULL)";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS F; CREATE TABLE F (Id INT PRIMARY KEY, B_Id INT NULL)";
command.ExecuteNonQuery();
command.CommandText = "ALTER TABLE A ADD CONSTRAINT (FOREIGN KEY (B_Id) REFERENCES B (Id) CONSTRAINT FK_A_B)";
command.ExecuteNonQuery();
command.CommandText = "ALTER TABLE B ADD CONSTRAINT (FOREIGN KEY (A_Id) REFERENCES A (Id) CONSTRAINT FK_B_A)";
command.ExecuteNonQuery();
command.CommandText = "ALTER TABLE B ADD CONSTRAINT (FOREIGN KEY (C_Id) REFERENCES C (Id) CONSTRAINT FK_B_C)";
command.ExecuteNonQuery();
command.CommandText = "ALTER TABLE B ADD CONSTRAINT (FOREIGN KEY (D_Id) REFERENCES D (Id) CONSTRAINT FK_B_D)";
command.ExecuteNonQuery();
command.CommandText = "ALTER TABLE C ADD CONSTRAINT (FOREIGN KEY (D_Id) REFERENCES D (Id) CONSTRAINT FK_C_D)";
command.ExecuteNonQuery();
command.CommandText = "ALTER TABLE E ADD CONSTRAINT (FOREIGN KEY (A_Id) REFERENCES A (Id) CONSTRAINT FK_E_A)";
command.ExecuteNonQuery();
command.CommandText = "ALTER TABLE F ADD CONSTRAINT (FOREIGN KEY (B_Id) REFERENCES B (Id) CONSTRAINT FK_F_B)";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO D (Id) VALUES (1)";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO C (Id, D_Id) VALUES (1, 1)";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO A (Id) VALUES (1)";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO B (Id, C_Id, D_Id) VALUES (1, 1, 1)";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO E (Id, A_Id) VALUES (1, 1)";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO F (Id, B_Id) VALUES (1, 1)";
command.ExecuteNonQuery();
command.CommandText = "UPDATE A SET B_Id = 1";
command.ExecuteNonQuery();
command.CommandText = "UPDATE B SET A_Id = 1";
command.ExecuteNonQuery();
command.CommandText = "SELECT COUNT(1) FROM A";
command.ExecuteScalar().ShouldBe(1);
command.CommandText = "SELECT COUNT(1) FROM B";
command.ExecuteScalar().ShouldBe(1);
command.CommandText = "SELECT COUNT(1) FROM C";
command.ExecuteScalar().ShouldBe(1);
command.CommandText = "SELECT COUNT(1) FROM D";
command.ExecuteScalar().ShouldBe(1);
command.CommandText = "SELECT COUNT(1) FROM E";
command.ExecuteScalar().ShouldBe(1);
command.CommandText = "SELECT COUNT(1) FROM F";
command.ExecuteScalar().ShouldBe(1);
var checkPoint = new Checkpoint
{
DbAdapter = DbAdapter.Informix,
SchemasToInclude = new[] { "informix" }
};
try
{
await checkPoint.Reset(_connection);
}
catch
{
_output.WriteLine(checkPoint.DeleteSql ?? string.Empty);
throw;
}
command.CommandText = "SELECT COUNT(1) FROM A";
command.ExecuteScalar().ShouldBe(0);
command.CommandText = "SELECT COUNT(1) FROM B";
command.ExecuteScalar().ShouldBe(0);
command.CommandText = "SELECT COUNT(1) FROM C";
command.ExecuteScalar().ShouldBe(0);
command.CommandText = "SELECT COUNT(1) FROM D";
command.ExecuteScalar().ShouldBe(0);
command.CommandText = "SELECT COUNT(1) FROM E";
command.ExecuteScalar().ShouldBe(0);
command.CommandText = "SELECT COUNT(1) FROM F";
command.ExecuteScalar().ShouldBe(0);
}
}
public async Task ShouldHandleCircularRelationships()
{
using (var command = new DB2Command(@"DROP TABLE IF EXISTS Parent;
CREATE TABLE Parent (
Id INT PRIMARY KEY,
ChildId INT NULL
);", _connection))
{
command.ExecuteNonQuery();
command.CommandText = @"DROP TABLE IF EXISTS Child;
CREATE TABLE Child (
Id INT PRIMARY KEY,
ParentId INT NULL
);";
command.ExecuteNonQuery();
command.CommandText = @"ALTER TABLE Parent ADD CONSTRAINT (FOREIGN KEY (ChildId) REFERENCES Child (Id) CONSTRAINT FK_Child)";
command.ExecuteNonQuery();
command.CommandText = @"ALTER TABLE Child ADD CONSTRAINT (FOREIGN KEY (ParentId) REFERENCES Parent (Id) CONSTRAINT FK_Parent)";
command.ExecuteNonQuery();
for (int i = 0; i < 100; i++)
{
command.Parameters.Add(new DB2Parameter("Value1", i));
command.Parameters.Add(new DB2Parameter("Value2", DBNull.Value));
command.CommandText = "INSERT INTO Parent VALUES (?, ?);";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Child VALUES (?, ?);";
command.ExecuteNonQuery();
command.Parameters.Clear();
}
command.CommandText = @"UPDATE Parent SET ChildId = 0";
command.ExecuteNonQuery();
command.CommandText = @"UPDATE Child SET ParentId = 1";
command.ExecuteNonQuery();
command.CommandText = "SELECT COUNT(1) FROM Parent";
command.ExecuteScalar().ShouldBe(100);
command.CommandText = "SELECT COUNT(1) FROM Child";
command.ExecuteScalar().ShouldBe(100);
var checkPoint = new Checkpoint
{
DbAdapter = DbAdapter.Informix,
SchemasToInclude = new[] { "informix" }
};
try
{
await checkPoint.Reset(_connection);
}
catch
{
_output.WriteLine(checkPoint.DeleteSql ?? string.Empty);
throw;
}
command.CommandText = "SELECT COUNT(1) FROM Parent";
command.ExecuteScalar().ShouldBe(0);
command.CommandText = "SELECT COUNT(1) FROM Child";
command.ExecuteScalar().ShouldBe(0);
}
}
