// ----------- Функции -------------
public static int DB_GetMaxLength(string table, string column)
{
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "select DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + table + "' and COLUMN_NAME='" + column + "'";
System.Data.SqlServerCe.SqlCeDataReader sqlReader = ExecuteReader(sqlQuery);
int len = 0;
if (sqlReader.Read())
{
len = (int)sqlReader.GetSqlInt32(sqlReader.GetOrdinal("CHARACTER_MAXIMUM_LENGTH"));
}
sqlReader.Close();
return(len);
}
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;
}
}
}