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