C# SqlCeCommand.Dispose方法代码示例

        /// <summary>
        /// Create the initial database
        /// </summary>
        private void CreateDB()
        {
            var connection = new SqlCeConnection(this.path);

            try
            {
                var eng = new SqlCeEngine(this.path);
                var cleanup = new System.Threading.Tasks.Task(eng.Dispose);
                eng.CreateDatabase();
                cleanup.Start();
            }
            catch (Exception e)
            {
                EventLogging.WriteError(e);
            }

            connection.Open();
            var usersDB =
                new SqlCeCommand(
                    "CREATE TABLE Users_DB("
                    + "UserID int IDENTITY (100,1) NOT NULL UNIQUE, "
                    + "UserName nvarchar(128) NOT NULL UNIQUE, "
                    + "PassHash nvarchar(128) NOT NULL, "
                    + "Friends varbinary(5000), "
                    + "PRIMARY KEY (UserID));",
                    connection);
            usersDB.ExecuteNonQuery();
            usersDB.Dispose();
            connection.Dispose();
            connection.Close();
        }

 //public void suaraOK()
 //{
 //    SoundPlayer playOK = new SoundPlayer(@"Backup\doorbell.wav");
 //    playOK.Play();
 //}
 //public void suaraError()
 //{
 //    SoundPlayer playError = new SoundPlayer(@"Backup\sirentone.WAV");
 //    playError.Play();
 //}
 public void Simpan()
 {
     //string Tanggal = DateTime.Now.ToString("dd/MM/yyyy");
     //string JamTanggal = DateTime.Now.ToString();
     try
     {
         SqlCeDataReader dr;
         SqlCeCommand cmd = new SqlCeCommand("Select * from AngkutTP where ScanBarcode ='" + textBoxBarcode.Text + "'", cKoneksi.Con);
         if (cKoneksi.Con.State == ConnectionState.Closed) { cKoneksi.Con.Open(); }
         dr = cmd.ExecuteReader();
         if (dr.Read())
         {
             //cSound.suaraError();
             MessageBox.Show("Duplicate Scan!!", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
             dr.Close();
             cmd.Dispose();
             if (cKoneksi.Con.State == ConnectionState.Open) { cKoneksi.Con.Close(); }
             textBoxBarcode.Text = "";
             textBoxBarcode.Focus();
         }
         else
         {
             dr.Close();
             cmd.Dispose();
             string strSQL = "INSERT INTO AngkutTP VALUES('" + textBoxNoPolisi.Text + "','" + textBoxNomorator.Text + "','" + textBoxNamaTP.Text + "','" + textBoxBarcode.Text + "',GetDate())";
             cQuery.Execute(strSQL);
             //cSound.suaraOK();
         }
     }
     catch (SqlCeException ex)
     {
         MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
     }
 }

 private void addToDatabase(string _user,string _pwd)
 {
     myConnection = default(SqlCeConnection);
        // DataTable dt = new DataTable();
     Adapter = default(SqlCeDataAdapter);
     myConnection = new SqlCeConnection(storagePath.getDatabasePath());
     myConnection.Open();
     myCommand = myConnection.CreateCommand();
     myCommand.CommandText = "INSERT INTO ["+storagePath.getUserTable()+"] ([username],[password]) VALUES  " +
         " ('" + _user
         + "','" + _pwd
         + "' ) ";
     myCommand.CommandType = CommandType.Text;
     try
     {
         myCommand.ExecuteNonQuery();
         myCommand.Dispose();
         MessageBox.Show("Username : " + _user + " and password : " + _pwd + " added sucessfully");
     }
     catch (Exception ex)
     {
         //MessageBox.Show(_id + " : " + _item + " : " + _name);
         myCommand.Dispose();
     }
     myConnection.Close();
 }

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

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

        /// <summary>
        /// Returns the number of affected rows from the Non-Query (UPDATE-, INSERT- or DELETE-statement only)
        /// </summary>
        /// <param name="sqlString">The SQL</param>
        /// <returns></returns>
        public int nonQuery(string sqlString)
        {
            int numberOfRows = 0;

            SqlCeCommand sqlCommand = null;

            try
            {
                dbConnection.ConnectionString = global::Tekken5DarkRessurectionScoreKeeper.Properties.Settings.Default.TekkenScoreDBConnectionString;
                sqlCommand = new SqlCeCommand(sqlString, dbConnection);

                openConnection();
                numberOfRows = sqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString(), "Database error (NonQuery)");
            }
            finally
            {
                closeConnection();
                sqlCommand.Dispose();
            }
            return numberOfRows;
        }

        /**
         *
         * 执行sql，包括 ： 插入、更新、删除
         *
         * */
        public bool excute(string sql)
        {
            SqlCeCommand command = null;
            try
            {
                command = new SqlCeCommand();
                command.Connection = conn;
                command.CommandText = sql;
                command.Transaction = conn.BeginTransaction();
                if (-1 != command.ExecuteNonQuery())
                {
                    command.Transaction.Commit();
                    return true;
                }

                else
                    return false;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            finally
            {
                if (command != null)
                    command.Dispose();
            }
        }

        public void SelectUser(int x)
        {
            try
            {
                byte count = 0;
                SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Users", cKoneksi.Con);
                SqlCeDataReader dr;
                if (cKoneksi.Con.State == ConnectionState.Closed) { cKoneksi.Con.Open(); }
                dr = cmd.ExecuteReader();
                if (dr.Read()) { count = 1; } else { count = 0; }
                dr.Close(); cmd.Dispose(); if (cKoneksi.Con.State == ConnectionState.Open) { cKoneksi.Con.Close(); }
                if (count != 0)
                {
                    DataSet ds = new DataSet();
                    SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT * FROM Users", cKoneksi.Con);
                    da.Fill(ds, "Users");
                    textBoxUser.Text = ds.Tables["Users"].Rows[x][0].ToString();
                    textBoxPass.Text = ds.Tables["Users"].Rows[x][1].ToString();
                    checkBoxTP.Checked = Convert.ToBoolean(ds.Tables["Users"].Rows[x][2]);
                    checkBoxTPK.Checked = Convert.ToBoolean(ds.Tables["Users"].Rows[x][3]);

                    ds.Dispose();
                    da.Dispose();
                }
                else
                {
                    MessageBox.Show("Data User Kosong", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
                    buttonClose.Focus();
                }
            }
            catch (SqlCeException ex)
            {
                MessageBox.Show(cError.ComposeSqlErrorMessage(ex), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
            }
        }

 public void Login()
 {
     try
     {
         SqlCeDataReader dr;
         string sql = "SELECT * From Users cross join idhandheld where userid='" + textBoxuserid.Text + "' and password ='" + textBoxPassword.Text + "'";
         SqlCeCommand cmd = new SqlCeCommand(sql,cKoneksi.Con);
         if (cKoneksi.Con.State == ConnectionState.Closed) { cKoneksi.Con.Open(); }
         dr = cmd.ExecuteReader();
         if (dr.Read())
         {
             UserID = dr["Userid"].ToString();
             IDHH = dr["IDHH"].ToString();
             ClassUser.UserID = UserID.Trim();
             ClassUser.HandheldID = IDHH.Trim();
             TP = dr["TP"].ToString();
             TPK = dr["TPK"].ToString();
             dr.Close();
             cmd.Dispose();
             if (cKoneksi.Con.State == ConnectionState.Open) { cKoneksi.Con.Close(); }
             cekrole();
         }
         else
         {
             if (cKoneksi.Con.State == ConnectionState.Open) { cKoneksi.Con.Close(); }
             dr.Close();
             cmd.Dispose();
             MessageBox.Show("Username / Password Salah", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2);
             textBoxuserid.Text = "";
             textBoxuserid.Focus();
             textBoxPassword.Text = "";
         }
         textBoxuserid.Text = "";
         textBoxuserid.Focus();
         textBoxPassword.Text = "";
     }
     catch (SqlCeException ex)
     {
         MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
     }
 }

 /// <summary>
 /// Cleanup unsuccessfull test log entries from the database (tests which did fail anyway).
 /// </summary>
 /// <param name="testContext">The test context.</param>
 /// <remarks>Documented by Dev03, 2008-08-01</remarks>
 internal static void Cleanup()
 {
     using (SqlCeConnection connection = GetConnection())
     {
         connection.Open();
         SqlCeCommand command = new SqlCeCommand("DELETE FROM \"TestRunData\" WHERE stoptime IS NULL AND run_id = @runid", connection);
         command.Parameters.Add("@runid", SqlDbType.Int, 8);
         command.Parameters["@runid"].Value = TestRunId;
         command.ExecuteNonQuery();
         command.Dispose();
     }
 }

        /// <summary>
        /// Hakee tietokannasta osallistujien ID:T ja paivittaa onkoTyhja
        /// arvon osallistujien ID-listan pituudella. Arvoa tarvitaan
        /// evaamaan tai sallimaan paasy tulosten kirjaukseen 
        /// </summary>
        /// <param name="onkoTyhja"> arvo kertoo montako osallistujaa tietokannassa on </param>
        /// <returns></returns>
        public int checkOsallistujat(int onkoTyhja)
        {
            List<int> osallistujat = new List<int>();

            SqlCeCommand cmd = null;
            SqlCeDataReader rdr = null;
            bool ok = false;

            do
            {
                SqlCeConnection con = _connection;
                try
                {
                    if (con.State == ConnectionState.Closed)
                        con.Open();

                    string Sql = String.Format(" SELECT osallistujaID FROM Osallistuja");
                    cmd = new SqlCeCommand(Sql, con);

                    rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        osallistujat.Add(rdr.GetInt32(0));
                    }

                    onkoTyhja = osallistujat.Count();
                    Console.WriteLine("Osallistujien maara tietokannassa: " + onkoTyhja);
                }
                catch (SqlCeException ex)
                {
                    //ShowErrors(ex);
                    Console.WriteLine("VIRHEILMOITUS checkOsallistujat");
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    if (con.State == ConnectionState.Open)
                    {
                        con.Close();
                    }
                    rdr.Close();
                    cmd.Dispose();
                    ok = true;
                }

            } while (!ok);

            return onkoTyhja;
        }

        public PrekrsajnaForma()
        {
            InitializeComponent();

            // Dohvat popisa prekršaja.
            SqlCeConnection conn = new SqlCeConnection(Program.ConnString);
            string sqlQry =
                "SELECT " +
                    "ID, " +
                    "Naziv " +
                "FROM " +
                    "Prekrsaj;";

            try
            {
                DataTable dt = new DataTable("Prekrsaji");
                SqlCeCommand cmd = new SqlCeCommand(sqlQry, conn);
                SqlCeDataAdapter da = new SqlCeDataAdapter(sqlQry, conn);

                conn.Open();
                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    lbxPrekrsaji.DataSource = dt;
                    //lbxPrekrsaji.Refresh();
                }
                else
                {
                    throw new Exception("WTF! (What a Terrible Failure). Katalog prekršaja je prazan!");
                }
                da.Dispose();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                conn.Dispose();
            }
        }

        private void buttonAngkutTP_Click(object sender, EventArgs e)
        {
            DialogResult pesan = MessageBox.Show("Anda Yakin ingin mengconvert data ini?", "Konfirmasi", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
            if (DialogResult.OK == pesan)
            {
                //convert
                try
                {
                    Cursor.Current = Cursors.WaitCursor;
                    byte count = 0;
                    SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM AngkutTP", cConnection.Con);
                    SqlCeDataReader dr;
                    if (cConnection.Con.State == ConnectionState.Closed) { cConnection.Con.Open(); }
                    dr = cmd.ExecuteReader();
                    if (dr.Read())
                    {
                        count = 1;
                    }
                    else
                    {
                        count = 0;
                    }
                    dr.Close();
                    cmd.Dispose();

                    if (cConnection.Con.State == ConnectionState.Open) { cConnection.Con.Close(); }
                    if (count != 0)
                    {
                        ConvertAngkutTP();

                        Cursor.Current = Cursors.Default;
                    }
                    else
                    {
                        Cursor.Current = Cursors.Default;
                        MessageBox.Show("Tidak ada data Angkut TP", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
                    }

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
                    Cursor.Current = Cursors.Default;
                }

            }
        }

        public bool checkUser(String username)
        {
            try
            {
                conn = new SqlCeConnection(Settings.Default.AccountsConnectionString);
                conn.Open();

                cmd = new SqlCeCommand("SELECT * FROM [Users] WHERE Username = @Username;", conn);
                cmd.Parameters.AddWithValue("@Username", username);

                SqlCeDataReader reader = cmd.ExecuteReader();
                cmd.Dispose();
                return reader.HasRows;
            }
            catch (Exception) { }

            return false;
        }

 public static void Exec(string query, SqlCeConnection conn, Dictionary<string, object> parameters = null)
 {
     SqlCeTransaction trans = conn.BeginTransaction();
     using (SqlCeCommand com = new SqlCeCommand(query, conn))
     {
         com.Transaction = trans;
         if (parameters != null)
         {
             foreach (var element in parameters)
             {
                 com.Parameters.AddWithValue(element.Key, element.Value);
             }
         }
         com.ExecuteNonQuery();
         trans.Commit(CommitMode.Immediate);
         com.Dispose();
     }
 }