C# SqlCeDataAdapter.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);
            }
        }

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

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

        public Boolean checkStoredata(string _item)
        {
            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.getStoreTable() + "] WHERE ID ='"
                + bm+"' and Job='"+jb+"' and ItemId = '"+_item+"' and Checked='true'" ;

            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 RunQueryTable(string query, ref DataTable myDataTable)
 {
     SqlCeDataAdapter DBAdapter = null;
     try
     {
         using (SqlCeCommand command = Connection.CreateCommand())
         {
             command.CommandText = query;
             DBAdapter = new SqlCeDataAdapter(query, Connection);
             DBAdapter.Fill(myDataTable);
             DBAdapter.Dispose();
         }
     }
     catch (Exception e)
     {
         string test = e.Message;
     }
 }

        private void FormMasterUser_Load(object sender, EventArgs e)
        {
            statusBar1.Text = "User : " + ClassUser.UserID.ToString() + " | Id Hand Held : " + ClassUser.HandheldID;
            try
            {
                DataSet ds = new DataSet();
                string strSQL = "SELECT * FROM Users";
                SqlCeDataAdapter da = new SqlCeDataAdapter(strSQL, cKoneksi.Con);
                da.Fill(ds, "Users");
                dataGridMasterUser.DataSource = ds.Tables[0];

                DataGridTableStyle ts = new DataGridTableStyle();
                dataGridMasterUser.TableStyles.Clear();
                ts.MappingName = "Users";

                DataGridTextBoxColumn col1 = new DataGridTextBoxColumn();
                col1.HeaderText = "Username";
                col1.MappingName = "Userid";
                col1.Width = 70;
                ts.GridColumnStyles.Add(col1);

                DataGridTextBoxColumn col2 = new DataGridTextBoxColumn();
                col2.HeaderText = "Password";
                col2.MappingName = "Password";
                col2.Width = 60;
                ts.GridColumnStyles.Add(col2);

                DataGridTextBoxColumn col3 = new DataGridTextBoxColumn();
                col3.HeaderText = "TP";
                col3.MappingName = "TP";
                col3.Width = 38;
                ts.GridColumnStyles.Add(col3);

                DataGridTextBoxColumn col4 = new DataGridTextBoxColumn();
                col4.HeaderText = "TPK";
                col4.MappingName = "TPK";
                col4.Width = 38;
                ts.GridColumnStyles.Add(col4);

                dataGridMasterUser.TableStyles.Add(ts);
                dataGridMasterUser.Refresh();

                ds.Tables.Clear();
                da.Dispose();
                ds.Dispose();
                dataGridMasterUser.Enabled = true;
                textBoxUser.Text = "";
                textBoxUser.Enabled = false;
                textBoxPass.Text = "";
                textBoxPass.Enabled = false;
                checkBoxTP.Enabled = false;
                checkBoxTPK.Enabled = false;
                buttonCancel.Enabled = false;
                buttonSave.Enabled = false;
                buttonInsert.Enabled = true;
                buttonUpdate.Enabled = true;
                buttonDelete.Enabled = true;

                SelectUser(0);
            }
            catch (SqlCeException ex)
            {
                MessageBox.Show(cError.ComposeSqlErrorMessage(ex), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
            }
        }

        public static bool GetConfigFromSqlCe(string filename, string password)
        {
            try
            {
                string strDataSource = @"Data Source=" + Path.Combine(Directory.GetCurrentDirectory(), filename) + ";Encrypt Database=True;Password=" + password + ";" +
                    @"File Mode=shared read;Persist Security Info = False;";
                SqlCeConnection conn = new SqlCeConnection();
                conn.ConnectionString = strDataSource;

                SqlCeCommand selectCmd = conn.CreateCommand();
                selectCmd.CommandText = "SELECT * FROM Config";

                SqlCeDataAdapter adp = new SqlCeDataAdapter(selectCmd);

                DataTable dt = new DataTable();
                adp.Fill(dt);
                adp.Dispose();
                conn.Close();

                Param.SqlCeConfig = new Hashtable();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    Param.SqlCeConfig.Add(dt.Rows[i][0].ToString(), dt.Rows[i][1].ToString());
                }

                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("รหัสผ่านไม่ถูกต้อง กรุณาลองใหม่อีกครั้ง\n" + ex.Message, "มีข้อผิดพลาดเกิดขึ้น", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }

        }

        /// <summary>
        /// Execute a SqlCeCommand (that returns a resultset) against the specified SqlCeTransaction
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  XmlReader r = ExecuteXmlReader(trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", new SqlCeParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">A valid SqlCeTransaction</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <returns>An XmlReader containing the resultset generated by the command</returns>
        public static string ExecuteXml(SqlCeTransaction transaction, CommandType commandType, string commandText, params SqlCeParameter[] commandParameters)
        {
            if( transaction == null ) throw new ArgumentNullException( "transaction" );
            if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );

            //			// Create a command and prepare it for execution
            SqlCeCommand cmd = new SqlCeCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, (SqlCeConnection)transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // Create the DataAdapter & DataSet
            SqlCeDataAdapter obj_Adapter =new SqlCeDataAdapter (cmd);
            DataSet ds=new DataSet();
            ds.Locale  =CultureInfo.InvariantCulture;
            obj_Adapter.Fill(ds);

            // Detach the SqlCeParameters from the command object, so they can be used again
            cmd.Parameters.Clear();
            string retval= ds.GetXml();
            ds.Clear();
            obj_Adapter.Dispose ();
            return retval;
        }

        /// <summary>
        /// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
        /// </remarks>
        /// <param name="insertCommand">A valid transact-SQL statement to insert new records into the data source</param>
        /// <param name="deleteCommand">A valid transact-SQL statement to delete records from the data source</param>
        /// <param name="updateCommand">A valid transact-SQL statement used to update records in the data source</param>
        /// <param name="dataSet">The DataSet used to update the data source</param>
        /// <param name="tableName">The DataTable used to update the data source.</param>
        public static void UpdateDataset(SqlCeCommand insertCommand, SqlCeCommand deleteCommand, SqlCeCommand updateCommand, DataSet dataSet, string tableName)
        {
            if( insertCommand == null ) throw new ArgumentNullException( "insertCommand" );
            if( deleteCommand == null ) throw new ArgumentNullException( "deleteCommand" );
            if( updateCommand == null ) throw new ArgumentNullException( "updateCommand" );
            if( tableName == null || tableName.Length == 0 ) throw new ArgumentNullException( "tableName" );

            // Create a SqlDataAdapter, and dispose of it after we are done
            SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter();
            try
            {
                // Set the data adapter commands
                dataAdapter.UpdateCommand = updateCommand;
                dataAdapter.InsertCommand = insertCommand;
                dataAdapter.DeleteCommand = deleteCommand;

                // Update the dataset changes in the data source
                dataAdapter.Update (dataSet,tableName);

                // Commit all the changes made to the DataSet
                dataSet.AcceptChanges();
            }
            catch (SqlCeException E)
            {string strError=E.Message;}
            finally{dataAdapter.Dispose();}
        }

        /// <summary>
        /// Private helper method that execute a SqlCeCommand (that returns a resultset) against the specified SqlCeTransaction and SqlCeConnection
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  FillDataset(conn, trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, new SqlCeParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid SqlCeConnection</param>
        /// <param name="transaction">A valid SqlCeTransaction</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command</param>
        /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
        /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
        /// by a user defined name (probably the actual table name)
        /// </param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        private static void FillDataset(SqlCeConnection connection, SqlCeTransaction transaction, CommandType commandType, 
			string commandText, DataSet dataSet, string[] tableNames,
			params SqlCeParameter[] commandParameters)
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );
            if( dataSet == null ) throw new ArgumentNullException( "dataSet" );

            // Create a command and prepare it for execution
            SqlCeCommand command = new SqlCeCommand();
            bool mustCloseConnection = false;
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );

            // Create the DataAdapter & DataSet
            SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(command);

            try
            {
                // Add the table mappings specified by the user
                if (tableNames != null && tableNames.Length > 0)
                {
                    string tableName = "Table";
                    for (int index=0; index < tableNames.Length; index++)
                    {
                        if( tableNames[index] == null || tableNames[index].Length == 0 ) throw new ArgumentException( "The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames" );
                        dataAdapter.TableMappings.Add(tableName, tableNames[index]);
                        tableName += (index + 1).ToString();
                    }
                }

                // Fill the DataSet using default values for DataTable names, etc
                dataAdapter.Fill(dataSet);

                // Detach the SqlCeParameters from the command object, so they can be used again
                command.Parameters.Clear();

                if( mustCloseConnection )
                    connection.Close();
            }
            finally
            {
                dataAdapter.Dispose();
            }
        }

        private void PopulateTheSchemaTables(string schemaQuery, DataTable[] tables)
        {
            string sqlQuery;

            using (SqlCeConnection connection = new SqlCeConnection(m_ConnectionString))
            {
                connection.Open();
                SqlCeDataAdapter adapter = new SqlCeDataAdapter();

                sqlQuery =
                     Utility.GetResource(Assembly.GetExecutingAssembly(),
                     "SumDataTierGenerator.SchemaExtractor.EmbeddedResources.SqlCeExtractDatabaseTables.sql");

                DataTable tblTables = new DataTable("Tables");
                adapter.SelectCommand = new SqlCeCommand(sqlQuery, connection);
                adapter.Fill(tables[0]);

                sqlQuery =
                    Utility.GetResource(Assembly.GetExecutingAssembly(),
                    "SumDataTierGenerator.SchemaExtractor.EmbeddedResources.SqlCeExtractDatabaseTableColumns.sql");

                DataTable tblColumns = new DataTable("Columns");
                adapter.SelectCommand.CommandText = sqlQuery;
                adapter.Fill(tables[1]);

                sqlQuery =
                    Utility.GetResource(Assembly.GetExecutingAssembly(),
                    "SumDataTierGenerator.SchemaExtractor.EmbeddedResources.SqlCeExtractDatabasePrimaryKeys.sql");

                DataTable tblKeys = new DataTable("Keys");
                adapter.SelectCommand.CommandText = sqlQuery;
                adapter.Fill(tables[2]);

                adapter.Dispose();
                connection.Close();
            }
        }

        public DataSet getData(string _item)
        {
            myConnection = default(SqlCeConnection);

            DataSet dsNew = new DataSet();
            Adapter = default(SqlCeDataAdapter);
            myConnection = new SqlCeConnection(storagePath.getDatabasePath());
            myConnection.Open();
            myCommand = myConnection.CreateCommand();
            myCommand.CommandText = "SELECT [id],[item],[qty],[unit] FROM ["
                + storagePath.getBomTable() + "] WHERE id ='"
                + bm + "' and item='"+_item+"' ";

            myCommand.CommandType = CommandType.Text;

            Adapter = new SqlCeDataAdapter(myCommand);
            Adapter.Fill(dsNew);
            Adapter.Dispose();
            myCommand.Dispose();
            //dt = null;
            myConnection.Close();

            return dsNew;
        }

        public static DataTable SqlCeQuery(string sql)
        {
            string connStr = "Data Source=" + Param.SqlCeFile;
            Param.SqlCeConnection = new SqlCeConnection(connStr);
            if (Param.SqlCeConnection.State == ConnectionState.Closed)
            {
                Param.SqlCeConnection = new SqlCeConnection(connStr);
                Param.SqlCeConnection.Open();
            }

            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            DataTable dt = new DataTable();
            try
            {
                SqlCeDataAdapter adapter = new SqlCeDataAdapter(sql, Param.SqlCeConnection);
                adapter.Fill(dt);
                adapter.Dispose();
            }
            catch (Exception ex)
            {
                WriteErrorLog(ex.Message);
                WriteErrorLog(ex.StackTrace);
            }
            finally
            {
                Param.SqlCeConnection.Close();
            }
            return dt;
        }

        public void BindDataGrid()
        {
            bm = bm.ToUpper();
            jb = jb.ToUpper();
            //MessageBox.Show("xx : "+strBom);
            //string tot = "B1106488";
            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], [item], [qty],[unit] FROM [" + tableName + "] WHERE id ='"
                + bm + "' and unit !='SEC' and item != 'ROYALTY STANDARD'";

            myCommand.CommandType = CommandType.Text;

            Adapter = new SqlCeDataAdapter(myCommand);
            Adapter.Fill(ds);
            Adapter.Dispose();
            myConnection.Close();
            DataTable myTable = new DataTable();
            //Create new Column in DataTable
            myTable.Columns.Add(new DataColumn("OK", typeof(System.String)));

            myTable.Columns.Add(new DataColumn("Item", typeof(System.String)));

               // myTable.Columns.Add(new DataColumn("Qty", typeof(System.String)));
               // myTable.Columns.Add(new DataColumn("Unit", typeof(System.String)));
            this.dgvData.TableStyles.Clear();
            DataGridTableStyle tableStyle = new DataGridTableStyle();
            tableStyle.MappingName = myTable.TableName;
            foreach (DataColumn item in myTable.Columns)
            {
                DataGridTextBoxColumn tbcName = new DataGridTextBoxColumn();
                if (item.ColumnName == "OK")
                {
                    tbcName.Width = 20;

                }
                else
                {
                    tbcName.Width = 175;
                }
                tbcName.MappingName = item.ColumnName;
                tbcName.HeaderText = item.ColumnName;
                tableStyle.GridColumnStyles.Add(tbcName);
            }
            this.dgvData.TableStyles.Add(tableStyle);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                DataRow dr = myTable.NewRow();
                itemCode =  ds.Tables[0].Rows[i]["item"].ToString();
              //  MessageBox.Show(itemCode);
                if (this.checkStoredata(itemCode) == true)
                {
                    dr["OK"] = " / ";
                }
                else
                {
                    dr["OK"] = " ";
                }

                dr["Item"] = ds.Tables[0].Rows[i]["item"];

               // dr["Qty"] = ds.Tables[0].Rows[i]["qty"];
               // dr["Unit"] = ds.Tables[0].Rows[i]["unit"];
               // if (ds.Tables["Showdata"].Rows[i]["Price"].ToString() != "")
               // {
                 //   totalX += Convert.ToInt32(ds.Tables["Showdata"].Rows[i]["Price"]);
              //  }
                myTable.Rows.Add(dr);
                itemCode = "";
            }
            this.dgvData.DataSource = myTable;
        }