/// <summary>
/// список типов населенных пунктов
/// </summary>
/// <returns>список типов населенных пунктов</returns>
public List <string> GetLocalityTypes()
{
List <string> localities = new List <string>();
string connectionString = cs.ToString();
FbConnection connection = new FbConnection(connectionString);
connection.Open();
string selectSQL;
selectSQL = string.Format("SELECT TYPE FROM SETTLEMENTS ORDER BY CODE");
DataSet myDataSet = new DataSet();
FbCommand sel_com = new FbCommand(selectSQL, connection);
FbDataAdapter myDataAdapter = new FbDataAdapter(sel_com);
myDataAdapter.Fill(myDataSet, "LOCTYPES");
connection.Close();
DataRowCollection dra = myDataSet.Tables["LOCTYPES"].Rows;
foreach (DataRow dr in dra)
{
localities.Add(dr[0].ToString());
}
return(localities);
}
/// <summary>
/// Gets the name of the tables within this database, excluding system tables ($).
/// </summary>
/// <returns>ICollection<string> - Collection of table names</returns>
public ICollection <string> GetTableNames()
{
ICollection <string> results = new List <string>();
using (FbConnection cn = (FbConnection)GetConnnection())
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
var tableNames = cn.GetSchema("Tables");
foreach (System.Data.DataRow row in tableNames.Rows)
{
if (!row["TABLE_NAME"].ToString().Contains("$"))
{
results.Add((string)row["TABLE_NAME"]);
System.Diagnostics.Debug.WriteLine($"Adding table: {row["TABLE_NAME"]}");
}
}
cn.Close();
}
}
return(results);
}
public CanalProgramado ObtenerCanalProgramado(int AClave)
{
CanalProgramado pCanalProgramado = null;
string pSentencia = "SELECT A.FECHA, A.GRANJA AS CLAVE_GRANJA, B.NOMBRE AS GRANJA, A.LOTE, A.CANALES, A.ACUMULADOS FROM DRASCCALL A JOIN DRASGRAN B ON B.CLAVE = A.GRANJA WHERE A.CLAVE = @CLAVE";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@CLAVE", FbDbType.Integer).Value = AClave;
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
while (reader.Read())
{
pCanalProgramado = new CanalProgramado();
pCanalProgramado.Fecha = reader["FECHA"] != DBNull.Value ? (DateTime?)reader["FECHA"] : null;
pCanalProgramado.Clave_Granja = reader["CLAVE_GRANJA"] != DBNull.Value ? (int)reader["CLAVE_GRANJA"] : -1;
pCanalProgramado.Granja = reader["GRANJA"] != DBNull.Value ? (string)reader["GRANJA"] : "";
pCanalProgramado.Lote = reader["LOTE"] != DBNull.Value ? (int)reader["LOTE"] : -1;
pCanalProgramado.Canales = reader["CANALES"] != DBNull.Value ? (Int16)reader["CANALES"] : (Int16)0;
pCanalProgramado.Acumulados = reader["ACUMULADOS"] != DBNull.Value ? (Int16)reader["ACUMULADOS"] : (Int16)0;
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return(pCanalProgramado);
}
/// <summary>
/// список документов для заявления
/// </summary>
/// <returns>список документов для заявления</returns>
public List <string> GetDocTypes()
{
List <string> docs = new List <string>();
string connectionString = cs.ToString();
FbConnection connection = new FbConnection(connectionString);
connection.Open();
string selectSQL;
selectSQL = string.Format("SELECT DOCNAME FROM DOCTYPES ORDER BY DOCTYPE");
DataSet myDataSet = new DataSet();
FbCommand sel_com = new FbCommand(selectSQL, connection);
FbDataAdapter myDataAdapter = new FbDataAdapter(sel_com);
myDataAdapter.Fill(myDataSet, "DOCTYPES");
connection.Close();
DataRowCollection dra = myDataSet.Tables["DOCTYPES"].Rows;
foreach (DataRow dr in dra)
{
docs.Add(dr[0].ToString());
}
return(docs);
}
public object Generate(int maxValue)
{
connectionString = GlobalSetting.CreateConectionString(
@"C:\Users\user\Documents\visual studio 2013\Projects\WpfRandomValue\WPFDatabase.fdb",
"SYSDBA", "masterkey", "WIN1250");
using (var conn = new FbConnection(connectionString))
{
conn.Open();
using (BCRandomStream randomStream = new BCRandomStream(maxValue))
{
var command = new FbCommand();
command.Connection = conn;
command.CommandText = string.Format("INSERT INTO NUMBERTABLE(NUMBER) VALUES (@number)");//, );
FbParameter param = new FbParameter();
param.ParameterName = "@number";
param.Value = randomStream.Read();
command.Parameters.Add(param);
return(command.ExecuteScalar());
}
}
}
private void cb_choose_war_Loaded(object sender, RoutedEventArgs e)
{
FbConnectionStringBuilder fb_con = new FbConnectionStringBuilder();
fb_con.Charset = "UTF8";
fb_con.UserID = "SYSDBA";
fb_con.Password = "******";
fb_con.Database = "C:\\Users\\ARTEM\\Documents\\Visual Studio 2013\\Projects\\GreatestApplicatioInMyLife\\DB\\MAINDB.fdb";
fb_con.ServerType = 0;
fb = new FbConnection(fb_con.ToString());
fb.Open();
cb_choose_war.Items.Clear();
FbCommand sqlforcomb = new FbCommand("select NAME_WAR from GET_WAR", fb);
FbDataReader readercomb = sqlforcomb.ExecuteReader();
if (readercomb.HasRows) // если есть данные
{
DataSet newset1 = new DataSet("newset1");
DataTable dtcomb = new DataTable();
while (readercomb.Read())
{
try
{
string resultvalue = readercomb.GetString(0);
cb_choose_war.Items.Add(resultvalue);
}
catch { }
}
}
}
public static List <Pitanje> UcitajPitanja(int grupaId)
{
List <Pitanje> l = new List <Pitanje>();
using (FbConnection con = new FbConnection(M.Baza.connectionString))
{
con.Open();
using (FbCommand cmd = new FbCommand("SELECT PITANJEID, NASLOV, ODGOVOR1, ODGOVOR2, ODGOVOR3, ODGOVOR4, ODGOVOR5, POENA, PITANJE FROM KVIZ_PITANJA WHERE KVIZ_GRUPA_ID = @ID", con))
{
cmd.Parameters.AddWithValue("@ID", grupaId);
FbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
l.Add(new Pitanje {
id = Convert.ToInt32(dr[0]), naslov = dr[1].ToString(), odgovor1 = dr[2].ToString(), odgovor2 = dr[3].ToString(), odgovor3 = dr[4].ToString(), odgovor4 = dr[5].ToString(), odgovor5 = dr[6].ToString(), poena = Convert.ToDouble(dr[7]), pitanje = dr[8].ToString()
});
}
}
con.Close();
}
return(l);
}
private void hotSpot1_Click_1(object sender, EventArgs e)
{
txtOpenResult.Text = String.Empty;
pnlConnectState.BackColor = System.Drawing.Color.Yellow;
DBRegistrationClass dbr = new DBRegistrationClass();
dbr = DBReg.Clone();
dbr.User = txtUserName.Text;
dbr.Password = txtPassword.Text;
var con = new FbConnection(ConnectionStrings.Instance.MakeConnectionString(dbr));
try
{
Stopwatch sw = new Stopwatch();
sw.Start();
if (con.State != ConnectionState.Open)
{
con.Close();
}
con.Open();
Thread.Sleep(100);
if (con.State == ConnectionState.Open)
{
pnlConnectState.BackColor = System.Drawing.Color.Green;
Application.DoEvents();
con.Close();
txtOpenResult.Text = "succeeded";
}
}
catch (Exception ex)
{
pnlConnectState.BackColor = System.Drawing.Color.Red;
txtOpenResult.Text = ex.Message;
Application.DoEvents();
}
}
protected void Page_Load(object sender, EventArgs e)
{
//////////////////////////////////Выполняем подключение к базе данных
try
{
string path = Server.MapPath(@"DataBase\BIZ_TEST.FDB");
FbConnectionStringBuilder fb_con = new FbConnectionStringBuilder();
fb_con.Charset = "UTF8";
fb_con.UserID = "SYSDBA";
fb_con.Password = "******";
fb_con.Database = path;
fb_con.ServerType = 0;
fb = new FbConnection(fb_con.ToString());
fb.Open();
////////////////////////////////Заполняем список категорий
FbCommand sqlforkat = new FbCommand("select KAT from SEL_KATEGORY", fb);
FbDataReader readerkat = sqlforkat.ExecuteReader();
DataTable dt_kat = new DataTable();
dt_kat.Load(readerkat);
KategoryList.Items.Clear();
for (int i = 0; i < dt_kat.Rows.Count; i++)
{
KategoryList.Items.Add(dt_kat.Rows[i][0].ToString());
}
}
catch
{
LabelMes.ForeColor = Color.Red;
LabelMes.Text = "Проблемы с подключением к БД";
}
}
private void pvLogar(string strUsu, string strPass)
{
sqlConn = new FbConnection(clConexao.stringConexao);
try
{
_Sql = "SELECT ID_USU, NOME_USU FROM USUARIOS WHERE NOME_USU = @NOME AND SENHA = @SENHA";
FbCommand comandoSql = new FbCommand(_Sql, sqlConn);
comandoSql.Parameters.Add("@NOME", strUsu);
comandoSql.Parameters.Add("@SENHA", strPass);
sqlConn.Open();
leitorSql = comandoSql.ExecuteReader();
if (leitorSql.Read())
{
v[0] = leitorSql["ID_USU"].ToString();
v[1] = leitorSql["NOME_USU"].ToString();
v[2] = System.Windows.Forms.SystemInformation.ComputerName.ToString();
v[3] = txtSenha.Text;
frmPrincipal principal = new frmPrincipal(v);
principal.Show();
this.Hide();
}
else
{
MessageBox.Show("Usuário ou senha inválidos. \n", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception erro)
{
MessageBox.Show("Erro no método de conexão. Comunique o administrador do sistema.\nErro original: " + erro.Message, "Erro conexão", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public string RetornaProximoValorGenerator(string sNomeGernerator, int sTamanho)
{
try
{
string sNumArq = "";
FbCommand sSql = new FbCommand();
FbConnection Conn = HlpDbFuncoesGeral.conexao;
sSql.Connection = Conn;
Conn.Open();
sSql.CommandText = "SP_CHAVEPRI";
sSql.CommandType = CommandType.StoredProcedure;
sSql.Parameters.Clear();
sSql.Parameters.Add("@SNOMEGENERATOR", FbDbType.VarChar, 31).Value = sNomeGernerator;
sNumArq = sSql.ExecuteScalar().ToString();
return sNumArq.PadLeft(sTamanho, '0');
}
catch (Exception ex)
{
throw ex;
}
}
public AddUserWindow(User user)
{
InitializeComponent();
WindowStartupLocation = WindowStartupLocation.CenterScreen;
isEdit = true;
AddUserW.Title = "Edycja czytelnika";
HeaderL.Text = "Edycja czytelnika";
nameTB.Text = user.IMIE;
surnameTB.Text = user.NAZWISKO;
loginTB.Text = user.LOGIN;
emailTB.Text = user.EMAIL;
birthdateDP.SelectedDate = user.DATA_URODZENIA;
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.DataSource = (string)Application.Current.Properties["dataSource"];
csb.Port = 3050;
csb.Database = (string)Application.Current.Properties["dataBase"];
csb.UserID = (string)Application.Current.Properties["userID"];
csb.Password = (string)Application.Current.Properties["password"];
csb.ServerType = FbServerType.Default;
connection = new FbConnection(csb.ToString());
connection.Open();
idUser = user.ID_UZYTKOWNIK;
}
/// <summary>
/// Возвращает максимальное значение ID из таблицы JOURNAL_OF_USE
/// </summary>
/// <returns></returns>
public int ReturnMaxIdJournal()
{
int idjournal = 0;
using (FbConnection con = new FbConnection(props.Fields.ConnectionString))
{
try
{
con.Open();
FbCommand command = new FbCommand(@"SELECT MAX(JOURNAL_OF_USE.ID) FROM JOURNAL_OF_USE", con);
FbDataReader dr = command.ExecuteReader();
dr.Read();
idjournal = dr.GetInt32(0);
con.Close();
}
catch (Exception error)
{
MessageBox.Show("Не удалось получить максимальное значение ID.\nПодробности: " + error.Message, "Журнал учёта бланков и распорядительных документов суда", MessageBoxButtons.OK, MessageBoxIcon.Asterisk,
MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly);
}
}
return(idjournal);
}
public static DataTable GetViews(ConnectionData connectionData)
{
var dataTable = new DataTable();
var access = new DataAccess(connectionData);
var connectionString = access.CreateConnectionString();
try
{
using (var connection = new FbConnection(connectionString))
{
connection.Open();
//dataTable = connection.GetSchema("Views");
dataTable = QueryProcessing.ExecuteQuery(connectionData, SystemTables.GetViews("").ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(dataTable);
}
private void BindData()
{
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.DataSource = (string)Application.Current.Properties["dataSource"];
csb.Port = 3050;
csb.Database = (string)Application.Current.Properties["dataBase"];
csb.UserID = (string)Application.Current.Properties["userID"];
csb.Password = (string)Application.Current.Properties["password"];
csb.ServerType = FbServerType.Default;
DataSet dtSet = new DataSet();
using (connection = new FbConnection(csb.ToString()))
{
command = new FbCommand(sql, connection);
FbDataAdapter adapter = new FbDataAdapter();
connection.Open();
adapter.SelectCommand = command;
adapter.Fill(dtSet, "AUTORZY");
Author.DataContext = dtSet;
}
}
private void MusteriGuncelle2_Load(object sender, EventArgs e)
{
ToolTip Aciklama = new ToolTip();
Aciklama.SetToolTip(katekle, "Müşteri Kategorisi Ekle");
// Bağlantı //
FbConnection baglanti = new FbConnection(fk.Baglanti_Kodu());
// Bağlantı //
// Kategorileri çek //
FbDataReader KategoriGetir;
FbCommand KategoriSorgu = new FbCommand("SELECT * FROM Musteri_kategori", baglanti);
baglanti.Open();
Musteri_kategori.Items.Clear();
object Kssor = KategoriSorgu.ExecuteScalar();
if (Kssor != null)
{
KategoriGetir = KategoriSorgu.ExecuteReader();
while (KategoriGetir.Read())
{
Musteri_kategori.Items.Add(KategoriGetir["Kategori_adi"].ToString());
}
}
else
{
MessageBox.Show("Müşteri kategorisi ekleyiniz", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
MusteriKategoriEkle mke = new MusteriKategoriEkle();
this.Close();
mke.ShowDialog();
}
baglanti.Close();
// Kategorileri çek //
}
//Ingresa Clientes Grole a la Base de Datos
public ClienteGrole ClientesGroleInsertar(ClienteGrole AClienteGrole)
{
string pSentencia = "INSERT INTO DRASCLIENTES (NOMBRE) VALUES (@NOMBRE)";
FbConnection con = _Conexiones.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@NOMBRE", FbDbType.VarChar).Value = AClienteGrole.Nombre;
try
{
con.Open();
com.ExecuteNonQuery();
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return(ClienteGroleObtener(AClienteGrole.Nombre));
}
/// <summary>
/// список типов улиц
/// </summary>
/// <returns>список типов улиц</returns>
public List <string> GetStreetTypes()
{
List <string> streets = new List <string>();
string connectionString = cs.ToString();
FbConnection connection = new FbConnection(connectionString);
connection.Open();
string selectSQL;
selectSQL = string.Format("SELECT TYPE FROM TYPESTREET ORDER BY CODE");
DataSet myDataSet = new DataSet();
FbCommand sel_com = new FbCommand(selectSQL, connection);
FbDataAdapter myDataAdapter = new FbDataAdapter(sel_com);
myDataAdapter.Fill(myDataSet, "STREET");
connection.Close();
DataRowCollection dra = myDataSet.Tables["STREET"].Rows;
foreach (DataRow dr in dra)
{
streets.Add(dr[0].ToString());
}
return(streets);
}
public DetalleMateriaPrimaD ObtenerCajaMateriaPrima(string ACodigoBarras)
{
DetalleMateriaPrimaD pDetalleMateriaPrimaD = null;
string pSentencia = "SELECT * FROM DRASMATPRIMAD_DETALLE WHERE CODIGOBARRAS = @CODIGOBARRAS";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@CODIGOBARRAS", FbDbType.VarChar).Value = ACodigoBarras;
try
{
con.Open();
FbDataReader reader = com.ExecuteReader();
while (reader.Read())
{
pDetalleMateriaPrimaD = new DetalleMateriaPrimaD();
pDetalleMateriaPrimaD.Id = reader["ID"] != DBNull.Value ? (int)reader["ID"] : -1;
pDetalleMateriaPrimaD.Id_Maestro = reader["ID_MAESTRO"] != DBNull.Value ? (int)reader["ID_MAESTRO"] : -1;
pDetalleMateriaPrimaD.CodigoBarras = reader["CODIGOBARRAS"] != DBNull.Value ? (string)reader["CODIGOBARRAS"] : "";
pDetalleMateriaPrimaD.Peso = reader["PESO"] != DBNull.Value ? (decimal)reader["PESO"] : 0;
pDetalleMateriaPrimaD.Tipo = reader["TIPO"] != DBNull.Value ? (string)reader["TIPO"] : "";
pDetalleMateriaPrimaD.Producto = reader["PRODUCTO"] != DBNull.Value ? (string)reader["PRODUCTO"] : "";
pDetalleMateriaPrimaD.Id_Salida = reader["ID_SALIDA"] != DBNull.Value ? (int)reader["ID_SALIDA"] : -1;
pDetalleMateriaPrimaD.Id_Tarima = reader["ID_TARIMA"] != DBNull.Value ? (int)reader["ID_TARIMA"] : -1;
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return(pDetalleMateriaPrimaD);
}
//Modifica Clase
public ClienteGrole ClientesGroleModificar(ClienteGrole AClienteGrole)
{
string pSentencia = "UPDATE DRASCLIENTES SET NOMBRE=@NOMBRE WHERE ID=@ID";
FbConnection con = _Conexiones.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("ID", FbDbType.Integer).Value = AClienteGrole.Id;
com.Parameters.Add("NOMBRE", FbDbType.VarChar).Value = AClienteGrole.Nombre;
try
{
con.Open();
com.ExecuteNonQuery();
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return(ClienteGroleObtener(AClienteGrole.Nombre));
}
public void get()
{
this.Clear();
try
{
FbConnection fb = new FbConnection(connection.connectionString());
fb.Open();
FbTransaction fbt = fb.BeginTransaction(); //стартуем транзакцию; стартовать транзакцию можно только для открытой базы (т.е. мутод Open() уже был вызван ранее, иначе ошибка)
FbCommand SelectSQL = new FbCommand();
SelectSQL.CommandText = "select d.name,a.price,a.quanshop, a.usernameback from backshop a, goods b, name d where a.goodscode=b.goodscode and b.namecode=d.namecode and a.datatimeback>@datetime"; //задаем запрос на выборку
SelectSQL.Parameters.Add("@datetime", DateTime.Now.Date);
SelectSQL.Connection = fb;
SelectSQL.Transaction = fbt; //необходимо проинициализить транзакцию для объекта SelectSQL
FbDataReader reader = SelectSQL.ExecuteReader(); //для запросов, которые возвращают результат в виде набора данных надо использоваться метод ExecuteReader()
try
{
while (reader.Read()) //пока не прочли все данные выполняем...
{
var nch = new strokavozvrata(reader.GetString(0), reader.GetInt16(2), reader.GetDouble(1), reader.GetString(3));
this.Add(nch);
}
}
finally
{
//всегда необходимо вызывать метод Close(), когда чтение данных завершено
reader.Close();
fb.Close(); //закрываем соединение, т.к. оно нам больше не нужно
}
SelectSQL.Dispose();
}
catch (Exception e)
{
}
}
public void ConnectionPoolingLifetimeTest()
{
FbConnection.ClearAllPools();
FbConnectionStringBuilder csb = BuildConnectionStringBuilder(FbServerType, Compression);
csb.Pooling = true;
csb.ConnectionLifeTime = 5;
string cs = csb.ToString();
int active = GetActiveConnections();
using (FbConnection
myConnection1 = new FbConnection(cs),
myConnection2 = new FbConnection(cs))
{
myConnection1.Open();
myConnection2.Open();
Assert.AreEqual(active + 2, GetActiveConnections());
}
Thread.Sleep(TimeSpan.FromSeconds(csb.ConnectionLifeTime * 2));
Assert.AreEqual(active, GetActiveConnections());
}
private static bool IsInDatabase(Action <FbCommand> adjustCommand)
{
var result = false;
using (var connection = new FbConnection(IntegrationTestOptions.Firebird.ConnectionString))
{
connection.Open();
using (var tx = connection.BeginTransaction())
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
adjustCommand(cmd);
using (var reader = cmd.ExecuteReader())
{
result = reader.Read();
}
}
tx.Commit();
}
connection.Close();
}
return(result);
}
private void Guncelle_Click(object sender, EventArgs e)
{
try
{
FbConnection baglan = new FbConnection(fk.Baglanti_Kodu());
baglan.Open();
if (yeni_dizin.Text.Length > 1)
{
FbCommand Guncelle = new FbCommand("update Ayar set yedek_dizin='" + yeni_dizin.Text + "'", baglan);
Guncelle.ExecuteNonQuery();
yedek_dizin.Text = yeni_dizin.Text;
MessageBox.Show("Yedekleme dizininiz başarıyla güncellenmiştir.", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Lütfen yedek dizinini seçin.", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
baglan.Close();
}
catch (Exception e4)
{
MessageBox.Show(e4.Message, "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public override void Remove(Aluno objeto)
{
using (FbConnection conexaoFireBird = ConectarBD())
{
try
{
conexaoFireBird.Open();
var mSQL = $"DELETE from ALUNOS where MATRICULA={objeto.Matricula}";
var cmd = new FbCommand(mSQL, conexaoFireBird);
cmd.ExecuteNonQuery();
conexaoFireBird.Close();
}
catch (FbException fbex)
{
throw fbex;
}
finally
{
conexaoFireBird.Close();
}
}
}
public override void Update(Aluno objeto)
{
using (FbConnection conexaoFireBird = ConectarBD())
{
try
{
conexaoFireBird.Open();
string mSQL = $"UPDATE ALUNOS set NOME ='{objeto.Nome}', SEXO={(int)objeto.Sexo}, NASCIMENTO='{objeto.Nascimento.ToString("d", CultureInfo.CreateSpecificCulture("pt-BR"))}', CPF='{objeto.CPF}' WHERE MATRICULA={objeto.Matricula}";
FbCommand cmd = new FbCommand(mSQL, conexaoFireBird);
cmd.ExecuteNonQuery();
conexaoFireBird.Close();
}
catch (FbException fbex)
{
throw fbex;
}
finally
{
conexaoFireBird.Close();
}
}
}
public List <string> GetListRayon(string otg)
{
List <string> rayon = new List <string>();
string connectionString = cs.ToString();
FbConnection connection = new FbConnection(connectionString);
connection.Open();
string selectSQL;
selectSQL = string.Format("SELECT DISTINCT RAYON FROM OTGSETTLEMENTS WHERE OTG = {0} ORDER BY RAYON", otg);
DataSet myDataSet = new DataSet();
FbCommand sel_com = new FbCommand(selectSQL, connection);
FbDataAdapter myDataAdapter = new FbDataAdapter(sel_com);
myDataAdapter.Fill(myDataSet, "RAYON");
connection.Close();
DataRowCollection dra = myDataSet.Tables["RAYON"].Rows;
foreach (DataRow dr in dra)
{
rayon.Add(dr[0].ToString());
}
return(rayon);
}
public DataRowCollection GetNamePilga(string code)
{
if (code.Length == 0)
{
code = "0";
}
string connectionString = cs.ToString();
FbConnection connection = new FbConnection(connectionString);
connection.Open();
string selectSQL;
selectSQL = string.Format(@"SELECT CATNAME FROM CATEGPILG WHERE CODE = {0}", code);
DataSet myDataSet = new DataSet();
FbCommand sel_com = new FbCommand(selectSQL, connection);
FbDataAdapter myDataAdapter = new FbDataAdapter(sel_com);
myDataAdapter.Fill(myDataSet, "CATNAME");
connection.Close();
string strNum = string.Empty;
DataRowCollection dra = myDataSet.Tables["CATNAME"].Rows;
return(dra);
}
public DataRowCollection GetCodeHelpSelf(string ptk)
{
if (ptk.Length == 0)
{
ptk = "0";
}
string connectionString = cs.ToString();
FbConnection connection = new FbConnection(connectionString);
connection.Open();
string selectSQL;
selectSQL = string.Format(@"SELECT CODEPTK AS CODEHELP, NAMEPTK AS NAMEHELP FROM CODEPTK WHERE ID_PARENT = {0}", ptk);
DataSet myDataSet = new DataSet();
FbCommand sel_com = new FbCommand(selectSQL, connection);
FbDataAdapter myDataAdapter = new FbDataAdapter(sel_com);
myDataAdapter.Fill(myDataSet, "KINDHELP");
connection.Close();
string strNum = string.Empty;
DataRowCollection dra = myDataSet.Tables["KINDHELP"].Rows;
return(dra);
}
public DataRowCollection GetCodePtkById(string id)
{
if (id.Length == 0)
{
id = "0";
}
string connectionString = cs.ToString();
FbConnection connection = new FbConnection(connectionString);
connection.Open();
string selectSQL;
selectSQL = string.Format(@"SELECT CODEPTK FROM CODEPTK WHERE ID = {0}", id);
DataSet myDataSet = new DataSet();
FbCommand sel_com = new FbCommand(selectSQL, connection);
FbDataAdapter myDataAdapter = new FbDataAdapter(sel_com);
myDataAdapter.Fill(myDataSet, "PTK");
connection.Close();
string strNum = string.Empty;
DataRowCollection dra = myDataSet.Tables["PTK"].Rows;
return(dra);
}
private static void CreateProcedures(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
// SELECT_DATA
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE SELECT_DATA \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("INT_FIELD INTEGER, \r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100), \r\n");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2)) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("FOR SELECT INT_FIELD, VARCHAR_FIELD, DECIMAL_FIELD FROM TEST INTO :INT_FIELD, :VARCHAR_FIELD, :DECIMAL_FIELD \r\n");
commandText.Append("DO \r\n");
commandText.Append("SUSPEND; \r\n");
commandText.Append("end;");
FbCommand command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETRECORDCOUNT
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETRECORDCOUNT \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("RECCOUNT SMALLINT) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("for select count(*) from test into :reccount \r\n");
commandText.Append("do \r\n");
commandText.Append("suspend; \r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETVARCHARFIELD
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETVARCHARFIELD (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100))\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select varchar_field from test where int_field = :id into :varchar_field\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETASCIIBLOB
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETASCIIBLOB (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("ASCII_BLOB BLOB SUB_TYPE 1)\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select clob_field from test where int_field = :id into :ascii_blob\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// DATAREADERTEST
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE DATAREADERTEST\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("content VARCHAR(128))\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("content = 'test';\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
private static void InsertTestData(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)");
commandText.Append(" values(@int_field, @char_field, @varchar_field, @bigint_field, @smallint_field, @float_field, @double_field, @numeric_field, @date_field, @time_field, @timestamp_field, @clob_field, @blob_field)");
FbTransaction transaction = connection.BeginTransaction();
FbCommand command = new FbCommand(commandText.ToString(), connection, transaction);
try
{
// Add command parameters
command.Parameters.Add("@int_field" , FbDbType.Integer);
command.Parameters.Add("@char_field" , FbDbType.Char);
command.Parameters.Add("@varchar_field" , FbDbType.VarChar);
command.Parameters.Add("@bigint_field" , FbDbType.BigInt);
command.Parameters.Add("@smallint_field" , FbDbType.SmallInt);
command.Parameters.Add("@float_field" , FbDbType.Double);
command.Parameters.Add("@double_field" , FbDbType.Double);
command.Parameters.Add("@numeric_field" , FbDbType.Numeric);
command.Parameters.Add("@date_field" , FbDbType.Date);
command.Parameters.Add("@time_Field" , FbDbType.Time);
command.Parameters.Add("@timestamp_field" , FbDbType.TimeStamp);
command.Parameters.Add("@clob_field" , FbDbType.Text);
command.Parameters.Add("@blob_field" , FbDbType.Binary);
command.Prepare();
for (int i = 0; i < 100; i++)
{
command.Parameters["@int_field"].Value = i;
command.Parameters["@char_field"].Value = "IRow " + i.ToString();
command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@bigint_field"].Value = i;
command.Parameters["@smallint_field"].Value = i;
command.Parameters["@float_field"].Value = (float)(i + 10)/5;
command.Parameters["@double_field"].Value = Math.Log(i, 10);
command.Parameters["@numeric_field"].Value = (decimal)(i + 10)/5;
command.Parameters["@date_field"].Value = DateTime.Now;
command.Parameters["@time_field"].Value = DateTime.Now;
command.Parameters["@timestamp_field"].Value= DateTime.Now;
command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@blob_field"].Value = Encoding.Default.GetBytes("IRow Number " + i.ToString());
command.ExecuteNonQuery();
}
// Commit transaction
transaction.Commit();
}
catch (FbException)
{
transaction.Rollback();
throw;
}
finally
{
command.Dispose();
connection.Close();
}
}
private static void CreateTables(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
// Table for general purpouse tests
commandText.Append("CREATE TABLE TEST (");
commandText.Append("INT_FIELD INTEGER DEFAULT 0 NOT NULL PRIMARY KEY,");
commandText.Append("CHAR_FIELD CHAR(30),");
commandText.Append("VARCHAR_FIELD VARCHAR(100),");
commandText.Append("BIGINT_FIELD BIGINT,");
commandText.Append("SMALLINT_FIELD SMALLINT,");
commandText.Append("DOUBLE_FIELD DOUBLE PRECISION,");
commandText.Append("FLOAT_FIELD FLOAT,");
commandText.Append("NUMERIC_FIELD NUMERIC(15,2),");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2),");
commandText.Append("DATE_FIELD DATE,");
commandText.Append("TIME_FIELD TIME,");
commandText.Append("TIMESTAMP_FIELD TIMESTAMP,");
commandText.Append("CLOB_FIELD BLOB SUB_TYPE 1 SEGMENT SIZE 80,");
commandText.Append("BLOB_FIELD BLOB SUB_TYPE 0 SEGMENT SIZE 80,");
commandText.Append("IARRAY_FIELD INTEGER [0:3],");
commandText.Append("SARRAY_FIELD SMALLINT [0:4],");
commandText.Append("LARRAY_FIELD BIGINT [0:5],");
commandText.Append("FARRAY_FIELD FLOAT [0:3],");
commandText.Append("BARRAY_FIELD DOUBLE PRECISION [1:4],");
commandText.Append("NARRAY_FIELD NUMERIC(10,6) [1:4],");
commandText.Append("DARRAY_FIELD DATE [1:4],");
commandText.Append("TARRAY_FIELD TIME [1:4],");
commandText.Append("TSARRAY_FIELD TIMESTAMP [1:4],");
commandText.Append("CARRAY_FIELD CHAR(21) [1:4],");
commandText.Append("VARRAY_FIELD VARCHAR(30) [1:4],");
commandText.Append("BIG_ARRAY INTEGER [1:32767],");
commandText.Append("EXPR_FIELD COMPUTED BY (smallint_field * 1000),");
commandText.Append("CS_FIELD CHAR(1) CHARACTER SET UNICODE_FSS,");
commandText.Append("UCCHAR_ARRAY CHAR(10) [1:10] CHARACTER SET UNICODE_FSS);");
FbCommand command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
private static void CreateTriggers(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
// new_row
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER new_row FOR test ACTIVE\r\n");
commandText.Append("AFTER INSERT POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'new row';\r\n");
commandText.Append("END");
FbCommand command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// update_row
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER update_row FOR test ACTIVE\r\n");
commandText.Append("AFTER UPDATE POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'updated row';\r\n");
commandText.Append("END");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
