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