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 void BeginTrasactionTest() { string connectionString = this.BuildConnectionString(); FbConnection conn01 = new FbConnection(connectionString); conn01.Open(); FbTransaction txn01 = conn01.BeginTransaction(IsolationLevel.Unspecified); txn01.Rollback(); conn01.Close(); FbConnection conn02 = new FbConnection(connectionString); conn02.Open(); FbTransaction txn02 = conn02.BeginTransaction(IsolationLevel.ReadCommitted); txn02.Rollback(); conn02.Close(); FbConnection conn03 = new FbConnection(connectionString); conn03.Open(); FbTransaction txn03 = conn03.BeginTransaction(IsolationLevel.ReadUncommitted); txn03.Rollback(); conn03.Close(); FbConnection conn04 = new FbConnection(connectionString); conn04.Open(); FbTransaction txn04 = conn04.BeginTransaction(IsolationLevel.RepeatableRead); txn04.Rollback(); conn04.Close(); FbConnection conn05 = new FbConnection(connectionString); conn05.Open(); FbTransaction txn05 = conn05.BeginTransaction(IsolationLevel.Serializable); txn05.Rollback(); conn05.Close(); }
public static bool abrir() { conexao = new FbConnection(stringConexao); conexao.Open(); transacao = conexao.BeginTransaction(); return true; }
internal static void Add(MovieInfo movieInfo) { string commandText = @"INSERT INTO MOVIE_DATA ( LOCATION, FILE_DATA_X, FILE_DATA_Y, FILE_DATA_DURATION, RAW_TITLE_PATH) VALUES (@location, @file_data_x, @file_data_y, @file_duration, @raw_title_path)"; using (FbConnection connection = new FbConnection (string.Format (CONNECTION_STRING_FORMAT_FIREBIRD, DB_FILENAME))) { connection.Open (); using (FbTransaction transaction = connection.BeginTransaction ()) { using (FbCommand cmd = new FbCommand (commandText, connection, transaction)) { cmd.Parameters.Add (new FbParameter ("@location", movieInfo.DvdName)); cmd.Parameters.Add (new FbParameter ("@file_data_x", movieInfo.FileData.X)); cmd.Parameters.Add (new FbParameter ("@file_data_y", movieInfo.FileData.Y)); cmd.Parameters.Add (new FbParameter ("@file_duration", movieInfo.FileData.Duration)); cmd.Parameters.Add (new FbParameter ("@raw_title_path", movieInfo.RawTitlePath)); cmd.ExecuteNonQuery (); } transaction.Commit (); } } }
public static bool check_func(FbConnectionStringBuilder fc) { bool res_ = false; using (FbConnection fb = new FbConnection(fc.ConnectionString)) { try { fb.Open(); using (FbTransaction ft = fb.BeginTransaction()) { using (FbCommand fcon = new FbCommand(sql_func,fb,ft)) { using (FbDataReader fr = fcon.ExecuteReader()) { while (fr.Read()) { res_ = true; } fr.Dispose(); } fcon.Dispose(); } ft.Commit(); ft.Dispose(); } } catch { } finally { fb.Close(); } fb.Dispose(); } return res_; }
public override void execute(Options options, INIManager iniManager) { string AppKey = iniManager.IniReadValue("APP", "AppKey"); string AppSecret = iniManager.IniReadValue("APP", "AppSecret"); string TaobaoAsistantPath = iniManager.IniReadValue("淘宝助理", "安装目录"); string nick = options.Nick; string SessionKey = iniManager.IniReadValue(nick, "SessionKey"); string userId = iniManager.IniReadValue(nick, "UserId"); if (userId == null || userId.Trim().Equals("")) { userId = "1696293148"; } StreamWriter MovePicLogWriter; FileStream MovePicLog = new FileStream(Environment.CurrentDirectory + "\\" + "MovePic.log", FileMode.Append); MovePicLogWriter = new StreamWriter(MovePicLog, Encoding.Default); FbConnectionStringBuilder cs = new FbConnectionStringBuilder(); cs.Database = Path.Combine(TaobaoAsistantPath, "users\\" + nick + "\\APPITEM.DAT"); cs.Charset = "UTF8"; cs.UserID = "SYSDBA"; cs.Password = "******"; cs.ServerType = FbServerType.Embedded; FbConnection fbCon = new FbConnection(cs.ToString()); fbCon.Open(); FbCommand ItemsCommand = null; ItemsCommand = new FbCommand("SELECT * FROM ITEM WHERE OUTER_ID = @SearchString OR TITLE = @SearchString", fbCon); FbParameter SearchString = new FbParameter("@SearchString", FbDbType.VarChar); SearchString.Value = options.Item; ItemsCommand.Parameters.Add(SearchString); FbDataReader ItemsReader = ItemsCommand.ExecuteReader(); while (ItemsReader.Read()) { MovePicLogWriter.WriteLine("--------------------------------------------------------------------------------"); Console.WriteLine("--------------------------------------------------------------------------------"); string ClientID = ItemsReader["CLIENT_ID"].ToString(); string Title = ItemsReader["TITLE"].ToString(); Console.WriteLine("ClientID=" + ClientID); MovePicLogWriter.WriteLine("ClientID=" + ClientID); Console.WriteLine("TITLE=" + Title); MovePicLogWriter.WriteLine("TITLE=" + Title); FbTransaction myTransaction = fbCon.BeginTransaction(); FbCommand DeleteCommand = new FbCommand("UPDATE ITEM SET CLIENT_IS_DELETE = 1 WHERE CLIENT_ID = @ClientID", fbCon); FbParameter ParamID = new FbParameter("@ClientID", FbDbType.VarChar); ParamID.Value = ClientID; DeleteCommand.Parameters.Add(ParamID); DeleteCommand.Transaction = myTransaction; DeleteCommand.ExecuteNonQuery(); myTransaction.Commit(); } fbCon.Close(); MovePicLogWriter.Close(); MovePicLog.Close(); }
public void BeginTransactionNoWaitTimeoutTest() { using (FbConnection conn = new FbConnection(this.BuildConnectionString())) { conn.Open(); FbTransaction tx = conn.BeginTransaction(new FbTransactionOptions() { WaitTimeout = null }); Assert.NotNull(tx); tx.Rollback(); } }
public static void DatabaseCommand(string command) { FbConnection con = new FbConnection(ConStr.ConnectionString); con.Open(); FbTransaction transaction = con.BeginTransaction(); FbCommand com = new FbCommand(command, con, transaction); com.ExecuteNonQuery(); transaction.Commit(); con.Close(); }
public static string GetOneElement(string command) { string element = ""; FbConnection con = new FbConnection(ConStr.ConnectionString); con.Open(); FbTransaction transaction = con.BeginTransaction(); FbCommand com = new FbCommand(command, con, transaction); com.ExecuteNonQuery(); FbDataReader datareader; datareader = com.ExecuteReader(); while (datareader.Read()) { element = datareader[0].ToString(); } con.Close(); return element; }
public static bool ExistsElement(string command) { string element = ""; FbConnection con = new FbConnection(ConStr.ConnectionString); con.Open(); FbTransaction transaction = con.BeginTransaction(); FbCommand com = new FbCommand(command, con, transaction); com.ExecuteNonQuery(); FbDataReader datareader; datareader = com.ExecuteReader(); while (datareader.Read()) { element = datareader[0].ToString(); } con.Close(); if (element != null && element != "") return true; return false; }
internal static void Delete(MovieInfo movieInfo) { string commandText = @"DELETE FROM MOVIE_DATA WHERE LOCATION = @location AND RAW_TITLE_PATH = @raw_title_path"; using (FbConnection connection = new FbConnection (string.Format (CONNECTION_STRING_FORMAT_FIREBIRD, DB_FILENAME))) { connection.Open (); using (FbTransaction transaction = connection.BeginTransaction ()) { using (FbCommand cmd = new FbCommand (commandText, connection, transaction)) { cmd.Parameters.Add (new FbParameter ("@location", movieInfo.DvdName)); cmd.Parameters.Add (new FbParameter ("@raw_title_path", movieInfo.RawTitlePath)); cmd.ExecuteNonQuery (); } transaction.Commit (); } } }
protected 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; }
public List<Company> GetCompanies() { try { var myConnection = new FbConnection(GetConnectionString()); myConnection.Open(); var myTransaction = myConnection.BeginTransaction(); var myCommand = new FbCommand { Connection = myConnection, Transaction = myTransaction, CommandText = "SELECT * FROM COMPANIES ORDER BY CODE" }; var myReader = myCommand.ExecuteReader(); var companies = new List<Company>(); while (myReader.Read()) { var company = new Company { Code = myReader["CODE"].ToString(), Description = myReader["DESCRIPTION"].ToString() }; companies.Add(company); } return companies; } catch (Exception ex) { MessageBox.Show(ex.Message); return null; } }
public static void LoadOwners(Database databaseSettings) { _owners = new List<IOwnerData>(); try { using (var connection = new FbConnection(GetConnectionString(databaseSettings))) { connection.Open(); using (var command = new FbCommand("select * from firms order by id", connection, connection.BeginTransaction(IsolationLevel.ReadCommitted))) { using (var reader = command.ExecuteReader()) { while (reader.Read()) { _owners.Add(new OwnerData { ID = reader.GetString(0).Trim(), Name = reader.GetString(1).Trim(), Address = reader.GetString(2).Trim(), Inn = reader.GetString(3).Trim(), Kpp = reader.GetString(4).Trim(), Warrant = reader.GetString(5).Trim(), Section = reader.GetInt32(6) }); } } } } } catch (FbException) { } }
//private void _ConnectDB() //{ // if (!this.IsOpen()) // this._db.Open(); //} //public void DisconnectDB() //{ // if (this.IsOpen()) // _db.Close(); //} private List<Dictionary<string, string>> SqlSelect(string sqlQuery, string[] paramList) { List<Dictionary<string, string>> ret = new List<Dictionary<string, string>>(); using (FbConnection db = new FbConnection(this._genLocalDBPathIfLocalDB(_connectionString))) { db.Open(); using (FbTransaction trans = db.BeginTransaction()) { using (FbCommand cmd = new FbCommand(sqlQuery, db, trans)) { for (int ii = 0; ii < Math.Floor(paramList.Count() / 2.0); ii++) { cmd.Parameters.AddWithValue(paramList[ii * 2], paramList[ii * 2 + 1]); } using (FbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Dictionary<string, string> row = new Dictionary<string, string>(); for (int jj = 0; jj < reader.FieldCount; jj++) { row.Add(reader.GetName(jj).ToLower(), reader.GetString(jj)); } ret.Add(row); } } } trans.Commit(); } } return ret; }
static void SyncProject(string username) { //вывожу ее отдельно, так как здесь немного другая логика работы using (var db = new ApplicationDbContext()) { var t = db.tModerator.First(a => a.tUsers.UserName == username); if (t != null) { var fc = new FbConnectionStringBuilder { UserID = t.tModerator_userId, Password = t.tModerator_password, Database = t.tModerator_database, Charset = "win1251", Pooling = false, Role = "R_ADMIN" }; using (var fb = new FbConnection(fc.ConnectionString)) { try { fb.Open(); using (var ft = fb.BeginTransaction()) { using ( var fcon = new FbCommand( "select p.ID_PROJECTS, p.MNEMO from PROJECTS p order by 1", fb, ft)) { using (var fr = fcon.ExecuteReader()) { while (fr.Read()) { //Добавляем проект if (db.tProject.Find(fr[0]) != null) { var m = db.tProject.Find(fr[0]); m.tProject_name = fr[1].ToString(); db.Entry(m).State = System.Data.Entity.EntityState.Modified; db.SaveChanges(); } else { var m = new tProject { Id = (int)fr[0], tProject_name = fr[1].ToString() }; db.tProject.Add(m); db.SaveChanges(); } //теперь добавим подсистему using ( var fconSub = new FbCommand( "select s.ID_SUBSYSTEM, s.MNEMO from SUBSYSTEMS s where s.ID_PROJECTS = @id order by 1", fb, ft)) { fconSub.Parameters.AddWithValue("@id", (int)fr[0]); using (var frSub = fconSub.ExecuteReader()) { while (frSub.Read()) { if (db.tSubsystem.Find(frSub[0]) != null) { var sub = db.tSubsystem.Find(frSub[0]); sub.tSubsystem_name = frSub[1].ToString(); db.Entry(sub).State = System.Data.Entity.EntityState.Modified; } else { var sub = new tSubsystem { Id = (int)frSub[0], tSubsystem_name = frSub[1].ToString(), tproject = db.tProject.Find(fr[0]) }; db.tSubsystem.Add(sub); db.SaveChanges(); } } frSub.Dispose(); } fconSub.Dispose(); } //Релизы проектов using ( var fconSub = new FbCommand( "select pr.ID_RELEASE_PROJECTS, pr.MNEMO from RELEASE_PROJECTS pr where pr.ID_PROJECTS = @id order by pr.MNEMO", fb, ft)) { fconSub.Parameters.AddWithValue("@id", (int)fr[0]); using (var frSub = fconSub.ExecuteReader()) { while (frSub.Read()) { if (db.tReleaseProject.Find(frSub[0]) != null) { var sub = db.tReleaseProject.Find(frSub[0]); sub.tReleaseProject_name = frSub[1].ToString(); db.Entry(sub).State = System.Data.Entity.EntityState.Modified; } else { var sub = new tReleaseProject { Id = (int)frSub[0], tReleaseProject_name = frSub[1].ToString(), tproject = db.tProject.Find(fr[0]) }; db.tReleaseProject.Add(sub); db.SaveChanges(); } } frSub.Dispose(); } fconSub.Dispose(); } } fr.Dispose(); } fcon.Dispose(); } ft.Commit(); ft.Dispose(); } } catch{} finally { fb.Close(); } fb.Dispose(); } } db.Dispose(); } }
static void SyncBranch(string username) { using (var db = new ApplicationDbContext()) { var t = db.tModerator.First(a => a.tUsers.UserName == username); if (t != null) { var fc = new FbConnectionStringBuilder { UserID = t.tModerator_userId, Password = t.tModerator_password, Database = t.tModerator_database, Charset = "win1251", Pooling = false, Role = "R_ADMIN" }; using (var fb = new FbConnection(fc.ConnectionString)) { try { fb.Open(); using (var ft = fb.BeginTransaction()) { using (var fcon = new FbCommand("select b.id_branch, b.mnemo from branch b", fb, ft)) { using (var fr = fcon.ExecuteReader()) { while (fr.Read()) { if (db.tBranch.Find(fr[0]) != null) { var m = db.tBranch.Find(fr[0]); m.tBranch_name = fr[1].ToString(); db.Entry(m).State = System.Data.Entity.EntityState.Modified; db.SaveChanges(); } else { var m = new tBranch { Id = (int)fr[0], tBranch_name = fr[1].ToString() }; db.tBranch.Add(m); db.SaveChanges(); } } fr.Dispose(); } fcon.Dispose(); } ft.Commit(); ft.Dispose(); } } catch { //Пропускаем все ошибки - сихн е удалась } finally { fb.Close(); } fb.Dispose(); } } db.Dispose(); } }
private void ExecuteScript(FbConnection myConnection, string scriptPath) { try { using (FbTransaction myTransaction = myConnection.BeginTransaction()) { if (!File.Exists(scriptPath)) throw new FileNotFoundException("Script not found", scriptPath); string script = File.ReadAllText(scriptPath); // use FbScript to parse all statements FbScript fbs = new FbScript(script); fbs.Parse(); // execute all statements FbBatchExecution fbe = new FbBatchExecution(myConnection, fbs); fbe.CommandExecuting += (sender, args) => args.SqlCommand.Transaction = myTransaction; fbe.Execute(true); } } catch (Exception e) { Console.WriteLine(e.ToString()); throw; } }
void CreateDb(string ConnStr, string ScriptPath) { var assembly = Assembly.GetExecutingAssembly(); var stream = assembly.GetManifestResourceStream(typeof(FbDatabase).Namespace + ".struct.sql"); string script = new StreamReader(stream, System.Text.Encoding.ASCII).ReadToEnd(); FbScript fbs = new FbScript(script); fbs.Parse(); FbConnection.CreateDatabase(_conw.ConnectionString); using (var c = new FbConnection(ConnStr)) { c.Open(); try { using (FbTransaction myTransaction = c.BeginTransaction()) { FbBatchExecution fbe = new FbBatchExecution(c, fbs); fbe.CommandExecuting += (sender, args) => args.SqlCommand.Transaction = myTransaction; fbe.Execute(true); } } finally { c.Close(); } } }
private static void DeleteAllData(string connectionString) { using (var connection = new FbConnection(connectionString)) { connection.Open(); var commandText = @" execute block as declare name type of column rdb$relations.rdb$relation_name; begin for select rdb$relation_name from rdb$relations where coalesce(rdb$system_flag, 0) = 0 into name do begin execute statement 'delete from ' || name; end end"; using (var transaction = connection.BeginTransaction()) { using (var command = new FbCommand(commandText, connection, transaction)) { command.ExecuteNonQuery(); } transaction.Commit(); } } }
/// <summary> /// Копирование содержимого базы данных в xml-файл /// </summary> /// <param name="fileName">Имя файла</param> /// <param name="tableNames">Порядок копирования таблиц</param> /// <param name="fbConnection">Подключение к базе данных</param> private void DoCopyData(string fileName, string[] tableNames, FbConnection fbConnection) { using (FbTransaction fbTransaction = fbConnection.BeginTransaction()) using (var writer = CashDataWriterFactory.CreateDataStream(fileName)) { foreach (string tableName in tableNames) { CopyTable(writer, fbConnection, fbTransaction, tableName); } // копируем генераторы CopyGenerators(writer, fbConnection, fbTransaction); } }
public void BeginTransactionWithWaitTimeoutTest() { using (FbConnection conn = new FbConnection(BuildConnectionString(this.FbServerType))) { conn.Open(); FbTransaction tx = conn.BeginTransaction(new FbTransactionOptions() { WaitTimeout = TimeSpan.FromSeconds(10) }); Assert.NotNull(tx); tx.Rollback(); } }
public void BeginTransactionWithWaitTimeoutInvalidValue1Test() { using (FbConnection conn = new FbConnection(BuildConnectionString(this.FbServerType))) { conn.Open(); Assert.Throws<ArgumentException>(() => conn.BeginTransaction(new FbTransactionOptions() { WaitTimeout = TimeSpan.FromDays(9999) })); } }
public static ITransaction BeginTransaction(FirebirdSQLDatabase database, string connectionString, IsolationLevel level) { FbConnection connection = new FbConnection(connectionString); connection.Open(); return new FirebirdTransaction(database, connection, connection.BeginTransaction(level)); }
public virtual void SetUp() { TestsSetup.SetUp(_fbServerType); string cs = BuildConnectionString(_fbServerType); InsertTestData(cs); _connection = new FbConnection(cs); _connection.Open(); if (_withTransaction) { _transaction = _connection.BeginTransaction(); } }
public void BeginTransactionWithWaitTimeoutInvalidValue2Test() { using (FbConnection conn = new FbConnection(this.BuildConnectionString())) { conn.Open(); Assert.Throws<ArgumentException>(() => conn.BeginTransaction(new FbTransactionOptions() { WaitTimeout = TimeSpan.FromMilliseconds(1) })); } }
private void BeginTransactionILTestsHelper(IsolationLevel level) { using (FbConnection conn = new FbConnection(this.BuildConnectionString())) { conn.Open(); FbTransaction tx = conn.BeginTransaction(level); Assert.NotNull(tx); tx.Rollback(); } }
public override bool DeleteRole(string rolename, bool throwOnPopulatedRole) { if (!RoleExists(rolename)) { throw new ProviderException("Role does not exist."); } if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0) { throw new ProviderException("Cannot delete a populated role."); } using (FbConnection conn = new FbConnection(connectionString)) { conn.Open(); using (FbCommand cmd = new FbCommand("ROLES_DELETEROLE", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@ApplicationName", FbDbType.VarChar, 100).Value = ApplicationName; cmd.Parameters.Add("@Rolename", FbDbType.VarChar, 100).Value = rolename; FbTransaction tran = conn.BeginTransaction(); cmd.Transaction = tran; try { cmd.ExecuteNonQuery(); tran.Commit(); } catch (FbException ex) { tran.Rollback(); if (WriteExceptionsToEventLog) { WriteToEventLog(ex, "DeleteRole"); return false; } else { throw ex; } } } } return true; }
public static string Export(DateTime from, DateTime to, bool filterEnabled = false, string companyFrom = "", string companyTo = "") { if (_bRunning) return @"Export already running"; _bRunning = true; var iCounter = 0; var fileName = Settings.Default.PeopleExportLocation; var shiftsFileName = Settings.Default.PeopleShiftsExportLocation; try { // Set the ServerType to 1 for connect to the embedded server var connectionString = "User=SYSDBA;" + "Password=masterkey;" + "Database=" + Settings.Default.TmpDbLocation + ";" + "DataSource=" +Settings.Default.NetworkLocation +";" + "Port=3050;" + "Dialect=3;" + "Charset=NONE;" + "Role=;" + "Connection lifetime=15;" + "Pooling=true;" + "Packet Size=8192;" + "ServerType=0"; var myConnection = new FbConnection(connectionString); myConnection.Open(); var myTransaction = myConnection.BeginTransaction(); var myCommand = new FbCommand { Connection = myConnection, Transaction = myTransaction, CommandText = "SELECT COUNT(*) FROM CLOCKD " + "WHERE DATETIME >= @START_DATE AND DATETIME <= @END_DATE AND a.CALC0 IS NOT NULL" }; if (filterEnabled) { myCommand.CommandText = "SELECT COUNT(*) FROM CLOCKD a " + "INNER JOIN EMP b ON a.EMPNO = b.EMP_NO " + "WHERE" + " a.DATETIME >= @START_DATE AND a.DATETIME <= @END_DATE AND" + " b.COMPANY >= @COMPANYFROM AND b.COMPANY <= @COMPANYTO AND a.CALC0 IS NOT NULL"; myCommand.Parameters.Add("@COMPANYFROM", FbDbType.VarChar).Value = companyFrom; myCommand.Parameters.Add("@COMPANYTO", FbDbType.VarChar).Value = companyTo; } myCommand.Parameters.Add("@START_DATE", FbDbType.TimeStamp).Value = Helpers.GetLowestDt(from); myCommand.Parameters.Add("@END_DATE", FbDbType.TimeStamp).Value = Helpers.GetHighestDt(to); //Get amount of records var iCount = (int) myCommand.ExecuteScalar(); myCommand.CommandText = "SELECT * FROM CLOCKD " + "WHERE DATETIME >= @START_DATE AND DATETIME <= @END_DATE " + "ORDER BY EMPNO, DATETIME"; if (filterEnabled) { myCommand.CommandText = "SELECT * FROM CLOCKD a " + "INNER JOIN EMP b ON a.EMPNO = b.EMP_NO " + "WHERE a.DATETIME >= @START_DATE AND a.DATETIME <= @END_DATE AND " + "b.COMPANY >= @COMPANYFROM AND b.COMPANY <= @COMPANYTO AND a.CALC0 IS NOT NULL AND a.TARGET0 IS NOT NULL " + "ORDER BY a.EMPNO, a.DATETIME"; } myCommand.Parameters.Add("@START_DATE", FbDbType.TimeStamp).Value = Helpers.GetLowestDt(from); myCommand.Parameters.Add("@END_DATE", FbDbType.TimeStamp).Value = Helpers.GetHighestDt(to); var myReader = myCommand.ExecuteReader(); var lastEmployee = ""; var internalCounter = 0; var ds = new DataSet("NewDataSet"); var dt = new DataTable("NewDataTable"); //Set the locale for each -> Hours ds.Locale = Thread.CurrentThread.CurrentCulture; dt.Locale = Thread.CurrentThread.CurrentCulture; dt.Columns.Add("Employee Code"); dt.Columns.Add("OT 1.5 Hours Worked", typeof (double)); dt.Columns.Add("OT2.0 Hours Worked", typeof (double)); dt.Columns.Add("Lost Time Hours", typeof (double)); //Set locale for each -> Shift Template var shiftDs = new DataSet("ShiftDataSet"); var shiftDt = new DataTable("ShiftDt"); shiftDs.Locale = Thread.CurrentThread.CurrentCulture; shiftDs.Locale = Thread.CurrentThread.CurrentCulture; shiftDt.Columns.Add("Employee Code"); shiftDt.Columns.Add("12Shift Days Worked"); shiftDt.Columns.Add("2 Shift Days Worked"); shiftDt.Columns.Add("3 Shift Days Worked"); shiftDt.Columns.Add("Normal Shift Worked"); var overTime = 0.0; var doubleTime = 0.0; var lostTime = 0.0; while (myReader.Read()) { //increment toolbar OnProgress((int) Math.Round((double) (100*iCounter++)/iCount)); //Last employee var emp = myReader["EMPNO"].ToString().PadLeft(8, '0'); var shiftCode = myReader["WORKPAT"].ToString(); if (emp != lastEmployee && lastEmployee != "" || internalCounter +1 == iCount) { //If it is the last record, add the last hours if (internalCounter+1 == iCount) { lostTime += Convert.ToDouble(myReader["LOST"]); overTime += Convert.ToDouble(myReader["CALC1"]); doubleTime += Convert.ToDouble(myReader["CALC2"]); AddShift(shiftCode); } //Check if all values are zero - if yes do not show on report if ((lostTime + overTime + doubleTime) > 0) { //Convert values to decimal values overTime = overTime/60.0; doubleTime = doubleTime / 60.0; lostTime = lostTime / 60.0; dt.Rows.Add(lastEmployee, overTime, doubleTime, lostTime); } shiftDt.Rows.Add(lastEmployee, _twelveShift, _twoShift, _threeShift, _normalShift); overTime = 0; doubleTime = 0; lostTime = 0; _normalShift = 0; _twoShift = 0; _threeShift = 0; _twelveShift = 0; //Add the values of the new record lostTime += Convert.ToDouble(myReader["LOST"]); overTime += Convert.ToDouble(myReader["CALC1"]); doubleTime += Convert.ToDouble(myReader["CALC2"]); AddShift(shiftCode); } else { lostTime += Convert.ToDouble(myReader["LOST"]); overTime += Convert.ToDouble(myReader["CALC1"]); doubleTime += Convert.ToDouble(myReader["CALC2"]); AddShift(shiftCode); } lastEmployee = emp; Application.DoEvents(); internalCounter++; } ds.Tables.Add(dt); shiftDs.Tables.Add(shiftDt); CreateExcelFile.CreateExcelDocument(ds, fileName); CreateExcelFile.CreateExcelDocument(shiftDs, shiftsFileName); OnProgress(100); myConnection.Close(); myCommand.Dispose(); myConnection.Close(); _bRunning = false; return $"{iCount} record(s) processed"; } catch (Exception ex) { _bRunning = false; return ex.Message + @" Row: " + iCounter; } }
public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames) { foreach (string rolename in rolenames) { if (!RoleExists(rolename)) { throw new ProviderException("Role name not found."); } } foreach (string username in usernames) { foreach (string rolename in rolenames) { if (!IsUserInRole(username, rolename)) { throw new ProviderException("User is not in role."); } } } using (FbConnection conn = new FbConnection(connectionString)) { conn.Open(); using (FbCommand cmd = new FbCommand("ROLES_DELETEUSERROLE", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@ApplicationName", FbDbType.VarChar, 100).Value = ApplicationName; FbParameter roleParameter = cmd.Parameters.Add("@Rolename", FbDbType.VarChar, 100); FbParameter userParameter = cmd.Parameters.Add("@Username", FbDbType.VarChar, 100); FbTransaction trans = conn.BeginTransaction(); cmd.Transaction = trans; try { foreach (string username in usernames) { foreach (string rolename in rolenames) { userParameter.Value = username; roleParameter.Value = rolename; cmd.ExecuteNonQuery(); } } trans.Commit(); } catch (FbException ex) { trans.Rollback(); if (WriteExceptionsToEventLog) { WriteToEventLog(ex, "RemoveUsersFromRoles"); } else { throw ex; } } } } }