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