public void CleanProducts()
{
using (System.Data.SqlServerCe.SqlCeConnection conn =
new System.Data.SqlServerCe.SqlCeConnection(
Program.Default.ProductsConnectionString))
{
conn.Open();
using (System.Data.SqlServerCe.SqlCeCommand cmd
= new System.Data.SqlServerCe.SqlCeCommand())
{
cmd.Connection = conn;
cmd.CommandText = "delete from productsBinTbl";
cmd.ExecuteNonQuery();
cmd.CommandText = "delete from productsTbl";
cmd.ExecuteNonQuery();
}
this.ProductsTbl.Clear();
this.ProductsBinTbl.Clear();
this.AcceptChanges();
}
using (System.Data.SqlServerCe.SqlCeEngine engine
= new System.Data.SqlServerCe.SqlCeEngine(Program.Default.ProductsConnectionString))
{
engine.Shrink();
}
}
public void Dispose()
{
lock (cmdInsert)
{
if (cmdInsert != null)
{
cmdInsert.Dispose();
cmdInsert = null;
}
if (cnSet != null)
{
cnSet.Dispose();
cnSet = null;
}
}
lock (cmdFetch)
{
if (cmdFetch != null)
{
cmdFetch.Dispose();
cmdFetch = null;
}
if (cnGet != null)
{
cnGet.Dispose();
cnGet = null;
}
}
Initialized = false;
}
static public object ExecuteScalar(System.Data.SqlServerCe.SqlCeCommand command)
{
bool retry = false;
try
{
return(command.ExecuteScalar());
}
catch (System.Data.SqlServerCe.SqlCeException ex)
{
if (ex.NativeError == 0)
{
retry = true;
}
else
{
throw;
}
}
if (retry)
{
command.Connection.Close();
command.Connection.Open();
return(command.ExecuteScalar());
}
else
{
return(null);
}
}
public void CopyTestWithDb()
{
string sourceFileName = @"c:\tmp\System.IO.Transactions.TxF\TestFiles\TestFile.source";
string destFileName = @"c:\tmp\System.IO.Transactions.TxF\TestFiles\TestFile.dest";
System.Data.SqlServerCe.SqlCeConnection cnDb = new System.Data.SqlServerCe.SqlCeConnection(@"Data Source=C:\tmp\System.IO.Transactions.TxF\System.IO.Transactions.TxF.Test\DatabaseTest.sdf");
System.Data.SqlServerCe.SqlCeCommand cmDb = new System.Data.SqlServerCe.SqlCeCommand("INSERT INTO TestTbl (Col_1) VALUES (GETDATE());", cnDb);
bool overwrite = true;
using (cnDb)
{
cnDb.Open();
using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope())
{
try
{
File.Copy(sourceFileName, destFileName, overwrite);
cmDb.ExecuteNonQuery();
ts.Complete();
}
catch (Exception)
{
throw;
}
finally
{ }
}
}
}
public void Dispose()
{
lock(cmdInsert)
{
if(cmdInsert != null)
{
cmdInsert.Dispose();
cmdInsert = null;
}
if(cnSet != null)
{
cnSet.Dispose();
cnSet = null;
}
}
lock(cmdFetch)
{
if(cmdFetch != null)
{
cmdFetch.Dispose();
cmdFetch = null;
}
if(cnGet != null)
{
cnGet.Dispose();
cnGet = null;
}
}
Initialized = false;
}
bool DeleteGame(int gameId)
{
// Удалить из БД
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "DELETE FROM Games WHERE id=" + gameId;
while (DB.ExecuteNonQuery(sqlQuery, true) == 0)
{
if (MessageBox.Show("Игра " + gameId + " не была удалена!", "db error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2) == DialogResult.Retry)
{
DB.sqlConnection.Close();
DB.sqlConnection.Open();
}
else
{
return(false);
}
}
// Убрать из списка
m_listview_Games.Items.RemoveAt(m_listview_Games.SelectedIndices[0]);
// Перезагрузить список папок
LoadFoldersToCombo(true);
return(true);
}
// Очистить:
private void m_btn4_Click(object sender, EventArgs e)
{
if (m_combo1.SelectedIndex == -1)
{
MessageBox.Show("Папка не выбрана!");
return;
}
int id = ((comboitem_id_name)m_combo1.Items[m_combo1.SelectedIndex]).GetId();
if (MessageBox.Show("Очистить папку [" + GetFolderName(id) + "]?", "Уверены?", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) != DialogResult.Yes)
{
return;
}
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "DELETE FROM Games WHERE fk_Folder_id" + (id == -1 ? " IS NULL" : "=" + id);
int rows_affected = DB.ExecuteNonQuery(sqlQuery, true);
// ОБНОВИТЬ КОМБЫ
LoadFoldersToCombo(m_combo1);
LoadFoldersToCombo(m_combo2);
MessageBox.Show("Папка [" + GetFolderName(id) + "] очищена!\nБыло удалено " + rows_affected + " игр.");
return;
}
public static int DB_GetAttributeId(int selected_id, string selected_name, string table, string column_id, string column_name)
{
if (selected_id != -1)
{
return(selected_id);
}
else
{
if (selected_name.Length == 0)
{
return(-1); //т.е. NULL
}
else
{
string selected_name__cut = DB_GetCuttedString(selected_name, table, column_name);
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT " + column_id + " FROM " + table + " WHERE " + column_name + "='" + selected_name__cut + "'";
object o = ExecuteScalar(sqlQuery);
if (o != null && o != DBNull.Value)
{
return((int)o);
}
else
{
sqlQuery.CommandText = "INSERT INTO " + table + "(" + column_name + ") VALUES('" + selected_name__cut + "')";
int inserted_id;
ExecuteNonQuery(sqlQuery, true, out inserted_id);
return(inserted_id);
}
}
}
}
// Выбрана папка:
private void m_combo1_SelectedIndexChanged(object sender, EventArgs e)
{
// Заполнить название в поле "Переименовать"
if (m_combo1.SelectedIndex == -1)
{
SELECTOR_FOLDER__is_enabled = false;
m_textbox2.Text = "";
}
else
{
int id = ((comboitem_id_name)m_combo1.Items[m_combo1.SelectedIndex]).GetId();
SELECTOR_FOLDER = id;
SELECTOR_FOLDER__is_enabled = true;
if (id == -1)
{
m_textbox2.Text = ""; // т.к. NULL
}
else
{
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT Name FROM Folders WHERE id=" + id;
object o = DB.ExecuteScalar(sqlQuery);
if (o == null || o == DBNull.Value)
{
m_textbox2.Text = "?";
}
else
{
m_textbox2.Text = ((string)o).Trim();
}
}
}
}
// Переименовать папку:
private void m_btn3_Click(object sender, EventArgs e)
{
if (m_combo1.SelectedIndex == -1)
{
MessageBox.Show("Папка не выбрана!");
return;
}
string name = m_textbox2.Text.Trim();
int id = ((comboitem_id_name)m_combo1.Items[m_combo1.SelectedIndex]).GetId();
if (name.Length == 0)
{
MessageBox.Show("Название не введено!");
return;
}
else if (id == -1) //NULL
{
MessageBox.Show("Выберите другую папку!");
return;
}
else
{
string name__cut = DB.DB_GetCuttedString(name, "Folders", "Name");
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT id FROM Folders WHERE Name='" + name__cut + "'";
object o = DB.ExecuteScalar(sqlQuery);
if (o != null && o != DBNull.Value)
{
MessageBox.Show("Папка с таким именем уже есть!");
return;
}
else
{
sqlQuery.CommandText = "UPDATE Folders SET Name='" + name__cut + "' WHERE id=" + id;
while (DB.ExecuteNonQuery(sqlQuery, true) == 0)
{
if (MessageBox.Show("Папка не была переименована!", "db error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2) == DialogResult.Retry)
{
DB.sqlConnection.Close();
DB.sqlConnection.Open();
}
else
{
return;
}
}
// ОБНОВИТЬ КОМБЫ
LoadFoldersToCombo(m_combo1);
LoadFoldersToCombo(m_combo2);
MessageBox.Show("Папка переименована в [" + GetFolderName(id) + "]!");
return;
}
}
}
// Создание новой игры
private void menuItem1_Click(object sender, EventArgs e)
{
//brandnew!
GameOptionsForm_Common form = new GameOptionsForm_Common(false, -1);
if (form.ShowDialog() == DialogResult.OK && form.done2steps == true)
{
// Конечные id папки и игроков (-1 для NULL, если нет то создать)
int folder_id = DB.DB_GetAttributeId(form.Folder_Id, form.Folder_Name, "Folders", "id", "Name");
int n_id = DB.DB_GetAttributeId(form.N_Id, form.N_Name, "Players", "id", "Name");
int s_id = DB.DB_GetAttributeId(form.S_Id, form.S_Name, "Players", "id", "Name");
int e_id = DB.DB_GetAttributeId(form.E_Id, form.E_Name, "Players", "id", "Name");
int w_id = DB.DB_GetAttributeId(form.W_Id, form.W_Name, "Players", "id", "Name");
// Обрезанные строки места и комментария
string place__cut = DB.DB_GetCuttedString(form.Place, "Games", "Place");
string comment__cut = DB.DB_GetCuttedString(form.Comment, "Games", "Comment");
// Создать новую игру:
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "INSERT INTO Games(Type, GameOptions, DealsInMatch, FirstDealer, ZoneSwims, fk_Folder_id, fk_N, fk_S, fk_E, fk_W, Place, Comment, StartDate) VALUES(@type, @options, @dealscount, @firstdealer, @zoneswims, @folder, @n, @s, @e, @w, @place, @comment, GETDATE())";
sqlQuery.Parameters.Add("type", form.GameTip);
sqlQuery.Parameters.Add("options", form.GameOptions);
sqlQuery.Parameters.Add("dealscount", form.DealsCount);
sqlQuery.Parameters.Add("zoneswims", form.ZoneSwims);
sqlQuery.Parameters.Add("firstdealer", form.FirstDealer);
sqlQuery.Parameters.Add("folder", (folder_id != -1 ? (object)folder_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("n", (n_id != -1 ? (object)n_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("s", (s_id != -1 ? (object)s_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("e", (e_id != -1 ? (object)e_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("w", (w_id != -1 ? (object)w_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("place", (place__cut.Length > 0 ? (object)place__cut : (object)DBNull.Value));
sqlQuery.Parameters.Add("comment", (comment__cut.Length > 0 ? (object)comment__cut : (object)DBNull.Value));
sqlQuery.Prepare();
int new_game_id;
while (DB.ExecuteNonQuery(sqlQuery, true, out new_game_id) == 0)
{
if (MessageBox.Show("Новая игра не была добавлена!", "db error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2) == DialogResult.Retry)
{
DB.sqlConnection.Close();
DB.sqlConnection.Open();
}
else
{
return;
}
}
// Записать id новой игры:
this.m_selected_GameId = new_game_id;
this.DialogResult = DialogResult.OK;
}
}
// ----------- Функции -------------
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);
}
// Добавить папку:
private void m_btn1_Click(object sender, EventArgs e)
{
string name = m_textbox1.Text.Trim();
if (name.Length == 0)
{
MessageBox.Show("Название не введено!");
return;
}
else
{
string name__cut = DB.DB_GetCuttedString(name, "Folders", "Name");
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT id FROM Folders WHERE Name='" + name__cut + "'";
object o = DB.ExecuteScalar(sqlQuery);
if (o != null && o != DBNull.Value)
{
MessageBox.Show("Папка с таким именем уже есть!");
return;
}
else
{
sqlQuery.CommandText = "INSERT INTO Folders(Name) VALUES('" + name__cut + "')";
int new_folder_id;
while (DB.ExecuteNonQuery(sqlQuery, true, out new_folder_id) == 0)
{
if (MessageBox.Show("Папка [" + name__cut + "] не была добавлена!", "db error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2) == DialogResult.Retry)
{
DB.sqlConnection.Close();
DB.sqlConnection.Open();
}
else
{
return;
}
}
// ОБНОВИТЬ КОМБЫ
LoadFoldersToCombo(m_combo1);
LoadFoldersToCombo(m_combo2);
MessageBox.Show("Папка [" + GetFolderName(new_folder_id) + "] добавлена!");
return;
}
}
}
// Удалить:
private void m_btn5_Click(object sender, EventArgs e)
{
if (m_combo1.SelectedIndex == -1)
{
MessageBox.Show("Папка не выбрана!");
return;
}
int id = ((comboitem_id_name)m_combo1.Items[m_combo1.SelectedIndex]).GetId();
string name = GetFolderName(id);
if (id == -1)
{
MessageBox.Show("Папку [NULL] нельзя удалить!");
return;
}
if (MessageBox.Show("Удалить папку [" + name + "]?", "Уверены?", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) != DialogResult.Yes)
{
return;
}
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "DELETE FROM Games WHERE fk_Folder_id=" + id;
int rows_affected = DB.ExecuteNonQuery(sqlQuery, true);
sqlQuery.CommandText = "DELETE FROM Folders WHERE id=" + id;
int rows_affected_f = DB.ExecuteNonQuery(sqlQuery, true);
if (rows_affected_f == 0)
{
MessageBox.Show("Папка [" + name + "] не была удалена!\nИз нее удалено " + rows_affected + " игр.", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return;
}
else
{
// ОБНОВИТЬ КОМБЫ
LoadFoldersToCombo(m_combo1);
LoadFoldersToCombo(m_combo2);
MessageBox.Show("Папка [" + name + "] удалена!\nБыло удалено " + rows_affected + " игр.");
return;
}
}
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);
}
}
//Загрузка списка игр
void LoadGamesToList()
{
// Сначала очистить:
m_listview_Games.Items.Clear();
// Если папка не выбрана, не показывать ничего!
if (!SELECTOR_FOLDER__is_enabled)
{
return;
}
// Теперь загрузить:
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
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";
if (SELECTOR_FOLDER__is_enabled)
{
if (SELECTOR_FOLDER == -1)
{
sqlQuery.CommandText += " WHERE g.fk_Folder_id IS NULL";
}
else
{
sqlQuery.CommandText += (" WHERE g.fk_Folder_id = " + SELECTOR_FOLDER);
}
}
sqlQuery.CommandText += " ORDER BY g.StartDate DESC, g.id ASC";
System.Data.SqlServerCe.SqlCeDataReader sqlReader = DB.ExecuteReader(sqlQuery);
while (sqlReader.Read())
{
Load1Game(null, sqlReader);
}
sqlReader.Close();
// Подогнать ширину столбцов
this.m_listview_Games.Columns[0].Width = -1; //Комментарий
this.m_listview_Games.Columns[1].Width = -1; //Дата
this.m_listview_Games.Columns[2].Width = -1; //Тип
this.m_listview_Games.Columns[3].Width = -1; //Опции
this.m_listview_Games.Columns[4].Width = -1; //Место
this.m_listview_Games.Columns[5].Width = -1; //Игроки
}
public void BulkCopy(string SqlCommand, BulkCopyHandler bulkCopy)
{
DalLayerInfo dalInfo = GetDalyerInfo();
DbConnection conntion = dalInfo.Connection;
if (!dalInfo.LongConnection)
{
conntion.Open();
}
using (System.Data.SqlServerCe.SqlCeCommand comm = (System.Data.SqlServerCe.SqlCeCommand)conntion.CreateCommand())
{
comm.CommandText = SqlCommand;
comm.CommandType = CommandType.Text;
System.Data.SqlServerCe.SqlCeResultSet ceRsSet = comm.ExecuteResultSet(System.Data.SqlServerCe.ResultSetOptions.Scrollable | System.Data.SqlServerCe.ResultSetOptions.Updatable);
try
{
System.Data.SqlServerCe.SqlCeUpdatableRecord upRs = ceRsSet.CreateRecord();
if (bulkCopy != null)
{
bulkCopy(ceRsSet, upRs);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
ceRsSet.Close();
}
}
if (!dalInfo.LongConnection)
{
if (conntion.State != ConnectionState.Closed)
{
conntion.Close();
}
conntion.Dispose();
}
}
//-----------------------------------------
// Имя папки?
string GetFolderName(int id)
{
if (id == -1)
{
return("NULL");
}
else
{
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT Name FROM Folders WHERE id=" + id;
object o = DB.ExecuteScalar(sqlQuery);
if (o == null || o == DBNull.Value)
{
return("?");
}
else
{
return(((string)o).Trim());
}
}
}
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;
}
// Переместить игры в другую папку:
private void m_btn2_Click(object sender, EventArgs e)
{
if (m_combo1.SelectedIndex == -1)
{
MessageBox.Show("Начальная папка не выбрана!");
return;
}
if (m_combo2.SelectedIndex == -1)
{
MessageBox.Show("Конечная папка не выбрана!");
return;
}
int id1 = ((comboitem_id_name)m_combo1.Items[m_combo1.SelectedIndex]).GetId();
int id2 = ((comboitem_id_name)m_combo2.Items[m_combo2.SelectedIndex]).GetId();
if (id1 == id2)
{
MessageBox.Show("Начальная и конечная папки совпадают!");
return;
}
if (MessageBox.Show("Переместить игры из [" + GetFolderName(id1) + "] в [" + GetFolderName(id2) + "]?", "Уверены?", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) != DialogResult.Yes)
{
return;
}
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "UPDATE Games SET fk_Folder_id = " + (id2 == -1 ? "NULL" : id2.ToString()) + " WHERE fk_Folder_id" + (id1 == -1 ? " IS NULL" : "=" + id1);
int rows_affected = DB.ExecuteNonQuery(sqlQuery, true);
// ОБНОВИТЬ КОМБЫ
LoadFoldersToCombo(m_combo1);
LoadFoldersToCombo(m_combo2);
MessageBox.Show("Перемещено " + rows_affected + " игр из [" + GetFolderName(id1) + "] в [" + GetFolderName(id2) + "]!");
return;
}
/// <summary>
/// inits connection to server
/// </summary>
/// <returns></returns>
public bool Initialize()
{
if (!Initialized)
{
#region prepare mssql & cache table
try
{
// precrete dir
if (!Directory.Exists(gtileCache))
{
Directory.CreateDirectory(gtileCache);
}
string connectionString = string.Format("Data Source={0}Data.sdf", gtileCache);
if (!File.Exists(gtileCache + "Data.sdf"))
{
using (System.Data.SqlServerCe.SqlCeEngine engine = new System.Data.SqlServerCe.SqlCeEngine(connectionString))
{
engine.CreateDatabase();
}
try
{
using (SqlConnection c = new SqlConnection(connectionString))
{
c.Open();
using (SqlCommand cmd = new SqlCommand(
"CREATE TABLE [GMapNETcache] ( \n"
+ " [Type] [int] NOT NULL, \n"
+ " [Zoom] [int] NOT NULL, \n"
+ " [X] [int] NOT NULL, \n"
+ " [Y] [int] NOT NULL, \n"
+ " [Tile] [image] NOT NULL, \n"
+ " CONSTRAINT [PK_GMapNETcache] PRIMARY KEY (Type, Zoom, X, Y) \n"
+ ")", c))
{
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
try
{
File.Delete(gtileCache + "Data.sdf");
}
catch
{
}
throw ex;
}
}
// different connections so the multi-thread inserts and selects don't collide on open readers.
this.cnGet = new SqlConnection(connectionString);
this.cnGet.Open();
this.cnSet = new SqlConnection(connectionString);
this.cnSet.Open();
this.cmdFetch = new SqlCommand("SELECT [Tile] FROM [GMapNETcache] WITH (NOLOCK) WHERE [X]=@x AND [Y]=@y AND [Zoom]=@zoom AND [Type]=@type", cnGet);
this.cmdFetch.Parameters.Add("@x", System.Data.SqlDbType.Int);
this.cmdFetch.Parameters.Add("@y", System.Data.SqlDbType.Int);
this.cmdFetch.Parameters.Add("@zoom", System.Data.SqlDbType.Int);
this.cmdFetch.Parameters.Add("@type", System.Data.SqlDbType.Int);
this.cmdFetch.Prepare();
this.cmdInsert = new SqlCommand("INSERT INTO [GMapNETcache] ( [X], [Y], [Zoom], [Type], [Tile] ) VALUES ( @x, @y, @zoom, @type, @tile )", cnSet);
this.cmdInsert.Parameters.Add("@x", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@y", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@zoom", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@type", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@tile", System.Data.SqlDbType.Image); //, calcmaximgsize);
//can't prepare insert because of the IMAGE field having a variable size. Could set it to some 'maximum' size?
Initialized = true;
}
catch (Exception ex)
{
Initialized = false;
}
#endregion
}
return(Initialized);
}
// **** контролы ****
//m_textbox1 добавить
//m_textbox2 переименовать
//m_combo1 source
//m_combo2 destination
//----------------------------------------------------------------------------------------------------
//Загрузка комбы папок
void LoadFoldersToCombo(ComboBox combo)
{
bool is_from = (combo == m_combo1);
bool is_to = (combo == m_combo2);
// Сохранить SELECTOR_FOLDER...
int saved__SELECTOR_FOLDER = SELECTOR_FOLDER;
int saved__SELECTOR_FOLDER2 = SELECTOR_FOLDER2;
bool saved__SELECTOR_FOLDER__is_enabled = SELECTOR_FOLDER__is_enabled;
bool saved__SELECTOR_FOLDER2__is_enabled = SELECTOR_FOLDER2__is_enabled;
// Сначала очистить:
combo.Items.Clear();
combo.SelectedIndex = -1;
// Теперь загрузить:
//1. NULL
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT COUNT(id) FROM Games WHERE fk_Folder_id is NULL";
object o = DB.ExecuteScalar(sqlQuery);
int fid = -1;
string fname = "- нет папки -";
if (o != null && o != DBNull.Value)
{
fname += " (" + (int)o + ")";
}
int index = combo.Items.Add(new comboitem_id_name(fid, fname));
// Выбрать:
if (is_from && saved__SELECTOR_FOLDER__is_enabled && saved__SELECTOR_FOLDER == fid || is_to && saved__SELECTOR_FOLDER2__is_enabled && saved__SELECTOR_FOLDER2 == fid)
{
combo.SelectedIndex = index;
}
// 2. остальные
sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT f.id as [fid], f.Name as [fname], COUNT(g.id) as [fcount] FROM Folders f LEFT JOIN Games g ON f.id=g.fk_Folder_id GROUP BY f.id, f.Name ORDER BY [fname]";
System.Data.SqlServerCe.SqlCeDataReader sqlReader = DB.ExecuteReader(sqlQuery);
while (sqlReader.Read())
{
fid = sqlReader.IsDBNull(sqlReader.GetOrdinal("fid")) ? -1 : sqlReader.GetInt32(sqlReader.GetOrdinal("fid"));
fname = "";
if (fid == -1)
{
fname = "- нет папки -";
}
if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("fname")))
{
fname = sqlReader.GetString(sqlReader.GetOrdinal("fname"));
}
fname += " (" + (sqlReader.IsDBNull(sqlReader.GetOrdinal("fcount")) ? 0 : sqlReader.GetInt32(sqlReader.GetOrdinal("fcount"))) + ")";
index = combo.Items.Add(new comboitem_id_name(fid, fname));
// Выбрать:
if (is_from && saved__SELECTOR_FOLDER__is_enabled && saved__SELECTOR_FOLDER == fid || is_to && saved__SELECTOR_FOLDER2__is_enabled && saved__SELECTOR_FOLDER2 == fid)
{
combo.SelectedIndex = index;
}
}
sqlReader.Close();
}
static public int ExecuteNonQuery(System.Data.SqlServerCe.SqlCeCommand command, bool useTransaction, out int out__rowid)
{
return(ExecuteNonQuery(command, useTransaction, out out__rowid, true));
}
//-------------------------------------------------------- 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 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();
*/
}
//Загрузка комбы папок
void LoadFoldersToCombo(bool reload)
{
// Сначала очистить:
m_combo_Folders.Items.Clear();
// Теперь загрузить:
//1. NULL
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT COUNT(id) FROM Games WHERE fk_Folder_id is NULL";
object o = DB.ExecuteScalar(sqlQuery);
int fid = -1;
string fname = "- нет папки -";
if (o != null && o != DBNull.Value)
{
fname += " (" + (int)o + ")";
}
int index = m_combo_Folders.Items.Add(new comboitem_id_name(fid, fname));
// Выбрать:
if (SELECTOR_FOLDER__is_enabled && SELECTOR_FOLDER == fid)
{
if (reload)
{
dont_react_on_folder_selection = true;
}
m_combo_Folders.SelectedIndex = index;
if (reload)
{
dont_react_on_folder_selection = false;
}
}
// 2. остальные
sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "SELECT f.id as [fid], f.Name as [fname], COUNT(g.id) as [fcount] FROM Folders f LEFT JOIN Games g ON f.id=g.fk_Folder_id GROUP BY f.id, f.Name ORDER BY [fname]";
System.Data.SqlServerCe.SqlCeDataReader sqlReader = DB.ExecuteReader(sqlQuery);
while (sqlReader.Read())
{
fid = sqlReader.IsDBNull(sqlReader.GetOrdinal("fid")) ? -1 : sqlReader.GetInt32(sqlReader.GetOrdinal("fid"));
fname = "";
if (fid == -1)
{
fname = "- нет папки -";
}
if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("fname")))
{
fname = sqlReader.GetString(sqlReader.GetOrdinal("fname"));
}
fname += " (" + (sqlReader.IsDBNull(sqlReader.GetOrdinal("fcount")) ? 0 : sqlReader.GetInt32(sqlReader.GetOrdinal("fcount"))) + ")";
index = m_combo_Folders.Items.Add(new comboitem_id_name(fid, fname));
// Выбрать:
if (SELECTOR_FOLDER__is_enabled && SELECTOR_FOLDER == fid)
{
if (reload)
{
dont_react_on_folder_selection = true;
}
m_combo_Folders.SelectedIndex = index;
if (reload)
{
dont_react_on_folder_selection = false;
}
}
}
sqlReader.Close();
}
// Редактирование игры
void ContextMenu_OnClickEdit(object sender, EventArgs e)
{
ListViewItem lvi = m_listview_Games.Items[m_listview_Games.SelectedIndices[0]];
int gameId = (int)lvi.Tag;
// Открыть окно редактирования
GameOptionsForm_Common form = new GameOptionsForm_Common(true, gameId);
if (form.ShowDialog() == DialogResult.OK && form.done2steps == true)
{
// Конечные id папки и игроков (-1 для NULL, если нет то создать)
int folder_id = DB.DB_GetAttributeId(form.Folder_Id, form.Folder_Name, "Folders", "id", "Name");
int n_id = DB.DB_GetAttributeId(form.N_Id, form.N_Name, "Players", "id", "Name");
int s_id = DB.DB_GetAttributeId(form.S_Id, form.S_Name, "Players", "id", "Name");
int e_id = DB.DB_GetAttributeId(form.E_Id, form.E_Name, "Players", "id", "Name");
int w_id = DB.DB_GetAttributeId(form.W_Id, form.W_Name, "Players", "id", "Name");
// Обрезанные строки места и комментария
string place__cut = DB.DB_GetCuttedString(form.Place, "Games", "Place");
string comment__cut = DB.DB_GetCuttedString(form.Comment, "Games", "Comment");
// Редактировать в БД
System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery();
sqlQuery.CommandText = "UPDATE Games SET fk_Folder_id = @folder, fk_N = @n, fk_S = @s, fk_E = @e, fk_W = @w, Place = @place, Comment = @comment WHERE id=" + gameId;
sqlQuery.Parameters.Add("folder", (folder_id != -1 ? (object)folder_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("n", (n_id != -1 ? (object)n_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("s", (s_id != -1 ? (object)s_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("e", (e_id != -1 ? (object)e_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("w", (w_id != -1 ? (object)w_id : (object)DBNull.Value));
sqlQuery.Parameters.Add("place", (place__cut.Length > 0 ? (object)place__cut : (object)DBNull.Value));
sqlQuery.Parameters.Add("comment", (comment__cut.Length > 0 ? (object)comment__cut : (object)DBNull.Value));
sqlQuery.Prepare();
while (DB.ExecuteNonQuery(sqlQuery, true) == 0)
{
if (MessageBox.Show("Игра " + gameId + " не была изменена!", "db error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2) == DialogResult.Retry)
{
DB.sqlConnection.Close();
DB.sqlConnection.Open();
}
else
{
return;
}
}
// Если изменилась папка, убрать из списка; иначе обновить запись об игре в списке
if (SELECTOR_FOLDER__is_enabled && folder_id != SELECTOR_FOLDER)
{
// Убрать из списка
m_listview_Games.Items.RemoveAt(m_listview_Games.SelectedIndices[0]);
// Перезагрузить список папок
LoadFoldersToCombo(true);
}
else
{
// Обновить запись об игре в списке
sqlQuery = DB.CreateQuery();
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=" + gameId;
System.Data.SqlServerCe.SqlCeDataReader sqlReader = DB.ExecuteReader(sqlQuery);
if (sqlReader.Read())
{
Load1Game(lvi, sqlReader);
}
sqlReader.Close();
}
}
}
private void insertReferencia(char tipo, string nome, int x, int y)
{
System.Data.SqlServerCe.SqlCeCommand insert =
new System.Data.SqlServerCe.SqlCeCommand("insert into referencias (nome, chave_fk, cx, cy, cidade, tipo) " +
"values ('" + nome + "', '" + Usuario.Chave + "'," + x + "," + y + ",'" + Usuario.Cidade + "','" + tipo + "')", Conn.sqlConn);
try
{
insert.ExecuteNonQuery();
Usuario.Referencia = new Referencia(nome, Usuario.Chave, x, y, Usuario.Cidade, tipo);
this.Close();
}
catch (Exception ex)
{
if (ex.Message.Contains("PK"))
{
MessageBox.Show("Não foi possível inserir referência por que o nome já existe");
}
else
{
MessageBox.Show("Não foi possível inserir referência.");
}
}
}
private void m_TransferButton_Click(object sender, EventArgs e)
{
//get Bacnet selection
if (m_list.SelectedItems.Count <= 0)
{
MessageBox.Show(this, "Please select a device", "No device selected", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
KeyValuePair <BacnetAddress, uint> device = (KeyValuePair <BacnetAddress, uint>)m_list.SelectedItems[0].Tag;
//open database connection
System.Data.SqlServerCe.SqlCeConnection con = new System.Data.SqlServerCe.SqlCeConnection(@"Data Source=..\..\SampleDatabase.sdf");
con.Open();
//retrieve list of 'properties'
IList <BacnetValue> value_list;
bacnet_client.ReadPropertyRequest(device.Key, new BacnetObjectId(BacnetObjectTypes.OBJECT_DEVICE, device.Value), BacnetPropertyIds.PROP_OBJECT_LIST, out value_list);
LinkedList <BacnetObjectId> object_list = new LinkedList <BacnetObjectId>();
foreach (BacnetValue value in value_list)
{
if (Enum.IsDefined(typeof(BacnetObjectTypes), ((BacnetObjectId)value.Value).Type))
{
object_list.AddLast((BacnetObjectId)value.Value);
}
}
//go through all 'properties' and store their 'present data' into a SQL database
foreach (BacnetObjectId object_id in object_list)
{
//read all properties
IList <BacnetValue> values = null;
try
{
if (!bacnet_client.ReadPropertyRequest(device.Key, object_id, BacnetPropertyIds.PROP_PRESENT_VALUE, out values))
{
MessageBox.Show(this, "Couldn't fetch 'present value' for object: " + object_id.ToString());
continue;
}
}
catch (Exception)
{
//perhaps the 'present value' is non existing - ignore
continue;
}
//store in DB
using (System.Data.SqlServerCe.SqlCeCommand com = new System.Data.SqlServerCe.SqlCeCommand("INSERT INTO SampleTable VALUES(@ObjectName,@PropertyId,@Value)", con))
{
com.Parameters.AddWithValue("@ObjectName", object_id.ToString());
com.Parameters.AddWithValue("@PropertyId", values[0].Tag.ToString());
com.Parameters.AddWithValue("@Value", values[0].Value.ToString());
com.ExecuteNonQuery();
}
}
//close DB
con.Close();
//done
MessageBox.Show(this, "Done!", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
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 int ExecuteNonQuery(System.Data.SqlServerCe.SqlCeCommand command, bool useTransaction)
{
int fake;
return(ExecuteNonQuery(command, useTransaction, out fake, false));
}
private Boolean inserirPonto()
{
if (VerificaPonto() != null)
{
MessageBox.Show("Impossível salvar. Encontrado problemas nos campos:\n" + VerificaPonto(), "",
MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
//CadPontoPanel.BringToFront();
//CadPontoPanel.Focus();
return false;
}
else
{
int mult = Convert.ToInt32(quantTextBox.Text.ToString().Trim());
int cxTemp = 0, cyTemp = 0;
string sequencia = seqTextBox.Text;
string utilizadores = utiliTextBox.Text;
bool derivado = false;
/*
* se a sequência já estiver cadastrada o utilizador deve estar em branco pois
* trata-se de uma derivação
*/
if (PontosList.ContainsKey(sequencia))
{
cxTemp = 0;
cyTemp = 0;
utilizadores = "";
derivado = true;
}
else
{
cxTemp = cx;
cyTemp = cy;
}
while (mult > 0)
{
//string lat = txtLat.Text;
//string lon = txtLon.Text;
//int prefeitura = 0;
//if (ckPrefeitura.Checked) {
// prefeitura = 1;
//}
String dH = "";
if (gpsTime != null && (Usuario.UsandoTrimble || Usuario.ColetarSSF))
{
dH = gpsTime.ToString();
}
else if (dataHora != null && Usuario.UsandoHolux)
{
dH = dataHora.ToString();
}
else
{
dH = DateTime.Now.ToString();
}
System.Data.SqlServerCe.SqlCeCommand insert =
new System.Data.SqlServerCe.SqlCeCommand("insert into pontos (cidade_fk,chave_fk,bairro,rua,casa,luminaria,utilizadores," +
"reat_id,braco,medidor,data_reg,sequencia,barramento,medido,aceso,multiplicador," +
"cx,cy,cx_,cy_,observacao,lamp_id,ligacao, cadastrador, lat, lon, gpgga, " +
"satelites, ligacao_clandestina, condicao_risco, ativacao,zona,fase,material_poste, estrutura,esforco_poste,"+
"finalidade,classe_pi,resp_rede,quadricula,id_light,id_rioluz,"+
"alimentacao,tipo_rede) values ('" + Usuario.Cidade + "'," +
"'" + Usuario.Chave + "','" + bairroTextBox.Text + "','" + ruaTextBox.Text + "','" + casaTextBox.Text + "'," +
"'" + lumComboBox.Text + "','" + utilizadores + "','" + reatComboBox.Text + "','" + cbBraco.Text + "','" + nmedTextBox.Text + "'," +
"'" + dH + "'," + seqTextBox.Text.ToString().Trim() + ",'" + barrTextBox.Text + "','" + medComboBox.Text + "'," +
"'" + acesoComboBox.Text + "'," + mult + "," + cxTemp + "," + cyTemp + ",0,0," +
"'" + obsTextBox.Text + "','" + lampComboBox.Text + "','" + cbLigClandestina.SelectedText + "','" + Usuario.Nome +
"','" + lat + "','" + lon + "','" + gpgga + "','" + quantSatelites + "','" + cbLigClandestina.Text +
"','" + lstCondRisco.Text + "','" + cbAtivacao.Text + "','" + txtNFoto.Text + "','" + comboFase.Text + "','" +
cbMaterial.Text + "','" + cbEstrutura.Text + "','" + cbEsforco.Text + "','"+cbFinalidade.Text+"','"+cbClassePI.Text+"','"+cbRespRede.Text+"','"+
txtQuadricula.Text + "','" + txtIdLight.Text + "','" + txtRioLux.Text + "','" + cbAlimentacao.Text + "','" + cbTipoRedeLight.Text + "')", Conn.sqlConn);
//System.Data.SqlServerCe.SqlCeCommand insert =
// new System.Data.SqlServerCe.SqlCeCommand("insert into pontos (cidade_fk,chave_fk,bairro,rua,casa,luminaria,utilizadores," +
// "reat_id,braco,medidor,data_reg,sequencia,barramento,medido,aceso,multiplicador," +
// "cx,cy,cx_,cy_,observacao,lamp_id,ligacao, cadastrador, lat, lon, gpgga, "+
// "satelites, ligacao_clandestina, condicao_risco, ativacao,altura_poste,esforco_poste,"+
// "material_poste,tipo_poste,proprietario_poste,zona,aterramento,estrutura,isolador,"+
// "cruzeta,afastador,chave_f,bitola,tipo_rede,trafo_kva,obj_iluminado, fase) values ('" + Usuario.Cidade + "'," +
// "'" + Usuario.Chave + "','" + bairroTextBox.Text + "','" + ruaTextBox.Text + "','" + casaTextBox.Text + "'," +
// "'" + lumComboBox.Text + "','" + utilizadores + "','" + reatComboBox.Text + "','"+cbBraco.Text+"','" + nmedTextBox.Text + "'," +
// "'" + dH + "'," + seqTextBox.Text.ToString().Trim() + ",'" + barrTextBox.Text + "','" + medComboBox.Text + "'," +
// "'" + acesoComboBox.Text + "'," + mult + "," + cxTemp + "," + cyTemp + ",0,0," +
// "'" + obsTextBox.Text + "','" + lampComboBox.Text + "','"+cbLigClandestina.SelectedText+"','" + Usuario.Nome +
// "','" + lat + "','" + lon + "','"+gpgga+"','"+quantSatelites+"','"+cbLigClandestina.Text+
// "','" + lstCondRisco.Text + "','" + cbAtivacao.Text + "','" + cbAltura.Text +
// "','" + cbEsforco.Text + "','" + cbMaterial.Text + "','" + cbTipoPoste.Text +
// "','" + cbPropriedade.Text + "','" + txtNFoto.Text + "','" + cbAterramento.Text +
// "','" + cbEstrutura.Text + "','" + cbIsolador.Text + "','" + cbCruzeta.Text +
// "','" + cbAfastador.Text + "','" + cbCHaveF.Text + "','" + cbBitola.Text +
// "','" + cbTipoRede.Text + "','" + cbTrafoKva.Text + "','" + cbObjIlum.Text +
// "','" + comboFase.Text + "')", Conn.sqlConn);
try
{
insert.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
insert =
new System.Data.SqlServerCe.SqlCeCommand("SELECT @@IDENTITY", Conn.sqlConn);
if (Usuario.UsandoTrimble || Usuario.ColetarSSF)
{
object idPonto = insert.ExecuteScalar();
if (idPonto != null && ssfWriter != null && !Usuario.ColetarSSF)
{
base.errorCode = ssfWriter.EndFeature(idPonto.ToString());
}
//Cursor.Current = Cursors.WaitCursor;
//Application.DoEvents();
//DesconectarTrimble();
//PegarTrimble trimble = new PegarTrimble(insert.ExecuteScalar());
//trimble.ShowDialog();
}
//pegar posições ssf
//if (!ckSemSatelite.Checked)
//{
// if (base.ssfWriter != null &&
// base.ssfWriter.NumberOfPositionsLoggedInFeature() < 10)
// {
// MessageBox.Show("Aguarde até coletar pelo menos 10 posições");
// return;
// }
// if (quantSatelites.Length > 0)
// {
// if (Convert.ToInt32(quantSatelites) < 4)
// {
// MessageBox.Show("Aguarde mais satelites");
// return;
// }
// }
//}
Cursor.Current = Cursors.Default;
utiliTextBox.Text = "";
utilizadores = "";
cxTemp = 0;
cyTemp = 0;
mult--;
}
Usuario.Sequencia = Convert.ToInt32(seqTextBox.Text.ToString().Trim());
if (!derivado)
{
atualizaPontosList();
}
editando = false;
//this.pontosTableAdapter.Fill(cipDatabaseDataSet.Pontos, Usuario.Cidade, Usuario.Chave, "N");
//hasPoint = cipDatabaseDataSet.Pontos.Rows.Count > 0;
hasPoint = data.GetPontos().Rows.Count > 0;
}
//throw new Exception();
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);
}
}
/// <summary>
/// inits connection to server
/// </summary>
/// <returns></returns>
public bool Initialize()
{
if(!Initialized)
{
#region prepare mssql & cache table
try
{
// precrete dir
if(!Directory.Exists(gtileCache))
{
Directory.CreateDirectory(gtileCache);
}
string connectionString = string.Format("Data Source={0}Data.sdf", gtileCache);
if(!File.Exists(gtileCache + "Data.sdf"))
{
using(System.Data.SqlServerCe.SqlCeEngine engine = new System.Data.SqlServerCe.SqlCeEngine(connectionString))
{
engine.CreateDatabase();
}
try
{
using(SqlConnection c = new SqlConnection(connectionString))
{
c.Open();
using(SqlCommand cmd = new SqlCommand(
"CREATE TABLE [GMapNETcache] ( \n"
+ " [Type] [int] NOT NULL, \n"
+ " [Zoom] [int] NOT NULL, \n"
+ " [X] [int] NOT NULL, \n"
+ " [Y] [int] NOT NULL, \n"
+ " [Tile] [image] NOT NULL, \n"
+ " CONSTRAINT [PK_GMapNETcache] PRIMARY KEY (Type, Zoom, X, Y) \n"
+ ")", c))
{
cmd.ExecuteNonQuery();
}
}
}
catch(Exception ex)
{
try
{
File.Delete(gtileCache + "Data.sdf");
}
catch
{
}
throw ex;
}
}
// different connections so the multi-thread inserts and selects don't collide on open readers.
this.cnGet = new SqlConnection(connectionString);
this.cnGet.Open();
this.cnSet = new SqlConnection(connectionString);
this.cnSet.Open();
this.cmdFetch = new SqlCommand("SELECT [Tile] FROM [GMapNETcache] WITH (NOLOCK) WHERE [X]=@x AND [Y]=@y AND [Zoom]=@zoom AND [Type]=@type", cnGet);
this.cmdFetch.Parameters.Add("@x", System.Data.SqlDbType.Int);
this.cmdFetch.Parameters.Add("@y", System.Data.SqlDbType.Int);
this.cmdFetch.Parameters.Add("@zoom", System.Data.SqlDbType.Int);
this.cmdFetch.Parameters.Add("@type", System.Data.SqlDbType.Int);
this.cmdFetch.Prepare();
this.cmdInsert = new SqlCommand("INSERT INTO [GMapNETcache] ( [X], [Y], [Zoom], [Type], [Tile] ) VALUES ( @x, @y, @zoom, @type, @tile )", cnSet);
this.cmdInsert.Parameters.Add("@x", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@y", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@zoom", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@type", System.Data.SqlDbType.Int);
this.cmdInsert.Parameters.Add("@tile", System.Data.SqlDbType.Image); //, calcmaximgsize);
//can't prepare insert because of the IMAGE field having a variable size. Could set it to some 'maximum' size?
Initialized = true;
}
catch(Exception ex)
{
Initialized = false;
Debug.WriteLine(ex.Message);
}
#endregion
}
return Initialized;
}
void BadQueries(string name, string password)
{
var command1 = new System.Data.Odbc.OdbcCommand("SELECT AccountNumber FROM Users " + // Noncompliant {{Make sure to sanitize the parameters of this SQL command.}}
// ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
"WHERE Username='******' AND Password='******'");
command1.CommandText = "SELECT AccountNumber FROM Users " + // Noncompliant
// ^^^^^^^^^^^^^^^^^^^^
"WHERE Username='******' AND Password='******'";
var command2 = new System.Data.Odbc.OdbcDataAdapter("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'", "");
var command3 = new System.Data.OleDb.OleDbCommand("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'");
command3.CommandText = "SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'";
var command4 = new System.Data.OleDb.OleDbDataAdapter("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'", "");
var command5 = new Oracle.ManagedDataAccess.Client.OracleCommand("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'");
command5.CommandText = "SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'";
var command6 = new Oracle.ManagedDataAccess.Client.OracleDataAdapter("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'", "");
var command7 = new System.Data.SqlServerCe.SqlCeCommand("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'");
command7.CommandText = "SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'";
var command8 = new System.Data.SqlServerCe.SqlCeDataAdapter("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'", "");
var command9 = new System.Data.SqlClient.SqlCommand("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'");
command9.CommandText = "SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'";
var command10 = new System.Data.SqlClient.SqlDataAdapter("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'", "");
}
private void btnSalvar_Click(object sender, EventArgs e)
{
if (Usuario.Tipo.Equals("LIGHT") && (lumComboBox.Text != "PST") && (lumComboBox.Text != "PSTA") && (lumComboBox.Text != "LC"))
{
List<string> msgs = new List<string>();
//if (cbGerenciaReg.SelectedIndex == -1)
//{
// msgs.Add("Gerencia Regional");
//}
//if (txtNProjeto.Text.Length == 0)
//{
// msgs.Add("Número do Projeto");
//}
//if (txtOficioInter.Text.Length == 0)
//{
// msgs.Add("Ofício de interligação");
//}
if (cbFinalidade.SelectedIndex == -1)
{
msgs.Add("Finalidade");
}
if (cbClassePI.SelectedIndex == -1)
{
msgs.Add("Classe PI");
}
//if (cbSuporte.SelectedIndex == -1)
//{
// msgs.Add("Suporte");
//}
//if (cbTipoSuporte.SelectedIndex == -1)
//{
// msgs.Add("Tipo Suporte");
//}
if (cbRespRede.SelectedIndex == -1)
{
msgs.Add("Responsável Rede");
}
if (cbTipoRedeLight.SelectedIndex == -1)
{
msgs.Add("Tipo Rede");
}
if (cbAtivacao.SelectedIndex == -1)
{
msgs.Add("Ativação");
}
if (cbAlimentacao.SelectedIndex == -1)
{
msgs.Add("Alimentação");
}
if (cbAtivacao.SelectedIndex == -1)
{
msgs.Add("Ativação");
}
//if (cbOutros.SelectedIndex == -1)
//{
// msgs.Add("Outros");
//}
/*
* Gerencia regional
* Numero projeto
* Oficio Interligacao
* Finalidade
* Classe PI
* Quantidade
* Tipo_PI ??
* Tipo_Lampada ??
* Quantidade Lampada
* Potencia Lampada
* Classe Suporte
* Tipo Suporte ??
* Tamanho Suporte ??
* Diametro Suporte ??
* Responsavel Rede
* Tipo Rede
* Controle
* Alimentacao
* Outros
*
*/
if (msgs.Count > 0)
{
string msg = "";
foreach (string s in msgs)
{
msg += "\n"+s;
}
MessageBox.Show("Não foi possível salvar. Faltando campos obrigatórios: " + msg);
return;
}
}
if (!Usuario.SemEstrutura && cbEstrutura.Text.Length == 0)
{
MessageBox.Show("Cadastre a Estrutura");
return;
}
if (!Usuario.SemEsforco && cbEsforco.Text.Length == 0)
{
MessageBox.Show("Cadastre o Esforço");
return;
}
if (!Usuario.SemMaterial && cbMaterial.Text.Length == 0)
{
MessageBox.Show("Cadastre o material");
return;
}
if (barrTextBox.Text.Length < Usuario.QuantMinDigitosBarramento
|| barrTextBox.Text.Length > Usuario.QuantMaxDigitosBarramento)
{
MessageBox.Show("Tamanho do barramento deve estar entre "
+ Usuario.QuantMinDigitosBarramento + " e "
+ Usuario.QuantMaxDigitosBarramento);
return;
}
if (lumComboBox.Text != "PST" && lumComboBox.Text != "PSTA" && lumComboBox.Text != "LC" && Usuario.CadastraBraco)
{
if (cbBraco.Text.Length == 0)
{
MessageBox.Show("Cadastre o braço");
return;
}
}
if (!Usuario.SemFase &&
lumComboBox.Text != "PST" && lumComboBox.Text != "PSTA" && lumComboBox.Text != "LC")
{
if (comboFase.Text.Length == 0)
{
MessageBox.Show("Cadastre a fase");
return;
}
}
//if (Usuario.UsandoTrimble && lat.Length <= 1)
//{
// MessageBox.Show("Não foi possível verificar a coordenada");
// return;
//}
//if (!chkTipoCabo.Checked)
//{
// if (comboTipoCabo.Text.Length == 0)
// {
// MessageBox.Show("Cadastre o Tipo de Cabo de Utilizador");
// return;
// }
//}
bool insere = inserirPonto();
if (insere && ligarComboBox.Text.Length > 0)
{
//ligar ponto
Ponto ponto1 = (Ponto)PontosList[seqTextBox.Text.ToString().Trim()];
Ponto ponto2 = (Ponto)PontosList[ligarComboBox.Text];
bool cruza = false;
string ligacao = "";
//verificar se a reta que liga os pontos se cruza com outra
foreach (Ponto p in PontosList.Values)
{
/*
* verifica se o ponto do loop esta ligado a outro e se não é os
* nenhum dos pontos que formam o segmento e também se ele não
* esta ligado a alguma extremidade do segmento pois assim
* a colisão será positiva
*/
if (p.X_ != 0 &&
p.Seq != ponto1.Seq &&
p.Seq != ponto2.Seq &&
p.X_ != ponto1.X &&
p.X_ != ponto2.X)
if (Library.IsLinesIntersecting(ponto1.X, ponto1.Y, ponto2.X, ponto2.Y, p.X, p.Y, p.X_, p.Y_))
{
cruza = true;
break;
}
}
if ((ponto1.X == ponto2.X_) && (ponto1.Y == ponto2.Y_))
{
MessageBox.Show("Estes pontos já estão ligados!", "", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return;
}
if (cruza)
{
if (MessageBox.Show("Foi detectada uma interligação da rede. Essa é uma interligação aérea?",
"", MessageBoxButtons.YesNo, MessageBoxIcon.Question,
MessageBoxDefaultButton.Button1) == DialogResult.Yes)
{
ligacao = "SI";
}
else
{
ligacao = "CO";
}
}
if (ponto1.X_ == 0)
{
ponto1.X_ = ponto2.X;
ponto1.Y_ = ponto2.Y;
try
{
System.Data.SqlServerCe.SqlCeCommand sql =
new System.Data.SqlServerCe.SqlCeCommand("UPDATE Pontos SET cx = " + ponto1.X + ", cy = " + ponto1.Y + ", " +
"cx_ = " + ponto1.X_ + ", cy_ = " + ponto1.Y_ + ", ligacao = '" + ligacao + "' WHERE " +
"(cidade_fk = '" + Usuario.Cidade + "') AND (chave_fk = '" + Usuario.Chave + "') AND " +
"(id = " + ponto1.Id + ")", Conn.sqlConn);
sql.ExecuteNonQuery();
data.GetPontos().AcceptChanges();
//adapter_pontos.Fill(pontos_table);
//pontosTableAdapter.UpdateCoordenadasLigacao(ponto1.X, ponto1.Y,
// ponto1.X_, ponto1.Y_, ligacao,
// Usuario.Cidade, Usuario.Chave, ponto1.Seq);
//pontosTableAdapter.Update(this.cipDatabaseDataSet.Pontos);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (ligacao == "CO")
{
//constructionLines(p1ComboBox.Text, p2ComboBox.Text, dashedGreenPen);
}
else
{
//constructionLines(p1ComboBox.Text, p2ComboBox.Text, solidBluePen);
}
//this.menuItem1.Enabled = true;
//ligarPontoPanel.Visible = false;
}
else
{
MessageBox.Show("Não foi possível ligar pois o ponto inicial " +
"já está ligado a outro");
}
}
if (insere)
{
fechaConexaoBluetooth();
this.Close();
}
}
private void button2_Click(object sender, EventArgs e)
{
if (p1ComboBox.Text != "")
{
if (p1ComboBox.Text == p2ComboBox.Text)
{
MessageBox.Show("Impossível ligar um ponto a ele mesmo", "",
MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
}
else
{
Ponto ponto1 = (Ponto)PontosList[p1ComboBox.Text];
Ponto ponto2 = (Ponto)PontosList[p2ComboBox.Text];
bool cruza = false;
string ligacao = "";
//verificar se a reta que liga os pontos se cruza com outra
foreach (Ponto p in PontosList.Values)
{
/*
* verifica se o ponto do loop esta ligado a outro e se não é os
* nenhum dos pontos que formam o segmento e também se ele não
* esta ligado a alguma extremidade do segmento pois assim
* a colisão será positiva
*/
if (p.X_ != 0 &&
p.Seq != ponto1.Seq &&
p.Seq != ponto2.Seq &&
p.X_ != ponto1.X &&
p.X_ != ponto2.X)
if (Library.IsLinesIntersecting(ponto1.X, ponto1.Y, ponto2.X, ponto2.Y, p.X, p.Y, p.X_, p.Y_))
{
cruza = true;
break;
}
}
//if ((ponto1.X == ponto2.X_) && (ponto1.Y == ponto2.Y_))
if ((ponto1.Id_ligacao == ponto2.Id))
{
MessageBox.Show("Estes pontos já estão ligados!", "", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return;
}
if (cruza)
{
if (MessageBox.Show("Foi detectada uma interligação da rede. Essa é uma interligação aérea?",
"", MessageBoxButtons.YesNo, MessageBoxIcon.Question,
MessageBoxDefaultButton.Button1) == DialogResult.Yes)
{
ligacao = "SI";
}
else
{
ligacao = "CO";
}
}
if (ponto1.X_ == 0)
{
ponto1.X_ = ponto2.X;
ponto1.Y_ = ponto2.Y;
try
{
System.Data.SqlServerCe.SqlCeCommand sql =
new System.Data.SqlServerCe.SqlCeCommand("UPDATE Pontos SET cx = " + ponto1.X + ", cy = " + ponto1.Y + ", " +
"cx_ = " + ponto1.X_ + ", cy_ = " + ponto1.Y_ + ", ligacao = '" + ligacao + "' WHERE " +
"(cidade_fk = '" + Usuario.Cidade + "') AND (chave_fk = '" + Usuario.Chave + "') AND " +
"(id = " + ponto1.Id + ")", Conn.sqlConn);
//maneira de ligar o ponto pelo id e nao por cx, cy
//new System.Data.SqlServerCe.SqlCeCommand("UPDATE Pontos SET cx = " + ponto1.X + ", cy = " + ponto1.Y + ", " +
// "id_ligacao = " + ponto1.X_ + ", cy_ = " + ponto1.Y_ + ", ligacao = '" + ligacao + "' WHERE " +
// "(cidade_fk = '" + Usuario.Cidade + "') AND (chave_fk = '" + Usuario.Chave + "') AND " +
// "(id = " + ponto1.Id + ")", Conn.sqlConn);
sql.ExecuteNonQuery();
data.GetPontos().AcceptChanges();
//adapter_pontos.Fill(pontos_table);
//pontosTableAdapter.UpdateCoordenadasLigacao(ponto1.X, ponto1.Y,
// ponto1.X_, ponto1.Y_, ligacao,
// Usuario.Cidade, Usuario.Chave, ponto1.Seq);
//pontosTableAdapter.Update(this.cipDatabaseDataSet.Pontos);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (ligacao == "CO")
{
//constructionLines(p1ComboBox.Text, p2ComboBox.Text, dashedGreenPen);
}
else
{
//constructionLines(p1ComboBox.Text, p2ComboBox.Text, solidBluePen);
}
//this.menuItem1.Enabled = true;
//ligarPontoPanel.Visible = false;
}
else
{
MessageBox.Show("Não foi possível ligar pois o ponto inicial " +
"já está ligado a outro");
}
}
//this.Refresh();
}
this.Close();
}