static public System.Data.SqlServerCe.SqlCeDataReader ExecuteReader(System.Data.SqlServerCe.SqlCeCommand command)
{
bool retry = false;
try
{
return(command.ExecuteReader());
}
catch (System.Data.SqlServerCe.SqlCeException ex)
{
if (ex.NativeError == 0)
{
retry = true;
}
else
{
throw;
}
}
if (retry)
{
command.Connection.Close();
command.Connection.Open();
return(command.ExecuteReader());
}
else
{
return(null);
}
}
private void ReadEncryptedMountains()
{
System.Data.SqlServerCe.SqlCeConnection connection
= new System.Data.SqlServerCe.SqlCeConnection(string.Format("Data Source = {0}; max database size = 4091", sdfFileLocation.Text));
System.Data.SqlServerCe.SqlCeCommand command =
new System.Data.SqlServerCe.SqlCeCommand();
command.Connection = connection;
command.CommandText = "SELECT * FROM IRIMountain1";
connection.Open();
System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
watch.Start();
var reader = command.ExecuteReader();
List <MountainStructure> results = new List <MountainStructure>();
List <Microsoft.SqlServer.Types.SqlGeometry> geometries = new List <Microsoft.SqlServer.Types.SqlGeometry>();
while (reader.Read())
{
int id = (int)reader[0];
byte[] geometry = (byte[])reader[1];
MountainStructure temp = IRI.Ket.IO.BinaryStream.ByteArrayToStructure <MountainStructure>((byte[])reader[2]);
geometries.Add(Microsoft.SqlServer.Types.SqlGeometry.STGeomFromWKB(
new System.Data.SqlTypes.SqlBytes(geometry), 0));
results.Add(temp);
}
connection.Close();
watch.Stop();
var t = watch.ElapsedMilliseconds;
}
static void Main()
{
using (System.Data.SqlServerCe.SqlCeConnection conn =
new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ProductsConnectionString))
{
conn.Open();
using (System.Data.SqlServerCe.SqlCeCommand cmd =
new System.Data.SqlServerCe.SqlCeCommand("select * from productsbintbl", conn))
{
using (System.Data.SqlServerCe.SqlCeCommand cmdUpd =
new System.Data.SqlServerCe.SqlCeCommand(@"UPDATE ProductsBinTbl SET ActionCode = @ac
, Shablon = @sc
, SoundCode = @sndc
WHERE (ProductsBinTbl.Barcode = @b)", conn))
{
cmdUpd.Parameters.Add("@ac", typeof(byte));
cmdUpd.Parameters.Add("@sc", typeof(int));
cmdUpd.Parameters.Add("@sndc", typeof(int));
cmdUpd.Parameters.Add("@b", typeof(Int64));
System.Random r = new Random();
Array vals = Enum.GetValues(typeof(TSDUtils.ActionCode));
Array vals1 = Enum.GetValues(typeof(TSDUtils.ShablonCode));
using (System.Data.SqlServerCe.SqlCeDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Int64 bc = (Int64)rdr[0];
cmdUpd.Parameters[3].Value = bc;
byte c = 0;
for (int k=0;k<4;k++)
{
int b = 0;
Double d = Math.Round(r.NextDouble());
b = (byte)((byte)vals.GetValue(k) * ((byte)d));
c = (byte)(b|c);
}
uint sum = 0;
for (byte k = 0; k < 8; k++)
{
byte d1 = (byte)Math.Round(r.NextDouble() * 8);
byte b1 = (byte)(1 << k);//Math.Pow(2, k);
byte b = (byte)(c & b1);
if (b != 0)
{
uint b2 = (uint)(d1 << (3 * k));
sum += b2;
}
/*Double d = Math.Round(r.NextDouble());
b = (byte)((byte)vals1.GetValue(k) * ((byte)d));*/
//c = (byte)(b*Math.Pow( | c);
}
cmdUpd.Parameters[0].Value = c;
cmdUpd.Parameters[1].Value = sum;
cmdUpd.Parameters[2].Value = sum;
cmdUpd.ExecuteNonQuery();
}
}
}
}
}
return;
//TSDUtils.ActionCode a = TSDUtils.ActionCode.Remove | TSDUtils.ActionCode.Reprice;
//TSDUtils.ActionCode b = TSDUtils.ActionCode.Remove | TSDUtils.ActionCode.Returns;
//TSDUtils.ActionCode c = TSDUtils.ActionCode.Remove | TSDUtils.ActionCode.Returns | TSDUtils.ActionCode.Reprice;
/*Array e = Enum.GetValues(typeof(TSDUtils.ActionCode));
int counter = 0;
byte[] bArray = new byte[e.Length];
string s = string.Empty;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
foreach (TSDUtils.ActionCode i in e)
{
byte b = (byte)i;
bArray[counter++] = b;
sb.AppendFormat("{0} = {1} \n", i, b);
}
TSDUtils.ActionCode tmp = TSDUtils.ActionCode.NoAction;
for (int i = 0; i < bArray.Length; i++)
{
tmp = TSDUtils.ActionCode.NoAction;
for (int j = 0; j < bArray.Length; j++)
{
if (bArray[i] == bArray[j])
continue;
tmp = tmp |(TSDUtils.ActionCode) bArray[j];
byte b = (byte)tmp;
sb.AppendFormat("{0} = {1} \n", tmp, b);
}
}
*/
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
mainForm = new Form1();
//проверка на наличие второй запущеной копии программы
//если Null значит уже запущена другая копия
if (mainForm.mutex != null)
{
//другая копия программы не запущена
//инициализируем IPC сервер, который может принимать сообщения
//(в данном случае нужно для получения сообщения от второй копии показать главное окно
IpcChannel serverChannel =
new IpcChannel("localhost:9090");
System.Runtime.Remoting.Channels.ChannelServices.RegisterChannel(
serverChannel,false);
System.Runtime.Remoting.WellKnownServiceTypeEntry WKSTE =
new System.Runtime.Remoting.WellKnownServiceTypeEntry(
typeof(RemoteObject), "RemoteObject.rem", System.Runtime.Remoting.WellKnownObjectMode.Singleton);
System.Runtime.Remoting.RemotingConfiguration.RegisterWellKnownServiceType(WKSTE);
Application.Run(mainForm);//запуск главного экранного потока
}
else
{
//есть уже запущенная копия программы
IpcChannel channel = new IpcChannel();
System.Runtime.Remoting.Channels.ChannelServices.RegisterChannel(channel,false);
//получаем адрес сервера программы
RemoteObject service = (RemoteObject)Activator.GetObject(
typeof(RemoteObject), "ipc://localhost:9090/RemoteObject.rem");
//отправляем сообщение показать главное окно
service.Show();
//mainForm.Activate();
//выходим из программы
}
}
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;
}
}
}
static public void ModifyTable()
{
System.Data.SqlServerCe.SqlCeCommand sqlQuery = CreateQuery();
System.Data.SqlServerCe.SqlCeDataReader dr;
//--------- total score
sqlQuery.CommandText = "ALTER TABLE Matches ADD 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);
}
//----------- on delete/update для констраинтов матчей и сдач
sqlQuery.CommandText = "select CONSTRAINT_NAME from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where (CONSTRAINT_TABLE_NAME = 'Matches' AND UNIQUE_CONSTRAINT_TABLE_NAME = 'Games')";
object o = sqlQuery.ExecuteScalar();
if (o != null && o != DBNull.Value)
{
sqlQuery.CommandText = "ALTER TABLE Matches DROP CONSTRAINT " + o;
sqlQuery.ExecuteNonQuery();
sqlQuery.CommandText = "ALTER TABLE Matches ADD FOREIGN KEY(fk_Game_id) REFERENCES Games(id) ON UPDATE CASCADE ON DELETE CASCADE";
sqlQuery.ExecuteNonQuery();
}
sqlQuery.CommandText = "select CONSTRAINT_NAME from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where (CONSTRAINT_TABLE_NAME = 'Deals_Rob' AND UNIQUE_CONSTRAINT_TABLE_NAME = 'Matches')";
o = sqlQuery.ExecuteScalar();
if (o != null && o != DBNull.Value)
{
sqlQuery.CommandText = "ALTER TABLE Deals_Rob DROP CONSTRAINT " + o;
sqlQuery.ExecuteNonQuery();
sqlQuery.CommandText = "ALTER TABLE Deals_Rob ADD FOREIGN KEY(fk_Match_id) REFERENCES Matches(id) ON UPDATE CASCADE ON DELETE CASCADE";
sqlQuery.ExecuteNonQuery();
}
sqlQuery.CommandText = "select CONSTRAINT_NAME from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where (CONSTRAINT_TABLE_NAME = 'Deals_Sport' AND UNIQUE_CONSTRAINT_TABLE_NAME = 'Matches')";
o = sqlQuery.ExecuteScalar();
if (o != null && o != DBNull.Value)
{
sqlQuery.CommandText = "ALTER TABLE Deals_Sport DROP CONSTRAINT " + o;
sqlQuery.ExecuteNonQuery();
sqlQuery.CommandText = "ALTER TABLE Deals_Sport ADD FOREIGN KEY(fk_Match_id) REFERENCES Matches(id) ON UPDATE CASCADE ON DELETE CASCADE";
sqlQuery.ExecuteNonQuery();
}
sqlQuery.CommandText = "select CONSTRAINT_NAME from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where (CONSTRAINT_TABLE_NAME = 'Deals_Double' AND UNIQUE_CONSTRAINT_TABLE_NAME = 'Matches')";
o = sqlQuery.ExecuteScalar();
if (o != null && o != DBNull.Value)
{
sqlQuery.CommandText = "ALTER TABLE Deals_Double DROP CONSTRAINT " + o;
sqlQuery.ExecuteNonQuery();
sqlQuery.CommandText = "ALTER TABLE Deals_Double ADD FOREIGN KEY(fk_Match_id) REFERENCES Matches(id) ON UPDATE CASCADE ON DELETE CASCADE";
sqlQuery.ExecuteNonQuery();
}
//------------- удалить констраинты из Games
sqlQuery.CommandText = "select CONSTRAINT_NAME from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where (CONSTRAINT_TABLE_NAME = 'Games' AND UNIQUE_CONSTRAINT_TABLE_NAME = 'Folders')";
dr = sqlQuery.ExecuteReader();
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
sqlQuery.CommandText = "ALTER TABLE Games DROP CONSTRAINT " + dr.GetString(0);
sqlQuery.ExecuteNonQuery();
}
}
dr.Close();
//----------- folder
sqlQuery.CommandText = "ALTER TABLE Games ADD fk_Folder_id 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 = "ALTER TABLE Games ADD FOREIGN KEY(fk_Folder_id) REFERENCES Folders(id)";
sqlQuery.ExecuteNonQuery();
//----------- n s e w
sqlQuery.CommandText = "ALTER TABLE Games ADD fk_N INT, fk_S INT, fk_E INT, fk_W 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 = "ALTER TABLE Games ADD FOREIGN KEY(fk_N) REFERENCES Players(id), FOREIGN KEY(fk_S) REFERENCES Players(id), FOREIGN KEY(fk_E) REFERENCES Players(id), FOREIGN KEY(fk_W) REFERENCES Players(id)";
sqlQuery.ExecuteNonQuery();
//---------- place & comment & sdate
/*sqlQuery.CommandText = "ALTER TABLE Games DROP COLUMN Place";
* sqlQuery.ExecuteNonQuery();
* sqlQuery.CommandText = "ALTER TABLE Games DROP COLUMN Comment";
* sqlQuery.ExecuteNonQuery();
* sqlQuery.CommandText = "ALTER TABLE Games DROP COLUMN StartDate";
* sqlQuery.ExecuteNonQuery();*/
sqlQuery.CommandText = "ALTER TABLE Games ADD 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);
}
//--------- cd
if (MessageBox.Show("Изменить формат для распределения колоды?\nЭто удалит все сущ. колоды!", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.Yes)
{
sqlQuery.CommandText = "ALTER TABLE Deals_Sport DROP COLUMN CardsDistribution";
sqlQuery.ExecuteNonQuery();
sqlQuery.CommandText = "ALTER TABLE Deals_Sport ADD CardsDistribution BINARY(20)";
sqlQuery.ExecuteNonQuery();
}
/*sqlQuery.CommandText = "insert into folders (Name) values('тест')";
* sqlQuery.ExecuteNonQuery();
* sqlQuery.CommandText = "insert into folders (Name) values('ааа')";
* sqlQuery.ExecuteNonQuery();
*/
/*** справка по constaints ***
* sqlQuery.CommandText = "select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS";
* dr = sqlQuery.ExecuteReader();
* while (dr.Read())
* {
* //dr.Read();
* string s = "";
* for (int i = 0; i < dr.FieldCount; i++)
* s += i + ">" + dr.GetName(i) + " ^ " + dr.GetValue(i).ToString() + "\n"; //MessageBox.Show(dr.GetName(i));
* MessageBox.Show(s);
* }
* dr.Close();
*/
}
