public AGENDACollection ReadCollectionByParameter(List <RowsFiltro> RowsFiltro) { FbDataReader dataReader = null; AGENDACollection collection = null; string strSqlCommand = String.Empty; try { if (RowsFiltro != null) { if (RowsFiltro.Count > 0) { strSqlCommand = "SELECT * FROM AGENDA WHERE ("; ArrayList _rowsFiltro = new ArrayList(); RowsFiltro.ForEach(delegate(RowsFiltro i) { string[] item = { i.Condicao.ToString(), i.Campo.ToString(), i.Tipo.ToString(), i.Operador.ToString(), i.Valor.ToString() }; _rowsFiltro.Add(item); }); int _count = 1; foreach (string[] item in _rowsFiltro) { strSqlCommand += "(" + item[1] + " " + item[3]; switch (item[2]) { case ("System.String"): if (item[3].ToUpper() != "LIKE") { strSqlCommand += " '" + item[4] + "')"; } else { strSqlCommand += " '%" + item[4] + "%')"; } break; case ("System.Int16"): if (item[3].ToUpper() != "LIKE") { strSqlCommand += " " + item[4] + ")"; } else { strSqlCommand += " '%" + item[4] + "%')"; } break; case ("System.Int32"): if (item[3].ToUpper() != "LIKE") { strSqlCommand += " " + item[4] + ")"; } else { strSqlCommand += " '%" + item[4] + "%')"; } break; case ("System.Int64"): if (item[3].ToUpper() != "LIKE") { strSqlCommand += " " + item[4] + ")"; } else { strSqlCommand += " '%" + item[4] + "%')"; } break; case ("System.Double"): if (item[3].ToUpper() != "LIKE") { strSqlCommand += " " + item[4] + ")"; } else { strSqlCommand += " '%" + item[4] + "%')"; } break; case ("System.Decimal"): if (item[3].ToUpper() != "LIKE") { strSqlCommand += " " + item[4] + ")"; } else { strSqlCommand += " '%" + item[4] + "%')"; } break; case ("System.Float"): if (item[3].ToUpper() != "LIKE") { strSqlCommand += " " + item[4] + ")"; } else { strSqlCommand += " '%" + item[4] + "%')"; } break; case ("System.Byte"): strSqlCommand += " " + item[4] + ")"; break; case ("System.SByte"): strSqlCommand += " " + item[4] + ")"; break; case ("System.Char"): if (item[3].ToUpper() != "LIKE") { strSqlCommand += " '" + item[4] + "')"; } else { strSqlCommand += " '%" + item[4] + "%')"; } break; case ("System.DateTime"): if (item[3].ToUpper() != "LIKE") { strSqlCommand += " '" + item[4] + "')"; } else { strSqlCommand += " '%" + item[4] + "%')"; } break; case ("System.Guid"): if (item[3].ToUpper() != "LIKE") { strSqlCommand += " '" + item[4] + "')"; } else { strSqlCommand += " '%" + item[4] + "%')"; } break; case ("System.Boolean"): strSqlCommand += " " + item[4] + ")"; break; } if (_rowsFiltro.Count > 1) { if (_count < _rowsFiltro.Count) { strSqlCommand += " " + item[0] + " "; } _count++; } } strSqlCommand += ");"; } else { strSqlCommand = "SELECT * FROM AGENDA "; } } else { strSqlCommand = "SELECT * FROM AGENDA "; } //Verificando a existência de um transação if (dbTransaction != null) { if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand(strSqlCommand, dbCnn); dbCommand.CommandType = CommandType.Text; dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) { dbCnn = new FbConnection(connectionString); } if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand(strSqlCommand, dbCnn); dbCommand.CommandType = CommandType.Text; dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } collection = ExecuteReader(ref collection, ref dataReader, dbCommand); if (dataReader != null) { dataReader.Close(); dataReader.Dispose(); } if (dbTransaction == null) { dbCommand.Transaction.Commit(); dbCnn.Close(); } return(collection); } catch (Exception ex) { // Deleta reader if (dataReader != null) { dataReader.Close(); dataReader.Dispose(); } if (dbTransaction != null) { this.RollbackTransaction(); } else { if (dbCommand.Transaction != null) { dbCommand.Transaction.Rollback(); } if (dbCnn.State == ConnectionState.Open) { dbCnn.Close(); } } throw ex; } }
public static void run_replace(FbConnectionStringBuilder fc) { com.Add(@"DECLARE EXTERNAL FUNCTION TRIM_ CSTRING(256) RETURNS CSTRING(256) ENTRY_POINT 'fn_trim' MODULE_NAME 'rfunc'"); com.Add(@"DECLARE EXTERNAL FUNCTION IIF_ INTEGER, INTEGER, INTEGER RETURNS INTEGER BY VALUE ENTRY_POINT 'fn_iif' MODULE_NAME 'rfunc'"); com.Add("execute procedure re_bild_triggers(2,null)"); com.Add("EXECUTE PROCEDURE Re_bild_trees(2, NULL)"); fc.UserID = "sysdba"; fc.Password = "******"; #region PROCEDURES using (FbConnection fb = new FbConnection(fc.ConnectionString)) { try { fb.Open(); using (FbTransaction ft = fb.BeginTransaction()) { using (FbCommand fcon = new FbCommand(sql_procedures, fb, ft)) { using (FbDataReader fr = fcon.ExecuteReader()) { while (fr.Read()) { var dll_command = new StringBuilder("create or alter procedure "); if (is_reserv(fr[0].ToString())) { dll_command.AppendLine("\"" + fr[0].ToString() + "\""); } else { dll_command.AppendLine(fr[0].ToString()); } //Список полей string fields_in = ""; string fields_out = ""; bool is_out = false; using (FbCommand fcon_a = new FbCommand(sql_procedures_f, fb, ft)) { fcon_a.Parameters.Add("@a", FbDbType.VarChar, 31); fcon_a.Parameters[0].Value = fr[0].ToString(); using (FbDataReader fr_a = fcon_a.ExecuteReader()) { while (fr_a.Read()) { //резервное? var fields_name = fr_a[0].ToString().Trim(); if (is_reserv(fields_name)) { fields_name = "\"" + fields_name + "\""; } //Извлекаем тип данных string field_type = get_field_type(fr_a[3].ToString(), fr_a[4].ToString(), fr_a[5].ToString(), fr_a[6].ToString(), fr_a[7].ToString(), fr_a[8].ToString()); //теперь в зависимости от типа параметра (вх/вых) - запишем в определенный блок if (fr_a[2].ToString() == "0") { //входной if (fields_in == "") { fields_in += "( " + fields_name + " " + field_type; } else { fields_in += ",\n " + fields_name + " " + field_type; } } else { //значит выходной if (!is_out) { //ставим флаг, что есть выходные параметры is_out = true; } if (fields_out == "") { fields_out += "returns ( " + fields_name + " " + field_type; } else { fields_out += ",\n " + fields_name + " " + field_type; } } } fr_a.Dispose(); } fcon_a.Dispose(); } //теперь соединяем if (fields_in != "") { fields_in += "\n )"; } if (fields_out != "") { fields_out += "\n )"; } dll_command.AppendLine(fields_in + fields_out + "\n AS\n"); //Добавляем содержимое try { using (FbCommand fcon_b = new FbCommand(sql_procedures_b, fb, ft)) { fcon_b.Parameters.Add("@a", FbDbType.VarChar, 31); fcon_b.Parameters[0].Value = fr[0].ToString(); using (FbDataReader fr_b = fcon_b.ExecuteReader()) { while (fr_b.Read()) { string line = regexTrim.Replace(fr_b.GetString(0), "TRIM_"); line = regexIIF.Replace(line, "IIF_"); dll_command.AppendLine(line); //.Replace("TRIM", "TRIM_").Replace("Trim", "TRIM_").Replace("trim", "TRIM_").Replace("IIF", "IIF_").Replace("iif", "IIF_").Replace("Iif", "IIF_"); } fr_b.Dispose(); } fcon_b.Dispose(); } } catch { } com.Add(dll_command.ToString()); } fr.Dispose(); } fcon.Dispose(); } ft.Commit(); ft.Dispose(); } } catch { } finally { fb.Close(); } fb.Dispose(); } #endregion #region TRIGGERS using (FbConnection fb = new FbConnection(fc.ConnectionString)) { try { fb.Open(); using (FbTransaction ft = fb.BeginTransaction()) { using (FbCommand fcon = new FbCommand(sql_triggers, fb, ft)) { using (FbDataReader fr = fcon.ExecuteReader()) { while (fr.Read()) { var dll_command = new StringBuilder("create or alter trigger " + fr[0].ToString().Trim() + " FOR " + fr[1].ToString()); //активность if (fr[5].ToString().Trim() == "1") { dll_command.Append("\n" + "INACTIVE"); } else { dll_command.Append("\n" + "ACTIVE"); } //теперь узнаем что за тип триггера switch (fr[3].ToString().Trim()) { case "1": dll_command.Append(" before insert "); break; case "2": dll_command.Append(" after insert "); break; case "3": dll_command.Append(" before update "); break; case "4": dll_command.Append(" after update "); break; case "5": dll_command.Append(" before delete "); break; case "6": dll_command.Append(" after delete "); break; case "17": dll_command.Append(" before insert or update "); break; case "25": dll_command.Append(" before insert or delete"); break; case "113": dll_command.Append(" before insert or update or delete "); break; case "27": dll_command.Append(" before update or delete "); break; case "18": dll_command.Append(" after insert or update "); break; case "26": dll_command.Append(" after insert or delete"); break; case "114": dll_command.Append(" after insert or update or delete "); break; case "28": dll_command.Append(" after update or delete "); break; } //позиция триггера dll_command.Append(" position " + fr[2].ToString().Trim()); //Добавляем содержимое using (FbCommand fcon_b = new FbCommand(sql_triggers_b, fb, ft)) { fcon_b.Parameters.Add("@a", FbDbType.VarChar, 31); fcon_b.Parameters[0].Value = fr[0].ToString(); using (FbDataReader fr_b = fcon_b.ExecuteReader()) { while (fr_b.Read()) { dll_command.Append("\n" + fr_b.GetString(0)); } fr_b.Dispose(); } fcon_b.Dispose(); } string command = regexTrim.Replace(dll_command.ToString(), "TRIM_"); command = regexIIF.Replace(command, "IIF_"); com.Add(command); } fr.Dispose(); } fcon.Dispose(); } ft.Commit(); ft.Dispose(); } } catch { } finally { fb.Close(); } fb.Dispose(); } #endregion com.Add("DROP EXTERNAL FUNCTION TRIM"); com.Add("DROP EXTERNAL FUNCTION IIF"); #region execute com using (FbConnection fb = new FbConnection(fc.ConnectionString)) { try { fb.Open(); using (FbTransaction ft = fb.BeginTransaction()) { using (FbCommand fcon = new FbCommand("", fb, ft)) { foreach (string cmd in com) { fcon.CommandText = cmd; fcon.ExecuteNonQuery(); } fcon.Dispose(); } ft.Commit(); ft.Dispose(); } } catch { } finally { fb.Close(); } fb.Dispose(); } #endregion #region SAVE //StringBuilder sb = new StringBuilder(); //sb.AppendLine("set term ^;"); //foreach (string cmd in com) //{ // sb.AppendLine(cmd + "^"); //} string sb = String.Concat("set term ^;", Environment.NewLine, String.Join("^" + Environment.NewLine, com), "^", Environment.NewLine); sb = sb.Replace("{", "{{").Replace("}", "}}"); com.Clear(); try { using (StreamWriter outfile = new StreamWriter(File.Create(@"d:\data.sql"), Encoding.Default)) { outfile.Write(sb, Encoding.Default); } } catch { } #endregion }
public AGENDAEntity Read(int IDAGENDA) { FbDataReader reader = null; try { //Verificando a existência de um transação aberta if (dbTransaction != null) { if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Rea_AGENDA", dbCnn); dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) { dbCnn = ((FbConnection)GetConnectionDB()); } if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Rea_AGENDA", dbCnn); dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.Parameters.AddWithValue("@IDAGENDA", IDAGENDA); //PrimaryKey reader = dbCommand.ExecuteReader(); AGENDAEntity entity = null; if (reader.HasRows) { while (reader.Read()) { entity = FillEntityObject(ref reader); } } // Deleta reader if (reader != null) { reader.Close(); reader.Dispose(); } // Fecha conexão if (dbTransaction == null) { dbCommand.Transaction.Commit(); if (dbCnn.State == ConnectionState.Open) { dbCnn.Close(); } } return(entity); } catch (Exception ex) { // Deleta reader if (reader != null) { reader.Close(); reader.Dispose(); } if (dbTransaction != null) { this.RollbackTransaction(); } else { if (dbCommand.Transaction != null) { dbCommand.Transaction.Rollback(); } if (dbCnn.State == ConnectionState.Open) { dbCnn.Close(); } } throw ex; } }
public LIS_LABELTELACollection ReadCollection() { FbDataReader dataReader = null; try { LIS_LABELTELACollection collection = null; //Verificando a existência de um transação aberta if (dbTransaction != null) { if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("SELECT * FROM LIS_LABELTELA", dbCnn); dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) { dbCnn = ((FbConnection)GetConnectionDB()); } if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("SELECT * FROM LIS_LABELTELA", dbCnn); dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } // Tipo do comando de banco Procedure ou SQL dbCommand.CommandType = CommandType.Text; collection = ExecuteReader(ref collection, ref dataReader, dbCommand); if (dataReader != null) { dataReader.Close(); dataReader.Dispose(); } if (dbTransaction == null) { dbCommand.Transaction.Commit(); dbCnn.Close(); } return(collection); } catch (Exception ex) { // Deleta reader if (dataReader != null) { dataReader.Close(); dataReader.Dispose(); } if (dbTransaction != null) { this.RollbackTransaction(); } else { if (dbCommand.Transaction != null) { dbCommand.Transaction.Rollback(); } if (dbCnn.State == ConnectionState.Open) { dbCnn.Close(); } } throw ex; } }