C# SqlCeConnection.CreateCommand方法代码示例

        private static bool assureTables()
        {
            SqlCeConnection cn = new SqlCeConnection(ConnectString());
            bool ok = true;
            if (cn.State == ConnectionState.Closed) cn.Open();

            SqlCeCommand scmd;

            try
            {
                if (!tableExists(cn, "Bydloes"))
                {
                    scmd = cn.CreateCommand();
                    scmd.CommandText = "CREATE TABLE [Bydloes] (" +
                        "[Id] int  NOT NULL," +
                        "[Imie] nvarchar(4000)  NOT NULL," +
                        "[Nazwisko] nvarchar(4000)  NOT NULL," +
                        "[pesel] nvarchar(4000)  NULL," +
                        "[dowod] nvarchar(4000)  NULL," +
                        "[nick] nvarchar(4000)  NULL," +
                        "[jest] bit  NOT NULL," +
                        "[adres] nvarchar(4000)  NULL" +
                    ");";
                    scmd.ExecuteNonQuery();
                    scmd = cn.CreateCommand();
                    scmd.CommandText = "ALTER TABLE [Bydloes] ADD CONSTRAINT [PK_Bydloes] PRIMARY KEY ([Id] );";
                }
                if (!tableExists(cn, "IdiotFriendlies"))
                {
                    scmd = cn.CreateCommand();
                    scmd.CommandText = "CREATE TABLE [IdiotFriendlies] (" +
                        "[Id] int  NOT NULL," +
                        "[Operacja] nvarchar(4000)  NOT NULL," +
                        "[poszla] bit  NOT NULL" +
                    ");";
                    scmd.ExecuteNonQuery();
                    scmd = cn.CreateCommand();
                    scmd.CommandText = "ALTER TABLE [IdiotFriendlies] ADD CONSTRAINT [PK_IdiotFriendlies] PRIMARY KEY ([Id] );";
                }
            }
            catch (SqlCeException sqlexception)
            {
                MessageBox.Show(sqlexception.Message, "Oh Crap.", MessageBoxButtons.OK, MessageBoxIcon.Error);
                ok = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Oh Shit.", MessageBoxButtons.OK, MessageBoxIcon.Error);
                ok = false;
            }
            finally
            {
                cn.Close();
            }
            return ok;
        }

        private static void ConvertAntonyms(IMeaning meaning, SqlCeConnection conn, Guid meaning_id)
        {
            using (SqlCeCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO Relations (MeaningId, Relation, Position, WordId) VALUES (@MeaningId, @Relation, @Position, @WordId)";
                cmd.Parameters.Add(new SqlCeParameter("MeaningId", meaning_id));
                cmd.Parameters.Add(new SqlCeParameter("Relation", SqlDbType.NVarChar));
                cmd.Parameters.Add(new SqlCeParameter("Position", SqlDbType.SmallInt));
                cmd.Parameters.Add(new SqlCeParameter("WordId", SqlDbType.UniqueIdentifier));
                cmd.Prepare();

                int position = 0;
                foreach (IWord word in meaning.Antonyms)
                {
                    Console.Write("synonym [{0}]...", word.Text);

                    cmd.Parameters["Relation"].Value = "A";
                    cmd.Parameters["Position"].Value = ++position;
                    cmd.Parameters["WordId"].Value = GetWordId(word, conn);
                    cmd.ExecuteNonQuery();

                    Console.WriteLine("ok");
                }
            }
        }

        public static SqlCeConnection CreateDBConnection()
        {
            if (!File.Exists(Path.Combine(Application.StartupPath, "db.sdf")))
            {
                using (SqlCeEngine eng = new SqlCeEngine(dbPath))
                    eng.CreateDatabase();
                using (SqlCeConnection conn = new SqlCeConnection(dbPath))
                {
                    conn.Open();
                    using (SqlCeCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "CREATE TABLE [Beatmaps] " +
                                          "(" +
                                          "[Hash] nvarchar(32) NOT NULL, " +
                                          "[Filename] nvarchar(500) NOT NULL" +
                                          ")";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "ALTER TABLE [Beatmaps] ADD CONSTRAINT [PK_Beatmaps] PRIMARY KEY ([Hash]) ";
                        cmd.ExecuteNonQuery();
                    }
                }

            }
            return new SqlCeConnection(dbPath);
        }

        private static void ConvertProfile(IUserProfile profile, SqlCeConnection conn)
        {
            Console.Write("profile [{0}]...", profile.Name);

            using (SqlCeCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO Profiles (Name, Description, DefaultVocabularyPath, Sleep, Beep) VALUES (@Name, @Description, @DefaultVocabularyPath, @Sleep, @Beep)";
                cmd.Parameters.Add(new SqlCeParameter("Name", profile.Name));
                cmd.Parameters.Add(new SqlCeParameter("Description", String.Empty));
                cmd.Parameters.Add(new SqlCeParameter("DefaultVocabularyPath", profile.DefaultVocabulary));
                cmd.Parameters.Add(new SqlCeParameter("Sleep", profile.SleepInterval));
                cmd.Parameters.Add(new SqlCeParameter("Beep", profile.Beep));
                cmd.Prepare();
                cmd.ExecuteNonQuery();
            }

            Console.WriteLine("ok");

            ConvertActions(profile, conn);

            foreach (IStatistics statistics in profile as IEnumerable)
            {
                ConvertStatistics(statistics, conn);
            }
        }

        private void frmEditReceipt_Load(object sender, EventArgs e)
        {
            SqlCeConnection myConnection = default(SqlCeConnection);
            myConnection = new SqlCeConnection("Data source="
                + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
                + "\\GodDB.sdf;"));
            myConnection.Open();
            SqlCeCommand myCommand = myConnection.CreateCommand();
            myCommand.CommandText = "Select [id],[itemid],[batch],[qty],[zone],[channel],[receiptdate],[receiptby] from [receipt] " +
                " where id = '" + strID + "'";
            myCommand.CommandType = CommandType.Text;

            DataTable dt = new DataTable();
            SqlCeDataAdapter Adapter = default(SqlCeDataAdapter);
            Adapter = new SqlCeDataAdapter(myCommand);
            Adapter.Fill(dt);

            myConnection.Close();
            if (dt.Rows.Count > 0)
            {
                this.txtItemId.Text = dt.Rows[0]["itemid"].ToString();
                this.txtBatch.Text = dt.Rows[0]["batch"].ToString();
                this.txtQty.Text = dt.Rows[0]["qty"].ToString();
                this.txtZone.Text = dt.Rows[0]["zone"].ToString();
                this.txtChannel.Text = dt.Rows[0]["channel"].ToString();
                this.txtReceiptDate.Text = dt.Rows[0]["receiptdate"].ToString();
                this.txtReceiptBy.Text = dt.Rows[0]["receiptby"].ToString();
            }
            dt = null;
        }

        private bool ReadSetup()
        {
            SqlCeConnection Myconnection = null;
            SqlCeDataReader dbReader = null;
            
            Myconnection = new SqlCeConnection( connStr );
            Myconnection.Open();

            SqlCeCommand cmd = Myconnection.CreateCommand();

            cmd.CommandText = "SELECT * FROM Setup";
            dbReader = cmd.ExecuteReader();
            
            if (dbReader.Read())
            {
                if (dbReader.GetString(0) == "False")
                    ItemCheckbox.Checked = false;
                else
                    ItemCheckbox.Checked = true;
                
                BarcodePrinterComboBox.Text = dbReader.GetString(1);
                BatteryPrinterComboBox.Text = dbReader.GetString(2);
                Myconnection.Close();
                return true;
                
            }
            Myconnection.Close();
            return false;
 
        }

        public Boolean checkStoredata()
        {
            bm = this.txtBom.Text.ToUpper();
            Boolean isCheck = false;
            myConnection = default(SqlCeConnection);
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            Adapter = default(SqlCeDataAdapter);
            myConnection = new SqlCeConnection(storagePath.getDatabasePath());
            myConnection.Open();
            myCommand = myConnection.CreateCommand();
            myCommand.CommandText = "SELECT [ID]  FROM [" + storagePath.getBomTable() + "] WHERE ID ='"
                + bm + "' ";

            myCommand.CommandType = CommandType.Text;

            Adapter = new SqlCeDataAdapter(myCommand);
            Adapter.Fill(dt);
            Adapter.Dispose();
            if (dt.Rows.Count > 0)
            {
                isCheck = true;

            }
            else
            {
                isCheck = false;
            }
            myCommand.Dispose();
            dt = null;
            myConnection.Close();
            return isCheck;
        }

        public void Construct_ShouldRunGivenScriptsOnDatabase()
        {
            var createTable = "create table TheTable(id int primary key, name nvarchar(128));";
            var insertData = "insert into TheTable(id, name) values (1, 'one');";
            var selectData = "select name from TheTable where id = 1;";
            using (var db = new TempDBSqlCe(new[] { createTable, insertData }))
            {
                //---------------Set up test pack-------------------

                //---------------Assert Precondition----------------

                //---------------Execute Test ----------------------
                using (var conn = new SqlCeConnection(db.ConnectionString))
                {
                    conn.Open();
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = selectData;
                        using (var rdr = cmd.ExecuteReader())
                        {
                            Assert.IsTrue(rdr.Read());
                            Assert.AreEqual("one", rdr["name"].ToString());
                        }
                    }
                }

                //---------------Test Result -----------------------
            }
        }

        public static DataTable GetContentsOf(string tableName)
        {
            using (var dbContext = new ProductsDbContext())
            using (var connection = new SqlCeConnection(dbContext.Database.Connection.ConnectionString))
            using (var command = connection.CreateCommand())
            {

                var sql = "SELECT* FROM " + tableName;
                command.CommandText = sql;

                var adapter = new SqlCeDataAdapter(command);
                var dataSet = new DataSet();
                adapter.Fill(dataSet);

                return dataSet.Tables[0];
            }

            //var connection = new SqlConnection(ConnectionString);

            //using (connection)
            //{
            //    connection.Open();
            //    var command = new SqlCommand("SELECT * FROM " + tableName, connection);

            //    var adapter = new SqlDataAdapter(command);
            //    var dataSet = new DataSet();
            //    adapter.Fill(dataSet);

            //    return dataSet.Tables[0];
            //}
        }

 public void ReadTo(Action<SqlCeDataReader> readerAction)
 {
     try
     {
         using (var connection = new SqlCeConnection(ConnectionString))
         {
             connection.Open();
             using (var transaction = connection.BeginTransaction())
             {
                 using (var command = connection.CreateCommand())
                 {
                     command.CommandText = _commandText;
                     SetParametersToCommand(command);
                     using (var reader = command.ExecuteReader())
                     {
                         while (reader.Read())
                         {
                             readerAction.Invoke(reader);
                         }
                     }
                 }
             }
         }
     }
     catch (SqlCeLockTimeoutException ex)
     {
         Thread.Sleep(TimeSpan.FromMilliseconds(500));
         ReadTo(readerAction);
     }
     catch (Exception ex)
     {
         LogOrSendMailToAdmin(ex);
         throw;
     }
 }

        private void frmDetail_Load(object sender, EventArgs e)
        {
            myConnection = default(SqlCeConnection);
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            Adapter = default(SqlCeDataAdapter);
            myConnection = new SqlCeConnection(storagePath.getDatabasePath());
            myConnection.Open();
            myCommand = myConnection.CreateCommand();
            myCommand.CommandText = "SELECT [ID],[Job],[ItemId],[Qty],[Unit],[CheckedDateTime],[CheckedBy],[Checked]  FROM ["
                + storagePath.getStoreTable() + "] WHERE ID ='"
                + bm + "' and Job='" + jb + "' and ItemId = '" + item + "' and Checked='true'";

            myCommand.CommandType = CommandType.Text;

            Adapter = new SqlCeDataAdapter(myCommand);
            Adapter.Fill(ds);
            Adapter.Dispose();
            if (ds.Tables[0].Rows.Count > 0)
            {
                //isCheck = true;
                this.txtItem.Text = ds.Tables[0].Rows[0]["ItemId"].ToString();
                this.txtCheckedDateTime.Text = ds.Tables[0].Rows[0]["CheckedDateTime"].ToString();
                this.txtCheckedBy.Text = ds.Tables[0].Rows[0]["CheckedBy"].ToString();
            }
            else
            {
               // isCheck = false;
            }
            myCommand.Dispose();
            dt = null;
            myConnection.Close();
        }

        private void buttonImage1_Click(object sender, EventArgs e)
        {
            /*
             * Verificar se existe coluna e inserir caso n exista
             * garantir compatibilidade com bancos velhos
             */
            //CONN
            SqlCeConnection conn =
                new SqlCeConnection("Data Source = " + Library.appDir + "\\db\\citeluz.sdf; [email protected]$X-PRO;");

            //coluna user
            conn.Open();
            SqlCeCommand command = conn.CreateCommand();
            command.CommandText = "ALTER TABLE trafo ADD [user] NVARCHAR(15) DEFAULT 'TESTE';";
            try
            {
                command.ExecuteNonQuery();
                MessageBox.Show("Tabela trafo atualizada com sucesso");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        private void openCheck(string _tableName,string _bom,string _job)
        {
            myConnection = default(SqlCeConnection);
            DataTable dt = new DataTable();
            Adapter = default(SqlCeDataAdapter);
            myConnection = new SqlCeConnection(storagePath.getDatabasePath());
            myConnection.Open();
            myCommand = myConnection.CreateCommand();
            myCommand.CommandText = "SELECT [id] FROM [" + _tableName + "] WHERE id ='" + txtBom.Text + "' ";

            myCommand.CommandType = CommandType.Text;

            Adapter = new SqlCeDataAdapter(myCommand);
            Adapter.Fill(dt);

            myConnection.Close();
            if (dt.Rows.Count > 0)
            {

                frmCheck frmCheck = new frmCheck(this.txtJob.Text.ToUpper(),this.txtBom.Text.ToUpper(),"");

               // frmCheck.BindDataGrid();
                frmCheck.setBom(dt.Rows[0]["id"].ToString());
                frmCheck.setData(_bom, _job);
                frmCheck.Show();
               // frmCheck.setBom(this.txtBom.Text);
                //frmCheck.setJob(this.txtJob.Text);
                dt = null;
                MessageBox.Show("Bom : " + dt.Rows[0]["id"].ToString());
            }
            else
            {
                MessageBox.Show("Bom : " + txtBom.Text + " does not exited");
            }
        }

 public static TRonda GetRondaFromTag(string tag, SqlCeConnection conn)
 {
     TRonda r = null;
     using (SqlCeCommand cmd = conn.CreateCommand())
     {
         var sql = @"SELECT r.rondaId, r.nombre, r.tag, r.tagf,
                         rp.rondaPuntoId, rp.orden, rp.puntoId,
                         p.nombre AS pnombre, p.edificioId, p.tag AS ptag,
                         e.nombre AS enombre, e.grupoId, g.nombre AS gnombre, p.cota, p.cubiculo, r.mintime, r.maxtime, p.csnmax, p.csnmargen, p.lastcontrol
                     FROM rondas AS r
                         LEFT OUTER JOIN rondaspuntos AS rp ON rp.rondaId = r.rondaId
                         LEFT OUTER JOIN puntos AS p ON p.puntoId = rp.puntoId
                         LEFT OUTER JOIN edificios AS e ON e.edificioId = p.edificioId
                         LEFT OUTER JOIN grupos AS g ON g.grupoId = e.grupoId
                     WHERE r.tag = '{0}' ORDER BY rp.orden";
         cmd.CommandType = System.Data.CommandType.Text;
         cmd.CommandText = String.Format(sql, tag);
         using (SqlCeDataReader dr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
         {
             if (dr.HasRows)
             {
                 r = GetRondaFromDr(dr);
             }
             if (!dr.IsClosed)
                 dr.Close();
         }
     }
     return r;
 }

 public bool IsExist()
 {
     try
     {
         using (var connection = new SqlCeConnection(ConnectionString))
         {
             connection.Open();
             using (var transaction = connection.BeginTransaction())
             {
                 using (var command = connection.CreateCommand())
                 {
                     var statement = "SELECT COUNT(*) FROM information_schema.tables WHERE table_name = @tableName";
                     command.CommandText = statement;
                     command.Parameters.AddWithValue("tableName", tableName);
                     var count = Convert.ToInt32(command.ExecuteScalar());
                     return count > 0;
                 }
             }
         }
     }
     catch (SqlCeLockTimeoutException ex)
     {
         Thread.Sleep(TimeSpan.FromMilliseconds(500));
         return IsExist();
     }
     catch (Exception ex)
     {
         LogOrSendMailToAdmin(ex);
         throw;
     }
 }