/// <summary>
/// Create a SqlCeCommand instance using the global SQL CE Conection instance and associate this with a transaction
/// </summary>
/// <param name="transaction">SqlCeTransaction to be used for the SqlCeCommand</param>
public static System.Data.SqlServerCe.SqlCeCommand CreateCommand(System.Data.SqlServerCe.SqlCeTransaction transaction)
{
var command = Connection.CreateCommand();
command.Transaction = transaction;
return(command);
}
/// <summary>
/// Starts a SqlCeTransaction using the global SQL CE Conection instance
/// </summary>
public System.Data.SqlServerCe.SqlCeTransaction BeginTransaction()
{
if (transaction != null)
throw new System.InvalidOperationException("A transaction has already been started. Only one transaction is allowed");
transaction = EntityBase.Connection.BeginTransaction();
EnglishSentences.Transaction = transaction;
return transaction;
}
/// <summary>
/// Starts a SqlCeTransaction using the global SQL CE Conection instance
/// </summary>
public System.Data.SqlServerCe.SqlCeTransaction BeginTransaction()
{
if (transaction != null)
{
throw new System.InvalidOperationException("A transaction has already been started. Only one transaction is allowed");
}
transaction = EntityBase.Connection.BeginTransaction();
EnglishSentences.Transaction = transaction;
return(transaction);
}
static public void SafeTransRollback(System.Data.SqlServerCe.SqlCeTransaction trans)
{
try
{
trans.Rollback();
}
catch (System.InvalidOperationException)
{
}
}
/// <summary>
/// Starts a SqlCeTransaction using the global SQL CE Conection instance
/// </summary>
public System.Data.SqlServerCe.SqlCeTransaction BeginTransaction()
{
if (transaction != null)
{
throw new System.InvalidOperationException("A transaction has already been started. Only one transaction is allowed");
}
transaction = EntityBase.Connection.BeginTransaction();
Contact.Transaction = transaction;
Customer.Transaction = transaction;
Product.Transaction = transaction;
Stock.Transaction = transaction;
return(transaction);
}
protected void Dispose(bool disposing)
{
if (disposed)
{
return;
}
if (disposing)
{
if (transaction != null)
{
transaction.Dispose();
transaction = null;
}
}
disposed = true;
}
static public int GetLastInsertId(System.Data.SqlServerCe.SqlCeTransaction trans)
{
System.Data.SqlServerCe.SqlCeCommand sqlQuery = CreateQuery();
sqlQuery.CommandText = "SELECT @@IDENTITY";
if (trans != null)
{
sqlQuery.Transaction = trans;
}
object o = ExecuteScalar(sqlQuery);
if (o == null || o == DBNull.Value)
{
return(-1);
}
else
{
return((int)(decimal)o);
}
}
protected void Dispose(bool disposing)
{
if (disposed) return;
if (disposing)
{
if (transaction != null)
{
transaction.Dispose();
transaction = null;
}
}
disposed = true;
}
bool CloneGame(int gameId)
{
System.Data.SqlServerCe.SqlCeTransaction trans = null;
System.Data.SqlServerCe.SqlCeCommand sqlQuery = null;
System.Data.SqlServerCe.SqlCeDataReader sqlReader = null;
System.Data.SqlServerCe.SqlCeDataReader sqlReader2 = null;
try
{
int comm_maxlen = DB.DB_GetMaxLength("Games", "Comment");
// Склонировать (используя транзакцию)
sqlQuery = DB.CreateQuery();
trans = DB.sqlConnection.BeginTransaction();
sqlQuery.Transaction = trans;
sqlQuery.CommandText = "INSERT INTO Games(Type, GameOptions, DealsInMatch, FirstDealer, ZoneSwims, fk_Folder_id, fk_N, fk_S, fk_E, fk_W, Place, Comment, StartDate) (SELECT Type, GameOptions, DealsInMatch, FirstDealer, ZoneSwims, fk_Folder_id, fk_N, fk_S, fk_E, fk_W, Place, (CASE WHEN Comment is NULL THEN '{клон}' ELSE (SUBSTRING(Comment, 1, (" + comm_maxlen + " - LEN(' {клон}'))) + ' {клон}') END) as Comment_clon, StartDate FROM Games WHERE id=" + gameId + ")";
if (sqlQuery.ExecuteNonQuery() == 0)
{
trans.Rollback();
MessageBox.Show("Игра #" + gameId + " не была склонирована!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return(false);
}
int new_gameId = DB.GetLastInsertId(trans);
sqlQuery.CommandText = "SELECT id FROM Matches WHERE fk_Game_id=" + gameId + " ORDER BY id";
sqlReader = sqlQuery.ExecuteReader();
while (sqlReader.Read())
{
int cur_matchId = sqlReader.GetInt32(0);
sqlQuery.CommandText = "INSERT INTO Matches(fk_Game_id, SCORE_NS, SCORE_EW) (SELECT " + new_gameId + " as fk_Game_id__new, SCORE_NS, SCORE_EW FROM Matches WHERE id=" + cur_matchId + ")";
if (sqlQuery.ExecuteNonQuery() == 0)
{
sqlReader.Close();
trans.Rollback();
MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return(false);
}
int new_cur_matchId = DB.GetLastInsertId(trans);
/****int added = 0;
* sqlQuery.CommandText = "INSERT INTO Deals_Rob(fk_Match_id, Pair, Contract, Oners, Result, CardsDistribution) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Oners, Result, CardsDistribution FROM Deals_Rob WHERE fk_Match_id=" + cur_matchId + ")";
* added = sqlQuery.ExecuteNonQuery();
*
* sqlQuery.CommandText = "INSERT INTO Deals_Sport(fk_Match_id, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair FROM Deals_Sport WHERE fk_Match_id=" + cur_matchId + ")";
* added = sqlQuery.ExecuteNonQuery();
*
* sqlQuery.CommandText = "INSERT INTO Deals_Double(fk_Match_id, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted) (SELECT " + new_cur_matchId + " as fk_Match_id__new, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted FROM Deals_Double WHERE fk_Match_id=" + cur_matchId + ")";
* added = sqlQuery.ExecuteNonQuery();*****/
int cur_dealId = -1;
sqlQuery.CommandText = "SELECT id FROM Deals_Rob WHERE fk_Match_id=" + cur_matchId + " ORDER BY id";
sqlReader2 = sqlQuery.ExecuteReader();
while (sqlReader2.Read())
{
cur_dealId = (int)sqlReader2.GetSqlInt32(0);
sqlQuery.CommandText = "INSERT INTO Deals_Rob(fk_Match_id, Pair, Contract, Oners, Result, CardsDistribution) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Oners, Result, CardsDistribution FROM Deals_Rob WHERE id=" + cur_dealId + ")";
if (sqlQuery.ExecuteNonQuery() == 0)
{
sqlReader2.Close();
sqlReader.Close();
trans.Rollback();
MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования сдачи #" + cur_dealId + " матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return(false);
}
}
sqlReader2.Close();
sqlQuery.CommandText = "SELECT id FROM Deals_Sport WHERE fk_Match_id=" + cur_matchId + " ORDER BY id";
sqlReader2 = sqlQuery.ExecuteReader();
while (sqlReader2.Read())
{
cur_dealId = (int)sqlReader2.GetSqlInt32(0);
sqlQuery.CommandText = "INSERT INTO Deals_Sport(fk_Match_id, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair FROM Deals_Sport WHERE id=" + cur_dealId + ")";
if (sqlQuery.ExecuteNonQuery() == 0)
{
sqlReader2.Close();
sqlReader.Close();
trans.Rollback();
MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования сдачи #" + cur_dealId + " матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return(false);
}
}
sqlReader2.Close();
sqlQuery.CommandText = "SELECT id FROM Deals_Double WHERE fk_Match_id=" + cur_matchId + " ORDER BY id";
sqlReader2 = sqlQuery.ExecuteReader();
while (sqlReader2.Read())
{
cur_dealId = (int)sqlReader2.GetSqlInt32(0);
sqlQuery.CommandText = "INSERT INTO Deals_Double(fk_Match_id, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted) (SELECT " + new_cur_matchId + " as fk_Match_id__new, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted FROM Deals_Double WHERE id=" + cur_dealId + ")";
if (sqlQuery.ExecuteNonQuery() == 0)
{
sqlReader2.Close();
sqlReader.Close();
trans.Rollback();
MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования сдачи #" + cur_dealId + " матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return(false);
}
}
sqlReader2.Close();
}
sqlReader.Close();
// Добавить в список
sqlQuery.CommandText = "SELECT g.id, g.StartDate, g.Place, g.Comment, g.Type, g.DealsInMatch, g.GameOptions, n.Name as N, s.Name as S, e.Name as E, w.Name as W FROM Games g LEFT JOIN Players n ON g.fk_N = n.id LEFT JOIN Players s ON g.fk_S = s.id LEFT JOIN Players e ON g.fk_E = e.id LEFT JOIN Players w ON g.fk_W = w.id WHERE g.id=" + new_gameId;
sqlReader = sqlQuery.ExecuteReader();
if (sqlReader.Read())
{
Load1Game(null, sqlReader);
sqlReader.Close();
trans.Commit();
LoadFoldersToCombo(true); // перезагрузить список папок
MessageBox.Show("Игра #" + gameId + " успешно склонирована в #" + new_gameId, "Клонирование успешно", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
return(true);
}
else
{
sqlReader.Close();
trans.Rollback();
MessageBox.Show("Игра #" + gameId + " не была склонирована в #" + new_gameId, "Клонирование неудачно", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return(false);
}
}
catch (System.Data.SqlServerCe.SqlCeException ex)
{
if (trans != null)
{
DB.SafeTransRollback(trans);
}
if (sqlReader != null && sqlReader.IsClosed == false)
{
sqlReader.Close();
}
if (sqlReader2 != null && sqlReader2.IsClosed == false)
{
sqlReader2.Close();
}
if (ex.NativeError == 0)
{
return(false);
}
else
{
throw;
}
}
}
//-------------------------------------------------------- create/modify db 'bridge' ---------------------------------
/*static public bool CreateBridgeDB()
* {
* System.Data.SqlServerCe.SqlCeCommand sqlQuery = CreateQuery();
*
* sqlQuery.CommandText = "CREATE DATABASE Bridge";
* try
* {
* sqlQuery.ExecuteNonQuery();
* return true;
* }
* catch (System.Data.SqlServerCe.SqlCeException e)
* {
* // error #25114 - База уже есть
* MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
* return false;
* }
* }*/
static public bool CreateTables(bool use_trans)
{
System.Data.SqlServerCe.SqlCeCommand sqlQuery = CreateQuery();
System.Data.SqlServerCe.SqlCeTransaction trans = null;
if (use_trans)
{
trans = sqlConnection.BeginTransaction();
sqlQuery.Transaction = trans;
}
sqlQuery.CommandText = "CREATE TABLE Folders (id INT IDENTITY PRIMARY KEY, Name NVARCHAR(30))";
try
{
sqlQuery.ExecuteNonQuery();
}
catch (System.Data.SqlServerCe.SqlCeException e)
{
MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
}
/*sqlQuery.CommandText = "alter table folders alter column Name nvarchar(30)";
* sqlQuery.ExecuteNonQuery();
* sqlQuery.CommandText = "alter table players alter column Name nvarchar(50)";
* sqlQuery.ExecuteNonQuery();*/
sqlQuery.CommandText = "CREATE TABLE Players (id INT IDENTITY PRIMARY KEY, Name NVARCHAR(50))";
try
{
sqlQuery.ExecuteNonQuery();
}
catch (System.Data.SqlServerCe.SqlCeException e)
{
MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
}
sqlQuery.CommandText = "CREATE TABLE Games (id INT IDENTITY PRIMARY KEY, Type TINYINT NOT NULL, GameOptions TINYINT, DealsInMatch TINYINT, FirstDealer TINYINT, ZoneSwims BIT, fk_Folder_id INT, CONSTRAINT FK__Games__no1__Folders__id FOREIGN KEY(fk_Folder_id) REFERENCES Folders(id), fk_N INT, CONSTRAINT FK__Games__N__Players__id FOREIGN KEY(fk_N) REFERENCES Players(id), fk_S INT, CONSTRAINT FK__Games__S__Players__id FOREIGN KEY(fk_S) REFERENCES Players(id), fk_E INT, CONSTRAINT FK__Games__E__Players__id FOREIGN KEY(fk_E) REFERENCES Players(id), fk_W INT, CONSTRAINT FK__Games__W__Players__id FOREIGN KEY(fk_W) REFERENCES Players(id), Place NVARCHAR(30), Comment NVARCHAR(60), StartDate DATETIME)";
try
{
sqlQuery.ExecuteNonQuery();
}
catch (System.Data.SqlServerCe.SqlCeException e)
{
MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
}
sqlQuery.CommandText = "CREATE TABLE Matches (id INT IDENTITY PRIMARY KEY, fk_Game_id INT, CONSTRAINT FK__Matches__no1__Games__id FOREIGN KEY(fk_Game_id) REFERENCES Games(id) ON UPDATE CASCADE ON DELETE CASCADE, SCORE_NS INT, SCORE_EW INT)";
try
{
sqlQuery.ExecuteNonQuery();
}
catch (System.Data.SqlServerCe.SqlCeException e)
{
MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
}
sqlQuery.CommandText = "CREATE TABLE Deals_Rob (id INT IDENTITY PRIMARY KEY, fk_Match_id INT, CONSTRAINT FK__Deals_Rob__no1__Matches__id FOREIGN KEY(fk_Match_id) REFERENCES Matches(id) ON UPDATE CASCADE ON DELETE CASCADE, Pair BIT, Contract TINYINT, Oners TINYINT, Result TINYINT, CardsDistribution BINARY(20))";
try
{
sqlQuery.ExecuteNonQuery();
}
catch (System.Data.SqlServerCe.SqlCeException e)
{
MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
}
sqlQuery.CommandText = "CREATE TABLE Deals_Sport (id INT IDENTITY PRIMARY KEY, fk_Match_id INT, CONSTRAINT FK__Deals_Sport__no1__Matches__id FOREIGN KEY(fk_Match_id) REFERENCES Matches(id) ON UPDATE CASCADE ON DELETE CASCADE, Pair BIT, Contract TINYINT, Result TINYINT, CardsDistribution BINARY(20), Figures TINYINT, Fits TINYINT, StrongestPair BIT)";
try
{
sqlQuery.ExecuteNonQuery();
}
catch (System.Data.SqlServerCe.SqlCeException e)
{
MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
}
sqlQuery.CommandText = "CREATE TABLE Deals_Double (id INT IDENTITY PRIMARY KEY, fk_Match_id INT, CONSTRAINT FK__Deals_Double__no1__Matches__id FOREIGN KEY(fk_Match_id) REFERENCES Matches(id) ON UPDATE CASCADE ON DELETE CASCADE, CardsDistribution BINARY(20), Pair1 BIT, Contract1 TINYINT, Result1 TINYINT, Pair2 BIT, Contract2 TINYINT, Result2 TINYINT, IsSecondStarted BIT)";
try
{
sqlQuery.ExecuteNonQuery();
}
catch (System.Data.SqlServerCe.SqlCeException e)
{
MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
}
if (use_trans)
{
try
{
trans.Commit(System.Data.SqlServerCe.CommitMode.Immediate);
return(true);
}
catch (System.Data.SqlServerCe.SqlCeException e)
{
SafeTransRollback(trans);
MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return(false);
}
}
else
{
return(true);
}
}
static private int ExecuteNonQuery(System.Data.SqlServerCe.SqlCeCommand command, bool useTransaction, out int out__rowid, bool findoutLastInsertId)
{
bool retry = false;
bool trans_started = false;
System.Data.SqlServerCe.SqlCeTransaction trans = null;
try
{
if (useTransaction)
{
trans = command.Connection.BeginTransaction();
trans_started = true;
command.Transaction = trans;
}
int rows_aff = command.ExecuteNonQuery();
int row_id = -1;
if (findoutLastInsertId)
{
if (useTransaction)
{
row_id = GetLastInsertId(trans);
}
else
{
row_id = GetLastInsertId();
}
}
if (useTransaction)
{
trans.Commit(System.Data.SqlServerCe.CommitMode.Immediate);
trans_started = false;
command.Transaction = null;
}
out__rowid = row_id;
return(rows_aff);
}
catch (System.Data.SqlServerCe.SqlCeException ex)
{
////////MessageBox.Show(ex.Message + "\n" + "HRES = " + ex.HResult + "\n" + "ERRNO = " + ex.NativeError);
if (useTransaction && trans_started)
{
SafeTransRollback(trans);
trans.Dispose();
trans = null;
command.Transaction = null;
trans_started = false;
}
if (ex.NativeError == 0)
{
retry = true;
}
else
{
throw;
}
}
if (retry)
{
command.Connection.Close();
command.Connection.Open();
if (useTransaction)
{
trans = command.Connection.BeginTransaction();
trans_started = true;
command.Transaction = trans;
}
int rows_aff = command.ExecuteNonQuery();
int row_id = -1;
if (findoutLastInsertId)
{
if (useTransaction)
{
row_id = GetLastInsertId(trans);
}
else
{
row_id = GetLastInsertId();
}
}
if (useTransaction)
{
trans.Commit(System.Data.SqlServerCe.CommitMode.Immediate);
trans_started = false;
command.Transaction = null;
}
out__rowid = row_id;
return(rows_aff);
}
else
{
out__rowid = -1;
return(0);
}
}
