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