public virtual DataTable GetSchema(FbConnection connection, object[] restrictions) { restrictions = this.ParseRestrictions(restrictions); FbCommand command = this.BuildCommand(connection, restrictions); FbDataAdapter adapter = new FbDataAdapter(command); DataSet dataSet = new DataSet(this.schemaName); try { adapter.Fill(dataSet, this.schemaName); } catch (Exception ex) { throw new FbException(ex.Message); } finally { adapter.Dispose(); command.Dispose(); } TrimStringFields(dataSet.Tables[this.schemaName]); return this.ProcessResult(dataSet.Tables[this.schemaName]); }
protected FbCommand BuildCommand(FbConnection connection, object[] restrictions) { DataView collections = FbMetaDataCollections.GetSchema().DefaultView; collections.RowFilter = "CollectionName = '" + this.schemaName + "'"; if (collections.Count == 0) { throw new NotSupportedException("Unsupported collection name."); } if (restrictions != null && restrictions.Length > (int)collections[0]["NumberOfRestrictions"]) { throw new InvalidOperationException("The number of specified restrictions is not valid."); } DataView restriction = FbRestrictions.GetSchema().DefaultView; restriction.RowFilter = "CollectionName = '" + this.schemaName + "'"; if (restriction.Count != (int)collections[0]["NumberOfRestrictions"]) { throw new InvalidOperationException("Incorrect restriction definitions."); } StringBuilder builder = this.GetCommandText(restrictions); FbCommand schema = connection.CreateCommand(); schema.CommandText = builder.ToString(); if (connection.InnerConnection.HasActiveTransaction) { schema.Transaction = connection.InnerConnection.ActiveTransaction; } if (restrictions != null && restrictions.Length > 0) { // Add parameters int index = 0; for (int i = 0; i < restrictions.Length; i++) { string rname = restriction[i]["RestrictionDefault"].ToString().ToLower(CultureInfo.CurrentCulture); if (restrictions[i] != null && !rname.EndsWith("_catalog") && !rname.EndsWith("_schema") && rname != "table_type") { string pname = String.Format(CultureInfo.CurrentCulture, "@p{0}", index++); FbParameter p = schema.Parameters.Add(pname, restrictions[i].ToString()); p.FbDbType = FbDbType.VarChar; p.Size = 255; } } } return schema; }
public virtual void SetUp() { string cs = this.BuildConnectionString(); CreateDatabase(cs); CreateTables(cs); InsertTestData(cs); CreateProcedures(cs); CreateTriggers(cs); this.connection = new FbConnection(cs); this.connection.Open(); if (this.withTransaction) { this.transaction = this.connection.BeginTransaction(); } }
public static DataTable GetSchema( FbConnection connection, string collectionName, object[] restrictions) { FbDbSchema returnSchema = null; switch (collectionName.ToLower(CultureInfo.CurrentCulture)) { case "charactersets": returnSchema = new FbCharacterSets(); break; case "checkconstraints": returnSchema = new FbCheckConstraints(); break; case "checkconstraintsbytable": returnSchema = new FbChecksByTable(); break; case "collations": returnSchema = new FbCollations(); break; case "columns": returnSchema = new FbColumns(); break; case "columnprivileges": returnSchema = new FbColumnPrivileges(); break; case "datatypes": return FbDataTypes.GetSchema(); case "domains": returnSchema = new FbDomains(); break; case "foreignkeys": returnSchema = new FbForeignKeys(); break; case "functions": returnSchema = new FbFunctions(); break; case "generators": returnSchema = new FbGenerators(); break; case "indexes": returnSchema = new FbIndexes(); break; case "metadatacollections": return FbMetaDataCollections.GetSchema(); case "primarykeys": returnSchema = new FbPrimaryKeys(); break; case "procedures": returnSchema = new FbProcedures(); break; case "procedureparameters": returnSchema = new FbProcedureParameters(); break; case "procedureprivileges": returnSchema = new FbProcedurePrivilegesSchema(); break; case "restrictions": return FbRestrictions.GetSchema(); case "roles": returnSchema = new FbRoles(); break; case "tables": returnSchema = new FbTables(); break; case "tableconstraints": returnSchema = new FbTableConstraints(); break; case "tableprivileges": returnSchema = new FbTablePrivileges(); break; case "triggers": returnSchema = new FbTriggers(); break; case "uniquekeys": returnSchema = new FbUniqueKeys(); break; case "viewcolumnusage": returnSchema = new FbViewColumnUsage(); break; case "views": returnSchema = new FbViews(); break; case "viewprivileges": returnSchema = new FbViewPrivileges(); break; default: throw new NotSupportedException("The specified metadata collection is not supported."); } return returnSchema.GetSchema(connection, restrictions); }
private static DataTable PrepareCollection(FbConnection connection, string collectionName, string[] restrictions) { FbSchema returnSchema = null; switch (collectionName.ToLower(CultureInfo.InvariantCulture)) { case "charactersets": returnSchema = new FbCharacterSets(); break; case "checkconstraints": returnSchema = new FbCheckConstraints(); break; case "checkconstraintsbytable": returnSchema = new FbChecksByTable(); break; case "collations": returnSchema = new FbCollations(); break; case "columns": returnSchema = new FbColumns(); break; case "columnprivileges": returnSchema = new FbColumnPrivileges(); break; case "domains": returnSchema = new FbDomains(); break; case "foreignkeycolumns": returnSchema = new FbForeignKeyColumns(); break; case "foreignkeys": returnSchema = new FbForeignKeys(); break; case "functions": returnSchema = new FbFunctions(); break; case "generators": returnSchema = new FbGenerators(); break; case "indexcolumns": returnSchema = new FbIndexColumns(); break; case "indexes": returnSchema = new FbIndexes(); break; case "primarykeys": returnSchema = new FbPrimaryKeys(); break; case "procedures": returnSchema = new FbProcedures(); break; case "procedureparameters": returnSchema = new FbProcedureParameters(); break; case "procedureprivileges": returnSchema = new FbProcedurePrivilegesSchema(); break; case "roles": returnSchema = new FbRoles(); break; case "tables": returnSchema = new FbTables(); break; case "tableconstraints": returnSchema = new FbTableConstraints(); break; case "tableprivileges": returnSchema = new FbTablePrivileges(); break; case "triggers": returnSchema = new FbTriggers(); break; case "uniquekeys": returnSchema = new FbUniqueKeys(); break; case "viewcolumns": returnSchema = new FbViewColumns(); break; case "views": returnSchema = new FbViews(); break; case "viewprivileges": returnSchema = new FbViewPrivileges(); break; default: throw new NotSupportedException("The specified metadata collection is not supported."); } return(returnSchema.GetSchema(connection, collectionName, restrictions)); }
public int Delete(int IDUSUARIO) { int result = 0; try { //Verificando a existência de um transação aberta if (dbTransaction != null) { if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Del_USUARIO", dbCnn); dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) { dbCnn = ((FbConnection)GetConnectionDB()); } if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Del_USUARIO", dbCnn); dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.Parameters.AddWithValue("@IDUSUARIO", IDUSUARIO); //PrimaryKey //Executando consulta dbCommand.ExecuteNonQuery(); result = IDUSUARIO; if (dbTransaction == null) { dbCommand.Transaction.Commit(); dbCnn.Close(); } } catch (Exception ex) { if (dbTransaction != null) { this.RollbackTransaction(); } else { if (dbCommand.Transaction != null) { dbCommand.Transaction.Rollback(); } if (dbCnn.State == ConnectionState.Open) { dbCnn.Close(); } } throw ex; } return(result); }
public USUARIOCollection ReadCollectionByParameter(List <RowsFiltro> RowsFiltro, string FieldOrder) { FbDataReader dataReader = null; USUARIOCollection collection = null; string strSqlCommand = String.Empty; try { if (RowsFiltro != null) { if (RowsFiltro.Count > 0) { strSqlCommand = "SELECT * FROM USUARIO 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 += ") order by " + FieldOrder; } else { strSqlCommand = "SELECT * FROM USUARIO order by " + FieldOrder; } } else { strSqlCommand = "SELECT * FROM USUARIO order by " + FieldOrder; } //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 void loadlocal(bool isServer) { _strConn = isServer ? connConfig_Server() : connConfig_Client(); _conn = new FbConnection(_strConn); }
public LIS_COMPOSPRODUTOCollection ReadCollection() { FbDataReader dataReader = null; try { LIS_COMPOSPRODUTOCollection 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_COMPOSPRODUTO", 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_COMPOSPRODUTO", 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; } }
static void Main(string[] args) { streamForLog.AutoFlush = true; Console.SetError(streamForLog); try { byte[] fontdata = Properties.Resources.Verdana_Bold; IntPtr data = Marshal.AllocCoTaskMem(fontdata.Length); Marshal.Copy(fontdata, 0, data, fontdata.Length); pfc.AddMemoryFont(data, fontdata.Length); } catch (Exception e) { wrLine(e.Message); } int delayRepeat = Convert.ToInt32(ConfigurationManager.AppSettings.Get("delayRepeat")); printerForBracelet = ConfigurationManager.AppSettings.Get("printerForBracelet"); printerForLab = ConfigurationManager.AppSettings.Get("printerForLab"); docaAddr = ConfigurationManager.AppSettings.Get("docaAddr"); bool labEnable = false; bool braceletEnable = false; if (ConfigurationManager.AppSettings.Get("labEnable").Equals("1")) { labEnable = true; } if (ConfigurationManager.AppSettings.Get("braceletEnable").Equals("1")) { braceletEnable = true; } bool isAvailable = false; FbConnectionStringBuilder cs = new FbConnectionStringBuilder(); cs.DataSource = docaAddr; cs.Database = "/opt/firebird/database/docaplus.gdb"; cs.UserID = "SYSDBA"; cs.Password = "******"; cs.Charset = "UTF8"; cs.Pooling = false; FbConnection connection = new FbConnection(cs.ToString()); int checkCount = 0; restartApp: if (!isAvailable) { isAvailable = availableCheck(); checkCount++; if (checkCount > 3) { Thread.Sleep(1000); } } if (isAvailable) { zapros: wrLine("barcode:"); barCode = Console.ReadLine(); wrLine(barCode); if (barCode.Length == 20) { checkCount = 0; anotherTry: isAvailable = availableCheck(); string idPat = barCode.Substring(2, 9); idHsp = barCode.Substring(11, 9); if (isAvailable) { try //connect to db { string query = $@"select pat_list.fam, pat_list.nam, pat_list.ots, depart.name, depart.phone, hosp.nom, dep_hsp.date_in, pat_list.id_pat, pat_list.d_bir, pat_list.sex from dep_hsp inner join pat_list on(dep_hsp.id_pat = pat_list.id_pat) inner join depart on(dep_hsp.id_dep = depart.id) inner join hosp on(dep_hsp.id_pat = hosp.id_pat) and (dep_hsp.id_hsp = hosp.id_hsp) where ( (dep_hsp.id_hsp = {idHsp}) )"; connection.Open(); wrLine(connection.State.ToString()); FbCommand fbCommand = new FbCommand(query, connection); FbDataAdapter fbDataAdapter = new FbDataAdapter(fbCommand); DataTable dataTable = new DataTable(); fbDataAdapter.Fill(dataTable); connection.Close(); wrLine(connection.State.ToString()); foreach (DataRow dataRow in dataTable.Rows) { foreach (DataColumn column in dataTable.Columns) { Console.WriteLine(column.ColumnName + " " + dataRow[column]); } } if (dataTable.Rows.Count > 0) { strFam = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[0].ToString()); strNam = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[1].ToString()); strOts = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[2].ToString()); strDepart = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[3].ToString()); strPhone = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[4].ToString()); strIb = spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[5].ToString()); DateTime dateIn = dateTime.AddSeconds(Convert.ToInt32(spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[6].ToString()))); DateTime dBir = dateTime.AddSeconds(Convert.ToInt32(spaceKiller(dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[8].ToString())) + 3600); strSex = dataTable.Rows[dataTable.Rows.Count - 1].ItemArray[9].ToString(); strDateIn = dateIn.ToString("dd.MM.yyyy") + " " + dateIn.ToString("HH:mm"); double age = Math.Truncate(DateTime.Now.Subtract(dBir).TotalDays / 365.25); string ageSuffix = "лет"; if (!(age < 21 && age > 4)) { if (age % 10 == 1) { ageSuffix = "год"; } else if (age % 10 > 1 && age % 10 < 5) { ageSuffix = "года"; } } strDbir = $"{dBir.ToString("dd.MM.yyyy")} ({age} {ageSuffix})"; } else { wrLine($"udefined pacient"); goto zapros; } } catch (Exception e) { wrLine(e.Message); connection.Close(); } wrLine("start parse"); strFio = $"{strFam}{strNam.Substring(0, 1)}.{strOts.Substring(0, 1)}."; wrLine(strFio); if (strFio.Length > 3) { if (DateTime.Compare(DateTime.Now, dateTimeLastBar.AddSeconds(delayRepeat)) > 0 || lastBarCode != barCode) { lastBarCode = barCode; dateTimeLastBar = DateTime.Now; //опредление на неопознанного пациента if (strFio.ToLower().IndexOf("неизвестный") >= 0) { strFirst = $"Неизвестный, поступил: {strDateIn} ({strSex})"; strFio = $"Неизвестный, {strDateIn} ({strSex})"; } else { strFirst = $"{strFio} {strDbir}"; } wrLine($"idPat: {idPat} |idHsp: {idHsp} | {strFirst}, {strIb} {strDepart}, tel: {strPhone}"); wrLine("end parse"); wrLine("start generating & printing"); //generating and printing int printOrPreview = Convert.ToInt32(ConfigurationManager.AppSettings.Get("printOrPreview")); PrintDocument printBracelet = new PrintDocument(); printBracelet.DefaultPageSettings.Landscape = true; printBracelet.DefaultPageSettings.PaperSize = new PaperSize("new size", 95, 700); printBracelet.PrinterSettings.PrinterName = printerForBracelet; printBracelet.DefaultPageSettings.PrinterResolution.Kind = PrinterResolutionKind.High; printBracelet.PrintPage += PrintPageBracelet; if (braceletEnable) { if (printOrPreview == 2) { PrintPreviewDialog ppvw = new PrintPreviewDialog(); ppvw.Document = printBracelet; ppvw.ShowDialog(); } if (printOrPreview == 1) { printBracelet.Print(); } } else { wrLine("app.config disabled print bracelet"); } PrintDocument printLab = new PrintDocument(); printLab.PrinterSettings.PrinterName = printerForLab; printLab.PrintPage += PrintPageLab; if (labEnable) { if (printOrPreview == 2) { PrintPreviewDialog ppvw = new PrintPreviewDialog(); ppvw.Document = printLab; ppvw.ShowDialog(); } if (printOrPreview == 1) { printLab.Print(); } } else { wrLine("app.config disabled print lab list"); } wrLine("end generating & printing"); strFirst = null; strIb = null; strDepart = null; idHsp = null; strFio = null; strPhone = null; strDateIn = null; } else { wrLine($"this barCode:\"{barCode}\" repeated {DateTime.Now.Subtract(dateTimeLastBar).ToString()} before"); } goto zapros; } } else { isAvailable = false; wrLine("connection check..."); checkCount++; if (checkCount < 5) { Thread.Sleep(500); goto anotherTry; } } } else { goto zapros; } if (barCode != "exit") { goto zapros; } } else { goto restartApp; } Console.ReadLine(); connection.Close(); Console.WriteLine(connection.State); Console.Read(); }
internal ConnectionFireBird(string connectionString) { Conn = new FbConnection(connectionString); }
public int Save(int?IDCAMPOSRELATPERSONAZ, int IDCAMPO, int TAMANHO, int ORDEM, string SOMATORIO, int IDRELATORIOPERSONALIZADO) { int result = 0; try { //Verificando a existência de um transação aberta if (dbTransaction != null) { if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_CamposRelatPers", dbCnn); dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) { dbCnn = ((FbConnection)GetConnectionDB()); } if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_CamposRelatPers", dbCnn); dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } dbCommand.CommandType = CommandType.StoredProcedure; if (IDCAMPOSRELATPERSONAZ != null) { dbCommand.Parameters.AddWithValue("@IDCAMPOSRELATPERSONAZ", IDCAMPOSRELATPERSONAZ); //PrimaryKey } else { dbCommand.Parameters.AddWithValue("@IDCAMPOSRELATPERSONAZ", DBNull.Value); //PrimaryKey } dbCommand.Parameters.AddWithValue("@IDCAMPO", IDCAMPO); //Coluna dbCommand.Parameters.AddWithValue("@TAMANHO", TAMANHO); //Coluna dbCommand.Parameters.AddWithValue("@ORDEM", ORDEM); //Coluna dbCommand.Parameters.AddWithValue("@SOMATORIO", SOMATORIO); //Coluna dbCommand.Parameters.AddWithValue("@IDRELATORIOPERSONALIZADO", IDRELATORIOPERSONALIZADO); //Coluna //Retorno da Procedure FbParameter returnValue; returnValue = dbCommand.CreateParameter(); dbCommand.Parameters["@IDCAMPOSRELATPERSONAZ"].Direction = ParameterDirection.ReturnValue; //Executando consulta dbCommand.ExecuteNonQuery(); result = (int)(dbCommand.Parameters["@IDCAMPOSRELATPERSONAZ"].Value); if (dbTransaction == null) { dbCommand.Transaction.Commit(); dbCnn.Close(); } } catch (Exception ex) { if (dbTransaction != null) { this.RollbackTransaction(); } else { if (dbCommand.Transaction != null) { dbCommand.Transaction.Rollback(); } if (dbCnn.State == ConnectionState.Open) { dbCnn.Close(); } } throw ex; } return(result); }
/// <summary> /// Constructor to query a database using an existing <see cref="FbConnection"/> to initialize the <paramref name="connection"/> /// </summary> /// <param name="connection">An instance of <see cref="FbConnection"/> to use to query a database </param> public FirebirdClient(FbConnection connection) : base(connection) { }
public int Save(int?IDGRUPOAGENDATLMK, string NOME, string OBSERVACAO) { int result = 0; try { //Verificando a existência de um transação aberta if (dbTransaction != null) { if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_GRUPOAGENDATLMK", dbCnn); dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) { dbCnn = ((FbConnection)GetConnectionDB()); } if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_GRUPOAGENDATLMK", dbCnn); dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } dbCommand.CommandType = CommandType.StoredProcedure; //PrimaryKey com valor igual a null, indica um novo registro, //o valor da chave será fornecido pelo banco. Qualquer outro valor indicará edição do registro. if (IDGRUPOAGENDATLMK == -1) { dbCommand.Parameters.AddWithValue("@IDGRUPOAGENDATLMK", DBNull.Value); } else { dbCommand.Parameters.AddWithValue("@IDGRUPOAGENDATLMK", IDGRUPOAGENDATLMK); } dbCommand.Parameters.AddWithValue("@NOME", NOME); //Coluna dbCommand.Parameters.AddWithValue("@OBSERVACAO", OBSERVACAO); //Coluna //Retorno da Procedure FbParameter returnValue; returnValue = dbCommand.CreateParameter(); dbCommand.Parameters["@IDGRUPOAGENDATLMK"].Direction = ParameterDirection.InputOutput; //Executando consulta dbCommand.ExecuteNonQuery(); result = int.Parse(dbCommand.Parameters["@IDGRUPOAGENDATLMK"].Value.ToString()); if (dbTransaction == null) { dbCommand.Transaction.Commit(); dbCnn.Close(); } } catch (Exception ex) { if (dbTransaction != null) { this.RollbackTransaction(); } else { if (dbCommand.Transaction != null) { dbCommand.Transaction.Rollback(); } if (dbCnn.State == ConnectionState.Open) { dbCnn.Close(); } } throw ex; } return(result); }
private void butApply_Click(object sender, System.EventArgs e) { string Type_OP = ""; if (r_AgToWh.Checked) { Type_OP = "R"; } if (r_WhToAg.Checked) { Type_OP = "A"; } var From = ListFrom.SelectedItem; var To = ListTo.SelectedItem; var Item = ItemList.SelectedItem; if ((Type_OP != "") && (From != null) && (To != null) && (Item != null) && (((checkDelivery.Checked) && (num_auto != "")) || (!checkDelivery.Checked))) { int Amount = System.Convert.ToInt32(AmountField.Text); int Prise = System.Convert.ToInt32(PriseField.Text); System.TimeSpan Del_time; if (TimeList.SelectedItem == null) { Del_time = new System.TimeSpan(0, 0, 0); } else { Del_time = System.TimeSpan.Parse(TimeList.SelectedItem.ToString()); } System.DateTime Del_Date = DateTime.Value.Date; FbParameter GOODS = new FbParameter("GOODS", FbDbType.Char); FbParameter AMOUNT = new FbParameter("AMOUNT", FbDbType.Integer); FbParameter AGENT_NAME = new FbParameter("AGENT_NAME", FbDbType.Char); FbParameter WH_NAME = new FbParameter("WH_NAME", FbDbType.Char); FbParameter TYPE_OP = new FbParameter("TYPE_OP", FbDbType.Char); FbParameter PRICE = new FbParameter("PRICE", FbDbType.Integer); FbParameter DEL_TIME = new FbParameter("DEL_TIME", FbDbType.Time); FbParameter DEL_DATE = new FbParameter("DEL_DATE", FbDbType.Date); FbParameter DELIV = new FbParameter("DELIV", FbDbType.Char); GOODS.Value = Item; AMOUNT.Value = Amount; if (r_AgToWh.Checked) { AGENT_NAME.Value = From.ToString(); WH_NAME.Value = To.ToString(); } if (r_WhToAg.Checked) { AGENT_NAME.Value = To.ToString(); WH_NAME.Value = From.ToString(); } PRICE.Value = Prise; DEL_TIME.Value = Del_time; DEL_DATE.Value = Del_Date; if (checkDelivery.Checked) { DELIV.Value = 'Y'; } else { DELIV.Value = 'N'; } TYPE_OP.Value = Type_OP[0]; Connection = new FbConnection(dbContext.Database.Connection.ConnectionString); Connection.Open(); dbContext.Database.ExecuteSqlCommand("EXECUTE PROCEDURE ADD_ORDER(@GOODS, @AMOUNT, @AGENT_NAME, @WH_NAME, @TYPE_OP, @PRICE, @DEL_TIME, @DEL_DATE, @DELIV)", GOODS, AMOUNT, AGENT_NAME, WH_NAME, TYPE_OP, PRICE, DEL_TIME, DEL_DATE, DELIV); string tmp = num_auto; metroTile2_Click(null, null); ResultLabel.Visible = true; ResultLabel.ForeColor = Color.Green; ResultLabel.Text = "Your order has been successfully submitted. "; if (tmp != "") { ResultLabel.Text += ("Vehicle №" + tmp); } } else { ResultLabel.Visible = true; ResultLabel.ForeColor = Color.Red; ResultLabel.Text = "Your order has not been submitted. Correct fields and try again."; } }
private void logar() { FbConnection fbConn = new FbConnection(frmHome.strConn); string query = "SELECT ID, ID_FAZENDA, USUARIO, SENHA, PERMISSAO FROM USUARIO WHERE ((USUARIO = @USUARIO) AND (SENHA = @SENHA))"; FbCommand fbCmd = new FbCommand(query, fbConn); try { //PARAMETROS FbParameter[] prmParametro = new FbParameter[3]; prmParametro[0] = new FbParameter("@ID_FAZENDA", Convert.ToInt32(cbListaFazendasCadastradas.SelectedValue)); prmParametro[1] = new FbParameter("@USUARIO", txtUsuario.Text); prmParametro[2] = new FbParameter("@SENHA", txtSenha.Text); foreach (FbParameter p in prmParametro) { fbCmd.Parameters.Add(p); } if (temUsuarios(Convert.ToInt32(cbListaFazendasCadastradas.SelectedValue))) { fbConn.Open(); FbDataReader r = fbCmd.ExecuteReader(); if (r.Read()) { frmHome.admin = (r[4].ToString() == "1"); logado = true; frmHome.logado = true; frmHome.IDFazendaSelecionada = Convert.ToInt32(cbListaFazendasCadastradas.SelectedValue); frmHome.NomeFazendaSelecionada = cbListaFazendasCadastradas.Text; frmHome.usuarioSelecionado = txtUsuario.Text; Close(); MessageBox.Show("Você está trabalhando na fazenda: " + frmHome.NomeFazendaSelecionada); } else { MessageBox.Show(@"Usuário e/ou Senha inválida"); } } else { if (MessageBox.Show("Não há usuários cadastrados.\nDeseja cadastrar agora?", "Informação", MessageBoxButtons.YesNo) == DialogResult.Yes) { frmHome.NomeFazendaSelecionada = cbListaFazendasCadastradas.SelectedText; frmHome.IDFazendaSelecionada = Convert.ToInt32(cbListaFazendasCadastradas.SelectedValue); frmCadastroUsuarios frm = new frmCadastroUsuarios(); frm.ShowDialog(); } } } catch (FbException fbex) { MessageBox.Show("Erro ao acessar o Banco de Dados: " + fbex.Message, "Erro"); } finally { fbConn.Close(); } txtSenha.Clear(); txtUsuario.Clear(); txtUsuario.Focus(); }
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(); } }
public int Save(int?IDIMPRGRID, string NOMETELA, string NOMEGRID, string CAMPOSSELECIONADOS, string FLAGAJUSTA, string FLAGMODOPAISAGEM, string FLAGEXIBIRDATA, int IDFUNCIONARIO) { int result = 0; try { //Verificando a existência de um transação aberta if (dbTransaction != null) { if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_IMPRGRID", dbCnn); dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) { dbCnn = ((FbConnection)GetConnectionDB()); } if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_IMPRGRID", dbCnn); dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } dbCommand.CommandType = CommandType.StoredProcedure; if (IDIMPRGRID != -1) { dbCommand.Parameters.AddWithValue("@IDIMPRGRID", IDIMPRGRID); //PrimaryKey } else { dbCommand.Parameters.AddWithValue("@IDIMPRGRID", DBNull.Value); //PrimaryKey } dbCommand.Parameters.AddWithValue("@NOMETELA", NOMETELA); //Coluna dbCommand.Parameters.AddWithValue("@NOMEGRID", NOMEGRID); //Coluna dbCommand.Parameters.AddWithValue("@CAMPOSSELECIONADOS", CAMPOSSELECIONADOS); //Coluna dbCommand.Parameters.AddWithValue("@FLAGAJUSTA", FLAGAJUSTA); //Coluna dbCommand.Parameters.AddWithValue("@FLAGMODOPAISAGEM", FLAGMODOPAISAGEM); //Coluna dbCommand.Parameters.AddWithValue("@FLAGEXIBIRDATA", FLAGEXIBIRDATA); //Coluna dbCommand.Parameters.AddWithValue("@IDFUNCIONARIO", IDFUNCIONARIO); //Coluna //Retorno da Procedure FbParameter returnValue; returnValue = dbCommand.CreateParameter(); dbCommand.Parameters["@IDIMPRGRID"].Direction = ParameterDirection.InputOutput; //Executando consulta dbCommand.ExecuteNonQuery(); result = int.Parse(dbCommand.Parameters["@IDIMPRGRID"].Value.ToString()); if (dbTransaction == null) { dbCommand.Transaction.Commit(); dbCnn.Close(); } } catch (Exception ex) { if (dbTransaction != null) { this.RollbackTransaction(); } else { if (dbCommand.Transaction != null) { dbCommand.Transaction.Rollback(); } if (dbCnn.State == ConnectionState.Open) { dbCnn.Close(); } } throw ex; } return(result); }
public FbConnection GetConnectionDB() { FbConnection cnx = new FbConnection(); cnx.ConnectionString = connectionString; return cnx; }
private void SilEvet_Click_1(object sender, EventArgs e) { try { byte sonuc; FbConnection baglan = new FbConnection(fk.Baglanti_Kodu()); FbConnection baglan2 = new FbConnection(fk.Baglanti_Kodu()); baglan.Open(); FbCommand UrunKategori = new FbCommand("SELECT Urun_id FROM Urunler WHERE Urun_kategori='" + id + "'", baglan); object Usor = UrunKategori.ExecuteScalar(); if (Usor != null) { FbDataReader UrunKategoriId = UrunKategori.ExecuteReader(); string[] urun_id = new string[0]; int i = 0; while (UrunKategoriId.Read()) { Array.Resize(ref urun_id, urun_id.Length + 1); urun_id[i] = UrunKategoriId["Urun_id"].ToString(); i++; } foreach (string uid in urun_id) { baglan2.Open(); FbCommand SatisTab = new FbCommand("SELECT Satis_id FROM Satis WHERE Satis_urun='" + uid + "'", baglan2); FbDataReader SatisIdOku = SatisTab.ExecuteReader(); while (SatisIdOku.Read()) { fk.Sil("Satis", "Satis_id='" + SatisIdOku["Satis_id"].ToString() + "'"); } baglan2.Close(); baglan2.Open(); FbCommand RaporTab = new FbCommand("SELECT rapor_id FROM Rapor WHERE rapor_satisId='" + uid + "'", baglan2); FbDataReader RaporIdOku = RaporTab.ExecuteReader(); while (RaporIdOku.Read()) { fk.Sil("Rapor", "rapor_id='" + RaporIdOku["rapor_id"].ToString() + "'"); } baglan2.Close(); fk.Sil("Urunler", "Urun_id='" + uid + "'"); } sonuc = fk.Sil("Urun_kategori", "Kategori_id='" + id + "'"); if (sonuc == 1) { MessageBox.Show("Ürün kategorisi başarıyla silinmiştir", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } else { MessageBox.Show("Ürün kategorisi silinemedi ( Hata kodu: K-07 )", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("Sistemsel bir hata oluştu, lütfen destek sağlayıcınıza başvurunuz", "Bilgilendirme", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } baglan.Close(); } catch (Exception e1) { MessageBox.Show(e1.Message, "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public int Save(int?IDCOTACAOCOMPRA, string NUMREFERENCIA, int IDFORNECEDOR, DateTime DATAEMISSAO, string FLAGCOTACAO, int IDSTATUS, string OBSERVACAO, decimal TOTALCOTACAO) { int result = 0; try { //Verificando a existência de um transação aberta if (dbTransaction != null) { if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_COTACAOCOMPRA", dbCnn); dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) { dbCnn = ((FbConnection)GetConnectionDB()); } if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_COTACAOCOMPRA", dbCnn); dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } dbCommand.CommandType = CommandType.StoredProcedure; //PrimaryKey com valor igual a null, indica um novo registro, //o valor da chave será fornecido pelo banco. Qualquer outro valor indicará edição do registro. if (IDCOTACAOCOMPRA == -1) { dbCommand.Parameters.AddWithValue("@IDCOTACAOCOMPRA", DBNull.Value); } else { dbCommand.Parameters.AddWithValue("@IDCOTACAOCOMPRA", IDCOTACAOCOMPRA); } dbCommand.Parameters.AddWithValue("@NUMREFERENCIA", NUMREFERENCIA); //Coluna if (IDFORNECEDOR != null) { dbCommand.Parameters.AddWithValue("@IDFORNECEDOR", IDFORNECEDOR); //ForeignKey } else { dbCommand.Parameters.AddWithValue("@IDFORNECEDOR", DBNull.Value); //ForeignKey 5 } dbCommand.Parameters.AddWithValue("@DATAEMISSAO", DATAEMISSAO); //Coluna dbCommand.Parameters.AddWithValue("@FLAGCOTACAO", FLAGCOTACAO); //Coluna if (IDSTATUS != null) { dbCommand.Parameters.AddWithValue("@IDSTATUS", IDSTATUS); //ForeignKey } else { dbCommand.Parameters.AddWithValue("@IDSTATUS", DBNull.Value); //ForeignKey 5 } dbCommand.Parameters.AddWithValue("@OBSERVACAO", OBSERVACAO); //Coluna dbCommand.Parameters.AddWithValue("@TOTALCOTACAO", TOTALCOTACAO); //Coluna //Retorno da Procedure FbParameter returnValue; returnValue = dbCommand.CreateParameter(); dbCommand.Parameters["@IDCOTACAOCOMPRA"].Direction = ParameterDirection.InputOutput; //Executando consulta dbCommand.ExecuteNonQuery(); result = int.Parse(dbCommand.Parameters["@IDCOTACAOCOMPRA"].Value.ToString()); if (dbTransaction == null) { dbCommand.Transaction.Commit(); dbCnn.Close(); } } catch (Exception ex) { if (dbTransaction != null) { this.RollbackTransaction(); } else { if (dbCommand.Transaction != null) { dbCommand.Transaction.Rollback(); } if (dbCnn.State == ConnectionState.Open) { dbCnn.Close(); } } throw ex; } return(result); }
public void CreateDataBase() { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Файл fdb|*.FDB"; if (saveFileDialog.ShowDialog() == true && saveFileDialog.FileName != null) { XMLConfig.SaveXMLConfig(dataBase.Value, loggin.Value, password.Value, "*****@*****.**", "testpassword", System.IO.Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName) + "XMLConfig.xml"); dataBase.Value = saveFileDialog.FileName; connectionInfo.Login = Loggin; connectionInfo.Password = Password; connectionInfo.DB = DataBase; string conStr = new FbConnectionStringBuilder { Database = connectionInfo.DB, Password = connectionInfo.Password, UserID = connectionInfo.Login, Dialect = 3, }.ToString(); int pageSize = 4096; bool forcedWrites = true; bool overwrite = false; if (!File.Exists(connectionInfo.DB)) { FbConnection.CreateDatabase(conStr, pageSize, forcedWrites, overwrite); using (FbConnection con = new FbConnection(conStr)) { try { con.Open(); if (con.State == System.Data.ConnectionState.Open) { using (FbTransaction dbtran = con.BeginTransaction()) { using (FbCommand createTables = new FbCommand()) { createTables.CommandText = "EXECUTE BLOCK AS BEGIN" + " EXECUTE STATEMENT 'Create table Faculty (id_faculty integer, name_of_faculty varchar(35), primary key(id_faculty))';" + " EXECUTE STATEMENT 'CREATE GENERATOR faculty_id';" + " EXECUTE STATEMENT 'set GENERATOR faculty_id to 0';" + " EXECUTE STATEMENT 'Create trigger incfaculty_id for Faculty active before insert position 0 as begin if (new.id_faculty is null) then new.id_faculty = gen_id(faculty_id, 1); end';" + " EXECUTE STATEMENT 'Create table Departments(id_department integer, name_of_department varchar(50), id_faculty integer, primary key(id_department), foreign key(id_faculty) references faculty(id_faculty) ON DELETE CASCADE)';" + " EXECUTE STATEMENT 'CREATE GENERATOR department_id';" + " EXECUTE STATEMENT 'set GENERATOR department_id to 0';" + " EXECUTE STATEMENT 'Create trigger incdepartment_id for Departments active before insert position 0 as begin if (new.id_department is null) then new.id_department = gen_id(department_id, 1); end';" + " EXECUTE STATEMENT 'Create table Groups(id_group integer, name_of_group varchar(50), term integer, id_department integer, primary key(id_group), foreign key(id_department) references Departments(id_department) ON DELETE CASCADE)';" + " EXECUTE STATEMENT 'CREATE GENERATOR group_id';" + " EXECUTE STATEMENT 'set GENERATOR group_id to 0';" + " EXECUTE STATEMENT 'Create trigger incgroup_id for Groups active before insert position 0 as begin if (new.id_group is null) then new.id_group = gen_id(group_id, 1); end';" + " EXECUTE STATEMENT 'Create table Subjects(id_subject integer, name_of_subject varchar(200), id_department integer, primary key(id_subject), foreign key(id_department) references Departments(id_department) ON DELETE CASCADE)';" + " EXECUTE STATEMENT 'CREATE GENERATOR subject_id';" + " EXECUTE STATEMENT 'set GENERATOR subject_id to 0';" + " EXECUTE STATEMENT 'Create trigger incsubject_id for Subjects active before insert position 0 as begin if (new.id_subject is null) then new.id_subject = gen_id(subject_id, 1); end';" + " EXECUTE STATEMENT 'Create table Teachers(id_teacher integer, fio varchar(50), mail varchar(50), isreadlecture integer, primary key(id_teacher))';" + " EXECUTE STATEMENT 'CREATE GENERATOR teacher_id';" + " EXECUTE STATEMENT 'set GENERATOR teacher_id to 0';" + " EXECUTE STATEMENT 'Create trigger incteacher_id for Teachers active before insert position 0 as begin if (new.id_teacher is null) then new.id_teacher = gen_id(teacher_id, 1); end';" + " EXECUTE STATEMENT 'Create table Classrooms(id_classroom integer, number_of_classroom varchar(10), id_department integer, specific varchar(20), primary key(id_classroom), foreign key(id_department) references Departments(id_department) ON DELETE CASCADE)';" + " EXECUTE STATEMENT 'CREATE GENERATOR classroom_id';" + " EXECUTE STATEMENT 'set GENERATOR classroom_id to 0';" + " EXECUTE STATEMENT 'Create trigger incclassroom_id for Classrooms active before insert position 0 as begin if (new.id_classroom is null) then new.id_classroom = gen_id(classroom_id, 1); end';" + " EXECUTE STATEMENT 'Create table Classes(nameofschedule varchar(10000), id_teacher integer, id_departmentsteacher integer, id_subject integer, id_classroom integer, id_group integer, specifics varchar(10), daytime varchar(20), pair integer, numerator_denominator integer, keyy varchar(35), typekey varchar(100), foreign key(id_teacher) references Teachers(id_teacher) ON DELETE CASCADE, foreign key(id_group) references Groups(id_group) ON DELETE CASCADE, foreign key(id_classroom) references Classrooms(id_classroom) ON DELETE CASCADE, foreign key(id_subject) references Subjects(id_subject) ON DELETE CASCADE, foreign key(id_departmentsteacher)references departments(id_department))';" + " EXECUTE STATEMENT 'Create table TeachersAndDepartments(id_teacher integer, id_department integer, post varchar(30), primary key(id_teacher, id_department), foreign key(id_teacher) references Teachers(id_teacher) ON DELETE CASCADE, foreign key(id_department) references Departments(id_department) ON DELETE CASCADE)';" + " EXECUTE STATEMENT 'Create table GroupsAndSubjects(id_group integer, id_subject integer, lecturehour integer, exercisehour integer, labhour integer, primary key(id_group,id_subject), foreign key(id_group) references Groups(id_group) ON DELETE CASCADE, foreign key(id_subject) references Subjects(id_subject) ON DELETE CASCADE)';" + " EXECUTE STATEMENT 'Create table TeachersAndSubjects(id_teacher integer, id_department integer,id_subject integer, daylist varchar(150), primary key(id_teacher,id_department,id_subject), foreign key(id_teacher) references Teachers(id_teacher) ON DELETE CASCADE, foreign key(id_department) references Departments(id_department) ON DELETE CASCADE, foreign key(id_subject) references Subjects(id_subject) ON DELETE CASCADE )';" + " END"; createTables.Connection = con; createTables.Transaction = dbtran; int result = createTables.ExecuteNonQuery(); Console.WriteLine(result); } dbtran.Commit(); } } } catch (Exception e) { MessageBox.Show(e.Message, "Ошибка создания базы данных"); } } } } }
public string RefreshIndicesAndGetTablename() { string cmd_index = IndexSQLStatementsClass.Instance().GetIndiciesByName(_dbReg.Version, IndexName.Trim()); DataFilled = false; txtIndexName.Text = IndexName.Trim(); string TableName = string.Empty; try { lvFields.Items.Clear(); var con = new FbConnection(ConnectionStrings.Instance().MakeConnectionString(_dbReg)); con.Open(); string pkColumn = string.Empty; string indexName = string.Empty; string indexColumnName = string.Empty; string oldIndexColumnName = string.Empty; int Unique = 0; int IndexType = -1; bool Active = false; FbCommand fcmd = new FbCommand(cmd_index, con); var dread = fcmd.ExecuteReader(); lvFields.Items.Clear(); if (dread.HasRows) { while (dread.Read()) { TableName = dread.GetValue(0).ToString().Trim(); indexColumnName = dread.GetValue(2).ToString().Trim(); Unique = StaticFunctionsClass.ToIntDef(dread.GetValue(3).ToString().Trim(), 0); Active = StaticFunctionsClass.ToIntDef(dread.GetValue(4).ToString().Trim(), 0) != 1; IndexType = StaticFunctionsClass.ToIntDef(dread.GetValue(5).ToString().Trim(), 0); cbUnique.Checked = Unique > 0; ckActive.Checked = Active; if (IndexType < 0) { rbSortNothing.Checked = true; } else if (IndexType == 0) { rbSortAscending.Checked = true; } else if (IndexType == 1) { rbSortDescending.Checked = true; } string[] lv = new string[1]; lv[0] = indexColumnName; if (this.oldIndexColumnName != indexColumnName) { ListViewItem lvi = new ListViewItem(lv); lvFields.Items.Add(lvi); this.oldIndexColumnName = indexColumnName; } } DataFilled = true; } con.Close(); } catch (Exception ex) { _localNotify?.AddToERROR($@"{StaticFunctionsClass.DateTimeNowStr()}->IndexForm->RefreshIndicesAndGetTablename()->{ex.Message}"); } return(TableName); }
/// <summary> /// Default constuctor. /// The connection is created and opened in this constuctor using the connection string returned by GetConnectionString(). /// </summary> public DatabaseManager() { connection = new FbConnection(GetConnectionString()); connection.Open(); }
public int Save(int?IDUSUARIO, int IDFUNCIONARIO, int IDNIVELUSUARIO, string FLAGATIVO, string NOMEUSUARIO, string SENHAUSUARIO, string OBSERVACAO) { int result = 0; try { //Verificando a existência de um transação aberta if (dbTransaction != null) { if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_USUARIO", dbCnn); dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) { dbCnn = ((FbConnection)GetConnectionDB()); } if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_USUARIO", dbCnn); dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } dbCommand.CommandType = CommandType.StoredProcedure; if (IDUSUARIO != null) { dbCommand.Parameters.AddWithValue("@IDUSUARIO", IDUSUARIO); //PrimaryKey } else { dbCommand.Parameters.AddWithValue("@IDUSUARIO", DBNull.Value); //PrimaryKey } dbCommand.Parameters.AddWithValue("@IDFUNCIONARIO", IDFUNCIONARIO); //Coluna dbCommand.Parameters.AddWithValue("@IDNIVELUSUARIO", IDNIVELUSUARIO); //Coluna dbCommand.Parameters.AddWithValue("@FLAGATIVO", FLAGATIVO); //Coluna dbCommand.Parameters.AddWithValue("@NOMEUSUARIO", NOMEUSUARIO); //Coluna dbCommand.Parameters.AddWithValue("@SENHAUSUARIO", SENHAUSUARIO); //Coluna dbCommand.Parameters.AddWithValue("@OBSERVACAO", OBSERVACAO); //Coluna //Retorno da Procedure FbParameter returnValue; returnValue = dbCommand.CreateParameter(); dbCommand.Parameters["@IDUSUARIO"].Direction = ParameterDirection.ReturnValue; //Executando consulta dbCommand.ExecuteNonQuery(); result = (int)(dbCommand.Parameters["@IDUSUARIO"].Value); if (dbTransaction == null) { dbCommand.Transaction.Commit(); dbCnn.Close(); } } catch (Exception ex) { if (dbTransaction != null) { this.RollbackTransaction(); } else { if (dbCommand.Transaction != null) { dbCommand.Transaction.Rollback(); } if (dbCnn.State == ConnectionState.Open) { dbCnn.Close(); } } throw ex; } return(result); }
/// <summary> /// This constuctor is used for testing. /// </summary> /// <param name="connectionString">The FireBird connection string to the database.</param> public DatabaseManager(String connectionString) { connection = new FbConnection(connectionString); connection.Open(); }
public USUARIOEntity Read(int IDUSUARIO) { 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_USUARIO", dbCnn); dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) { dbCnn = ((FbConnection)GetConnectionDB()); } if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Rea_USUARIO", dbCnn); dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.Parameters.AddWithValue("@IDUSUARIO", IDUSUARIO); //PrimaryKey reader = dbCommand.ExecuteReader(); USUARIOEntity 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 int Save(int?IDAGENDA, int IDSTATUS, DateTime DATA, string HORA, int IDGRUPOAGENDA, int IDEVENTO, string COMENTARIO, int IDFUNCIONARIO, int IDCLIENTE) { int result = 0; try { //Verificando a existência de um transação aberta if (dbTransaction != null) { if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_AGENDA", dbCnn); dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) { dbCnn = ((FbConnection)GetConnectionDB()); } if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_AGENDA", dbCnn); dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } dbCommand.CommandType = CommandType.StoredProcedure; //PrimaryKey com valor igual a null, indica um novo registro, //o valor da chave será fornecido pelo banco. Qualquer outro valor indicará edição do registro. if (IDAGENDA == -1) { dbCommand.Parameters.AddWithValue("@IDAGENDA", DBNull.Value); } else { dbCommand.Parameters.AddWithValue("@IDAGENDA", IDAGENDA); } dbCommand.Parameters.AddWithValue("@IDSTATUS", IDSTATUS); //Coluna dbCommand.Parameters.AddWithValue("@DATA", DATA); //Coluna dbCommand.Parameters.AddWithValue("@HORA", HORA); //Coluna dbCommand.Parameters.AddWithValue("@IDGRUPOAGENDA", IDGRUPOAGENDA); //Coluna dbCommand.Parameters.AddWithValue("@IDEVENTO", IDEVENTO); //Coluna dbCommand.Parameters.AddWithValue("@COMENTARIO", COMENTARIO); //Coluna if (IDFUNCIONARIO != null) { dbCommand.Parameters.AddWithValue("@IDFUNCIONARIO", IDFUNCIONARIO); //ForeignKey } else { dbCommand.Parameters.AddWithValue("@IDFUNCIONARIO", DBNull.Value); //ForeignKey 5 } dbCommand.Parameters.AddWithValue("@IDCLIENTE", IDCLIENTE); //Coluna //Retorno da Procedure FbParameter returnValue; returnValue = dbCommand.CreateParameter(); dbCommand.Parameters["@IDAGENDA"].Direction = ParameterDirection.InputOutput; //Executando consulta dbCommand.ExecuteNonQuery(); result = int.Parse(dbCommand.Parameters["@IDAGENDA"].Value.ToString()); if (dbTransaction == null) { dbCommand.Transaction.Commit(); dbCnn.Close(); } } catch (Exception ex) { if (dbTransaction != null) { this.RollbackTransaction(); } else { if (dbCommand.Transaction != null) { dbCommand.Transaction.Rollback(); } if (dbCnn.State == ConnectionState.Open) { dbCnn.Close(); } } throw ex; } return(result); }
public int Save(DIAGMEDIOPEDIDOEntity Entity) { int result = 0; try { //Verificando a existência de um transação aberta if (dbTransaction != null) { if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_DIAGMEDIOPEDIDO", dbCnn); dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) { dbCnn = ((FbConnection)GetConnectionDB()); } if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_DIAGMEDIOPEDIDO", dbCnn); dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } dbCommand.CommandType = CommandType.StoredProcedure; if (Entity.IDDIAGMEDIOPEDIDO != -1) { dbCommand.Parameters.AddWithValue("@IDDIAGMEDIOPEDIDO", Entity.IDDIAGMEDIOPEDIDO); //PrimaryKey } else { dbCommand.Parameters.AddWithValue("@IDDIAGMEDIOPEDIDO", DBNull.Value); //PrimaryKey } dbCommand.Parameters.AddWithValue("@IDPEDIDO", Entity.IDPEDIDO); //Coluna dbCommand.Parameters.AddWithValue("@DIRESFERICO", Entity.DIRESFERICO); //Coluna dbCommand.Parameters.AddWithValue("@DIRCILINDRICO", Entity.DIRCILINDRICO); //Coluna dbCommand.Parameters.AddWithValue("@DIREIXO", Entity.DIREIXO); //Coluna dbCommand.Parameters.AddWithValue("@DIRADICAO", Entity.DIRADICAO); //Coluna dbCommand.Parameters.AddWithValue("@DIRDNP", Entity.DIRDNP); //Coluna dbCommand.Parameters.AddWithValue("@DIRACO", Entity.DIRACO); //Coluna dbCommand.Parameters.AddWithValue("@ESQESFERICO", Entity.ESQESFERICO); //Coluna dbCommand.Parameters.AddWithValue("@ESQCILINDRICO", Entity.ESQCILINDRICO); //Coluna dbCommand.Parameters.AddWithValue("@ESQEIXO", Entity.ESQEIXO); //Coluna dbCommand.Parameters.AddWithValue("@ESQADICAO", Entity.ESQADICAO); //Coluna dbCommand.Parameters.AddWithValue("@ESQDNP", Entity.ESQDNP); //Coluna dbCommand.Parameters.AddWithValue("@ESQACO", Entity.ESQACO); //Coluna dbCommand.Parameters.AddWithValue("@LENTES", Entity.LENTES); //Coluna dbCommand.Parameters.AddWithValue("@ARMACAO", Entity.ARMACAO); //Coluna dbCommand.Parameters.AddWithValue("@DISTANCIAPUPILAR", Entity.DISTANCIAPUPILAR); //Coluna dbCommand.Parameters.AddWithValue("@DIREITO", Entity.DIREITO); //Coluna dbCommand.Parameters.AddWithValue("@ESQUERDO", Entity.ESQUERDO); //Coluna dbCommand.Parameters.AddWithValue("@DPA", Entity.DPA); //Coluna dbCommand.Parameters.AddWithValue("@MD", Entity.MD); //Coluna dbCommand.Parameters.AddWithValue("@MV", Entity.MV); //Coluna //Retorno da Procedure FbParameter returnValue; returnValue = dbCommand.CreateParameter(); dbCommand.Parameters["@IDDIAGMEDIOPEDIDO"].Direction = ParameterDirection.InputOutput; //Executando consulta dbCommand.ExecuteNonQuery(); result = int.Parse(dbCommand.Parameters["@IDDIAGMEDIOPEDIDO"].Value.ToString()); if (dbTransaction == null) { dbCommand.Transaction.Commit(); dbCnn.Close(); } } catch (Exception ex) { if (dbTransaction != null) { this.RollbackTransaction(); } else { if (dbCommand.Transaction != null) { dbCommand.Transaction.Rollback(); } if (dbCnn.State == ConnectionState.Open) { dbCnn.Close(); } } throw ex; } return(result); }
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(); }
public int Save(int?ID_ECF, string ECF_MOD, string ECF_FAB, string ECF_CX) { int result = 0; try { //Verificando a existência de um transação aberta if (dbTransaction != null) { if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_ECF", dbCnn); dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) { dbCnn = ((FbConnection)GetConnectionDB()); } if (dbCnn.State == ConnectionState.Closed) { dbCnn.Open(); } dbCommand = new FbCommand("Sav_ECF", dbCnn); dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } dbCommand.CommandType = CommandType.StoredProcedure; if (ID_ECF != null) { dbCommand.Parameters.AddWithValue("@ID_ECF", ID_ECF); //Coluna } else { dbCommand.Parameters.AddWithValue("@ID_ECF", DBNull.Value); //Coluna 3 } dbCommand.Parameters.AddWithValue("@ECF_MOD", ECF_MOD); //Coluna dbCommand.Parameters.AddWithValue("@ECF_FAB", ECF_FAB); //Coluna dbCommand.Parameters.AddWithValue("@ECF_CX", ECF_CX); //Coluna //Retorno da Procedure FbParameter returnValue; returnValue = dbCommand.CreateParameter(); dbCommand.Parameters[""].Direction = ParameterDirection.InputOutput; //Executando consulta dbCommand.ExecuteNonQuery(); result = int.Parse(dbCommand.Parameters[""].Value.ToString()); if (dbTransaction == null) { dbCommand.Transaction.Commit(); dbCnn.Close(); } } catch (Exception ex) { if (dbTransaction != null) { this.RollbackTransaction(); } else { if (dbCommand.Transaction != null) { dbCommand.Transaction.Rollback(); } if (dbCnn.State == ConnectionState.Open) { dbCnn.Close(); } } throw ex; } return(result); }
private static DataTable SqlCommandSchema(FbConnection connection, string collectionName, string[] restrictions) { throw new NotImplementedException(); }
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 void SatisHaftalik_Load(object sender, EventArgs e) { string tarih = DateTime.Now.Year.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString(); DateTime tarih22 = DateTime.Now.AddDays(-7); string tarih2 = tarih22.Year.ToString() + "-" + tarih22.Month.ToString() + "-" + tarih22.Day.ToString(); label1.Text = tarih22.Day.ToString() + " / " + tarih22.Month.ToString() + " / " + tarih22.Year.ToString() + " - " + DateTime.Now.Day.ToString() + " / " + DateTime.Now.Month.ToString() + " / " + DateTime.Now.Year.ToString() + " Raporları"; float top = 0; int i = 0; float top2 = 0; FbConnection baglan = new FbConnection(fk.Baglanti_Kodu()); FbConnection baglan2 = new FbConnection(fk.Baglanti_Kodu()); FbConnection baglan3 = new FbConnection(fk.Baglanti_Kodu()); baglan.Open(); //FbCommand BugunkuSatis = new FbCommand("SELECT Satis_urun FROM Satis WHERE Satis_tarih='" + tarih + "'", baglan); FbCommand BugunkuSatis = new FbCommand("SELECT Satis_urun FROM Satis WHERE Satis_tarih between '" + tarih2 + "' and '" + tarih + "'", baglan); //FbCommand KacAdet = new FbCommand("SELECT COUNT(*) FROM Satis WHERE Satis_tarih='" + tarih + "'", baglan); object SatirSayisi = KacAdet.ExecuteScalar(); FbCommand KacAdet = new FbCommand("SELECT COUNT(*) FROM Satis WHERE Satis_tarih between '" + tarih2 + "' and '" + tarih + "'", baglan); object SatirSayisi = KacAdet.ExecuteScalar(); FbDataReader oku1; object SatisSonuc; SatisSonuc = BugunkuSatis.ExecuteScalar(); if (SatisSonuc != null) { urunler = new string[int.Parse(SatirSayisi.ToString())]; oku1 = BugunkuSatis.ExecuteReader(); while (oku1.Read()) { urunler[i] = oku1["Satis_urun"].ToString(); i++; } oku1.Close(); baglan.Close(); baglan2.Open(); FbDataReader oku2; for (int j = 0; j <= int.Parse(SatirSayisi.ToString()) - 1; j++) { FbCommand UrunFiyat = new FbCommand("SELECT Urun_fiyat FROM Urunler WHERE Urun_id='" + urunler[j] + "'", baglan2); oku2 = UrunFiyat.ExecuteReader(); oku2.Read(); top += float.Parse(oku2["Urun_fiyat"].ToString()); label5.Text = top.ToString(); oku2.Close(); } baglan2.Close(); label5.Text += " TL"; baglan2.Open(); FbDataReader say; for (int j = 0; j <= int.Parse(SatirSayisi.ToString()) - 1; j++) { FbCommand UrunFiyat = new FbCommand("SELECT Urun_alisFiyat FROM Urunler WHERE Urun_id='" + urunler[j] + "'", baglan2); say = UrunFiyat.ExecuteReader(); say.Read(); top2 += float.Parse(say["Urun_alisFiyat"].ToString()); say.Close(); } baglan2.Close(); // net Kar label9.Text = (top - top2).ToString() + " TL"; // net Kar baglan.Open(); FbCommand EncokSatilan = new FbCommand("SELECT Urun_adi FROM Urunler WHERE Urun_id = (SELECT first 1 rapor_satisId FROM Rapor WHERE rapor_tarih between '" + tarih2 + "' and '" + tarih + "' and rapor_sayac = ( SELECT MAX(rapor_sayac) FROM Rapor WHERE rapor_tarih between '" + tarih2 + "' and '" + tarih + "'))", baglan); FbDataReader EncokStOku = EncokSatilan.ExecuteReader(); EncokStOku.Read(); label6.Text = EncokStOku["Urun_adi"].ToString(); baglan.Close(); baglan.Open(); FbCommand EncokKategori = new FbCommand("SELECT Kategori_adi FROM Urun_kategori WHERE Kategori_id = (SELECT first 1 Urun_kategori FROM Urunler WHERE Urun_id = ( SELECT first 1 rapor_satisId FROM Rapor WHERE rapor_tarih between '" + tarih2 + "' and '" + tarih + "' and rapor_sayac = ( SELECT MAX(rapor_sayac) FROM Rapor WHERE rapor_tarih between '" + tarih2 + "' and '" + tarih + "')))", baglan); FbDataReader EncokKtOku = EncokKategori.ExecuteReader(); EncokKtOku.Read(); label7.Text = EncokKtOku["Kategori_adi"].ToString(); baglan.Close(); baglan.Open(); FbCommand UrunSayi = new FbCommand("SELECT SUM(rapor_sayac) FROM Rapor WHERE rapor_tarih between '" + tarih2 + "' and '" + tarih + "' GROUP BY rapor_satisKt", baglan); object SatisAdet = UrunSayi.ExecuteScalar(); baglan.Close(); // Buraya kadar tamamlanmıştır.. int[] SeriesPoint = new int[0]; FbCommand UrunKategori = new FbCommand("SELECT rapor_satisKt FROM Rapor WHERE rapor_tarih between '" + tarih2 + "' and '" + tarih + "' GROUP BY rapor_satisKt", baglan); FbCommand UrunKategoriSatisSys = new FbCommand("SELECT SUM(rapor_sayac) FROM Rapor WHERE rapor_tarih between '" + tarih2 + "' and '" + tarih + "' GROUP BY rapor_satisKt", baglan3); // Grafik için sayısal değerler baglan3.Open(); int sa = 0; FbDataReader OkuUKSS = UrunKategoriSatisSys.ExecuteReader(); while (OkuUKSS.Read()) { Array.Resize(ref SeriesPoint, SeriesPoint.Length + 1); SeriesPoint[sa] = int.Parse(OkuUKSS[0].ToString()); sa++; } // kategori satış sayıları diziye atıldı baglan3.Close(); baglan.Open(); FbDataReader ktOku = UrunKategori.ExecuteReader(); istatislik.Titles.Add("Haftalık Satış Raporu Grafik"); for (int k = 0; k < SeriesPoint.Length; k++) { ktOku.Read(); baglan2.Open(); FbCommand Kategori = new FbCommand("SELECT Kategori_adi FROM Urun_kategori WHERE Kategori_id='" + ktOku["rapor_satisKt"].ToString() + "'", baglan2); FbDataReader Ktad = Kategori.ExecuteReader(); Ktad.Read(); Series series = istatislik.Series.Add(Ktad["Kategori_adi"].ToString()); series.Points.Add(SeriesPoint[k]); baglan2.Close(); } baglan.Close(); // Manuelsatışlar baglan.Open(); FbCommand ManuelSatislar = new FbCommand("SELECT * FROM ManuelSatis WHERE satis_tarih between '" + tarih2 + "' and '" + tarih + "'", baglan); object sn = ManuelSatislar.ExecuteScalar(); int msay = 0; if (sn != null) { FbDataReader moku = ManuelSatislar.ExecuteReader(); while (moku.Read()) { top += float.Parse(moku["satis_fiyat"].ToString()); top2 += float.Parse(moku["satis_alisFiyat"].ToString()); msay++; } label5.Text = top.ToString(); label9.Text = (top - top2).ToString(); Series series = istatislik.Series.Add("Manuel Satış"); series.Points.Add(msay); } baglan.Close(); // Manuelsatışlar } else { MessageBox.Show("Haftalık olarak şuana kadar satış yapılmamıştır. (R-02)", "Bilgilendirme"); this.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(); }
public static List <Employee> GetListPersonalFromDb(FbConnection db, int id_repair) { return(new List <Employee>()); }
internal ConnectionFireBird() { Conn = new FbConnection(); }
public int Save(int? IDDATACOMEMORATIVA, int IDCLIENTE, int IDPARENTESCO, int IDOCASIAO, DateTime DATACOM, string OBSERVACAO, string NOMECOMEMORATIVO) { int result = 0; try { //Verificando a existência de um transação aberta if (dbTransaction != null) { if (dbCnn.State == ConnectionState.Closed) dbCnn.Open(); dbCommand = new FbCommand("Sav_DATACOMEMORATIVA", dbCnn); dbCommand.Transaction = ((FbTransaction)(dbTransaction)); } else { if (dbCnn == null) dbCnn = ((FbConnection)GetConnectionDB()); if (dbCnn.State == ConnectionState.Closed) dbCnn.Open(); dbCommand = new FbCommand("Sav_DATACOMEMORATIVA", dbCnn); dbCommand.Transaction = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted); } dbCommand.CommandType = CommandType.StoredProcedure; if(IDDATACOMEMORATIVA!= -1) dbCommand.Parameters.AddWithValue("@IDDATACOMEMORATIVA", IDDATACOMEMORATIVA); //PrimaryKey else dbCommand.Parameters.AddWithValue("@IDDATACOMEMORATIVA", DBNull.Value); //PrimaryKey dbCommand.Parameters.AddWithValue("@IDCLIENTE", IDCLIENTE); //Coluna dbCommand.Parameters.AddWithValue("@IDPARENTESCO", IDPARENTESCO); //Coluna dbCommand.Parameters.AddWithValue("@IDOCASIAO", IDOCASIAO); //Coluna dbCommand.Parameters.AddWithValue("@DATACOM", DATACOM); //Coluna dbCommand.Parameters.AddWithValue("@OBSERVACAO", OBSERVACAO); //Coluna dbCommand.Parameters.AddWithValue("@NOMECOMEMORATIVO", NOMECOMEMORATIVO); //Coluna //Retorno da Procedure FbParameter returnValue; returnValue = dbCommand.CreateParameter(); dbCommand.Parameters["@IDDATACOMEMORATIVA"].Direction = ParameterDirection.InputOutput; //Executando consulta dbCommand.ExecuteNonQuery(); result = int.Parse(dbCommand.Parameters["@IDDATACOMEMORATIVA"].Value.ToString()); if (dbTransaction == null) { dbCommand.Transaction.Commit(); dbCnn.Close(); } } catch (Exception ex) { if (dbTransaction != null) this.RollbackTransaction(); else { if (dbCommand.Transaction != null) dbCommand.Transaction.Rollback(); if (dbCnn.State == ConnectionState.Open) dbCnn.Close(); } throw ex; } return result; }