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