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 bool PutImageToCache(byte[] tile, int type, GPoint pos, int zoom) { bool ret = true; { if (Initialize()) { try { lock (cmdInsert) { cmdInsert.Parameters["@x"].Value = pos.X; cmdInsert.Parameters["@y"].Value = pos.Y; cmdInsert.Parameters["@zoom"].Value = zoom; cmdInsert.Parameters["@type"].Value = type; cmdInsert.Parameters["@tile"].Value = tile; cmdInsert.ExecuteNonQuery(); } } catch (Exception ex) { ret = false; Dispose(); } } } return(ret); }
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 bool PutImageToCache(MemoryStream tile, MapType type, Point pos, int zoom) { bool ret = true; { if (Initialize()) { try { lock (cmdInsert) { cmdInsert.Parameters["@x"].Value = pos.X; cmdInsert.Parameters["@y"].Value = pos.Y; cmdInsert.Parameters["@zoom"].Value = zoom; cmdInsert.Parameters["@type"].Value = (int)type; cmdInsert.Parameters["@tile"].Value = tile.GetBuffer(); cmdInsert.ExecuteNonQuery(); } } catch (Exception ex) { Debug.WriteLine(ex.ToString()); ret = false; Dispose(); } } } return(ret); }
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 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; }
/// <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; }
/// <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); }
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 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(); */ }
//-------------------------------------------------------- create/modify db 'bridge' --------------------------------- /*static public bool CreateBridgeDB() * { * System.Data.SqlServerCe.SqlCeCommand sqlQuery = CreateQuery(); * * sqlQuery.CommandText = "CREATE DATABASE Bridge"; * try * { * sqlQuery.ExecuteNonQuery(); * return true; * } * catch (System.Data.SqlServerCe.SqlCeException e) * { * // error #25114 - База уже есть * MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); * return false; * } * }*/ static public bool CreateTables(bool use_trans) { System.Data.SqlServerCe.SqlCeCommand sqlQuery = CreateQuery(); System.Data.SqlServerCe.SqlCeTransaction trans = null; if (use_trans) { trans = sqlConnection.BeginTransaction(); sqlQuery.Transaction = trans; } sqlQuery.CommandText = "CREATE TABLE Folders (id INT IDENTITY PRIMARY KEY, Name NVARCHAR(30))"; try { sqlQuery.ExecuteNonQuery(); } catch (System.Data.SqlServerCe.SqlCeException e) { MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); } /*sqlQuery.CommandText = "alter table folders alter column Name nvarchar(30)"; * sqlQuery.ExecuteNonQuery(); * sqlQuery.CommandText = "alter table players alter column Name nvarchar(50)"; * sqlQuery.ExecuteNonQuery();*/ sqlQuery.CommandText = "CREATE TABLE Players (id INT IDENTITY PRIMARY KEY, Name NVARCHAR(50))"; try { sqlQuery.ExecuteNonQuery(); } catch (System.Data.SqlServerCe.SqlCeException e) { MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); } sqlQuery.CommandText = "CREATE TABLE Games (id INT IDENTITY PRIMARY KEY, Type TINYINT NOT NULL, GameOptions TINYINT, DealsInMatch TINYINT, FirstDealer TINYINT, ZoneSwims BIT, fk_Folder_id INT, CONSTRAINT FK__Games__no1__Folders__id FOREIGN KEY(fk_Folder_id) REFERENCES Folders(id), fk_N INT, CONSTRAINT FK__Games__N__Players__id FOREIGN KEY(fk_N) REFERENCES Players(id), fk_S INT, CONSTRAINT FK__Games__S__Players__id FOREIGN KEY(fk_S) REFERENCES Players(id), fk_E INT, CONSTRAINT FK__Games__E__Players__id FOREIGN KEY(fk_E) REFERENCES Players(id), fk_W INT, CONSTRAINT FK__Games__W__Players__id FOREIGN KEY(fk_W) REFERENCES Players(id), Place NVARCHAR(30), Comment NVARCHAR(60), StartDate DATETIME)"; try { sqlQuery.ExecuteNonQuery(); } catch (System.Data.SqlServerCe.SqlCeException e) { MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); } sqlQuery.CommandText = "CREATE TABLE Matches (id INT IDENTITY PRIMARY KEY, fk_Game_id INT, CONSTRAINT FK__Matches__no1__Games__id FOREIGN KEY(fk_Game_id) REFERENCES Games(id) ON UPDATE CASCADE ON DELETE CASCADE, SCORE_NS INT, SCORE_EW INT)"; try { sqlQuery.ExecuteNonQuery(); } catch (System.Data.SqlServerCe.SqlCeException e) { MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); } sqlQuery.CommandText = "CREATE TABLE Deals_Rob (id INT IDENTITY PRIMARY KEY, fk_Match_id INT, CONSTRAINT FK__Deals_Rob__no1__Matches__id FOREIGN KEY(fk_Match_id) REFERENCES Matches(id) ON UPDATE CASCADE ON DELETE CASCADE, Pair BIT, Contract TINYINT, Oners TINYINT, Result TINYINT, CardsDistribution BINARY(20))"; try { sqlQuery.ExecuteNonQuery(); } catch (System.Data.SqlServerCe.SqlCeException e) { MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); } sqlQuery.CommandText = "CREATE TABLE Deals_Sport (id INT IDENTITY PRIMARY KEY, fk_Match_id INT, CONSTRAINT FK__Deals_Sport__no1__Matches__id FOREIGN KEY(fk_Match_id) REFERENCES Matches(id) ON UPDATE CASCADE ON DELETE CASCADE, Pair BIT, Contract TINYINT, Result TINYINT, CardsDistribution BINARY(20), Figures TINYINT, Fits TINYINT, StrongestPair BIT)"; try { sqlQuery.ExecuteNonQuery(); } catch (System.Data.SqlServerCe.SqlCeException e) { MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); } sqlQuery.CommandText = "CREATE TABLE Deals_Double (id INT IDENTITY PRIMARY KEY, fk_Match_id INT, CONSTRAINT FK__Deals_Double__no1__Matches__id FOREIGN KEY(fk_Match_id) REFERENCES Matches(id) ON UPDATE CASCADE ON DELETE CASCADE, CardsDistribution BINARY(20), Pair1 BIT, Contract1 TINYINT, Result1 TINYINT, Pair2 BIT, Contract2 TINYINT, Result2 TINYINT, IsSecondStarted BIT)"; try { sqlQuery.ExecuteNonQuery(); } catch (System.Data.SqlServerCe.SqlCeException e) { MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); } if (use_trans) { try { trans.Commit(System.Data.SqlServerCe.CommitMode.Immediate); return(true); } catch (System.Data.SqlServerCe.SqlCeException e) { SafeTransRollback(trans); MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return(false); } } else { return(true); } }
static private int ExecuteNonQuery(System.Data.SqlServerCe.SqlCeCommand command, bool useTransaction, out int out__rowid, bool findoutLastInsertId) { bool retry = false; bool trans_started = false; System.Data.SqlServerCe.SqlCeTransaction trans = null; try { if (useTransaction) { trans = command.Connection.BeginTransaction(); trans_started = true; command.Transaction = trans; } int rows_aff = command.ExecuteNonQuery(); int row_id = -1; if (findoutLastInsertId) { if (useTransaction) { row_id = GetLastInsertId(trans); } else { row_id = GetLastInsertId(); } } if (useTransaction) { trans.Commit(System.Data.SqlServerCe.CommitMode.Immediate); trans_started = false; command.Transaction = null; } out__rowid = row_id; return(rows_aff); } catch (System.Data.SqlServerCe.SqlCeException ex) { ////////MessageBox.Show(ex.Message + "\n" + "HRES = " + ex.HResult + "\n" + "ERRNO = " + ex.NativeError); if (useTransaction && trans_started) { SafeTransRollback(trans); trans.Dispose(); trans = null; command.Transaction = null; trans_started = false; } if (ex.NativeError == 0) { retry = true; } else { throw; } } if (retry) { command.Connection.Close(); command.Connection.Open(); if (useTransaction) { trans = command.Connection.BeginTransaction(); trans_started = true; command.Transaction = trans; } int rows_aff = command.ExecuteNonQuery(); int row_id = -1; if (findoutLastInsertId) { if (useTransaction) { row_id = GetLastInsertId(trans); } else { row_id = GetLastInsertId(); } } if (useTransaction) { trans.Commit(System.Data.SqlServerCe.CommitMode.Immediate); trans_started = false; command.Transaction = null; } out__rowid = row_id; return(rows_aff); } else { out__rowid = -1; return(0); } }
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(); }