resultsetsample.cs

远程数据访问RDA等

        /// Text Changed event handler for the tbCommand TextBox
        /// - In this handler the data displayed in the grid is filtered
        /// - on the Ship Name column according to what is typed in the TextBox
        /// </summary>
        private void tbCommand_TextChanged(object sender, EventArgs e)
        {
            try
            {
                if (false == this.menuItemUseDataSet.Checked)
                {
                    if (null == this.resultSet)
                    {
                        MessageBox.Show("Command hasn't been executed. Press execute button first");
                        return;
                    }
                    ResultSetOptions options = ResultSetOptions.Scrollable | ResultSetOptions.Sensitive;
                    if (this.menuItemUpdatable.Checked) options |= ResultSetOptions.Updatable;

                    // Query the database again using the WHERE clause to filter according
                    // to the input in the text box
                    string query = String.Format(System.Globalization.CultureInfo.InvariantCulture,
                        "SELECT * FROM Orders WHERE [Ship Name] LIKE '{0}%' ", tbCommand.Text);
                    
                    this.command.CommandText = query;

                    this.resultSet = this.command.ExecuteResultSet(options);
                    this.BindData();
                }
                else
                {
                    if (null == this.table)
                    {
                        MessageBox.Show("Command hasn't been executed. Press execute button first");
                        return;
                    }
                    //Row filters are used to filter the table since the entire table
                    // is already loaded in memory
                    string filterExpression = String.Format(System.Globalization.CultureInfo.InvariantCulture,"[Ship Name] LIKE '{0}%' ", tbCommand.Text);
                    table.DefaultView.RowFilter = filterExpression;
                    this.BindData();
                }
            }
            catch (SqlCeException ex)
            {
                ShowErrors(ex);
            }
        }

        /// <summary>
        /// Click Event handler for the Previous button
        /// Decrements the position of the BindingManagerBase that is associated
        /// with the DataSource in use. If the cursor is already at the beginning 
        /// of the list it remains there
        /// </summary>
        private void btnPrev_Click(object sender, EventArgs e)
        {
            if (false == this.menuItemUseDataSet.Checked)
            {
                this.BindingContext[this.view2].Position -= 1;
            }
            else
            {
                this.BindingContext[this.table].Position -= 1;
            }
        }
        /// <summary>
        /// Click Event handler for the Next button
        /// Increments the position of the BindingManagerBase that is associated
        /// with the DataSource in use. If the cursor is already at the end 
        /// of the list it remains there
        /// </summary>
        private void btnNext_Click(object sender, EventArgs e)
        {
            if (false == this.menuItemUseDataSet.Checked)
            {
                this.BindingContext[this.view2].Position += 1;
            }
            else
            {
                this.BindingContext[this.table].Position += 1;
            }
        }

        #endregion

        /// <summary>
        /// Binds the SqlCeResultSet or DataSet to the controls in the UI
        /// </summary>
        private void BindData()
        {
                //Clear any existing bindings
                this.textBox1.DataBindings.Clear();
                this.textBox2.DataBindings.Clear();
                this.textBox3.DataBindings.Clear();
                this.textBox4.DataBindings.Clear();

                //Clear the text in the text boxes
                this.textBox1.Text = string.Empty;
                this.textBox2.Text = string.Empty;
                this.textBox3.Text = string.Empty;
                this.textBox4.Text = string.Empty;

                if (false == this.menuItemUseDataSet.Checked)
                {
                    if (null == this.resultSet)
                    {
                        MessageBox.Show("SQL Command has not been executed.Press execute button first");
                        return;
                    }

                    // Dispose previous views bound to the currently active RS
                    //
                    if (null != view1) ((IDisposable)view1).Dispose();
                    if (null != view2) ((IDisposable)view2).Dispose();

                    //Bind the data grid control
                    this.view1 = this.resultSet.ResultSetView;

                    //This array contains the ordinals of the columns displayed in the grid
                    //Currently it is set to display only columns 1,3,5 and 8
                    int[] ordinals = new int[] { 1,3,5,8};
                    this.view1.Ordinals = ordinals;

                    this.dataGrid.DataSource = view1;

                    // Bind individual text boxes 
                    this.view2 = this.resultSet.ResultSetView;

                    this.textBox1.DataBindings.Add(
                            "text", view2, resultSet.GetSqlMetaData(1).Name);

                    this.textBox2.DataBindings.Add(
                            "text", view2, resultSet.GetSqlMetaData(3).Name);

                    this.textBox3.DataBindings.Add(
                        "text", view2, resultSet.GetSqlMetaData(5).Name);

                    this.textBox4.DataBindings.Add(
                        "text", view2, resultSet.GetSqlMetaData(8).Name);

                }
                else
                {
                    if (null == this.table)
                    {
                        MessageBox.Show("SQL Command has not been executed.Press execute button first");
                        return;
                    }
                    // Binding the DataGrid to the DefaultView of the DataTable
                    this.dataGrid.DataSource = table.DefaultView;

                    //Bind the individual text boxes
                    this.textBox1.DataBindings.Add(
                        "text", table, table.Columns[0].ColumnName);

                    this.textBox2.DataBindings.Add(
                        "text", table, table.Columns[1].ColumnName);

                    this.textBox3.DataBindings.Add(
                        "text", table, table.Columns[2].ColumnName);

                    this.textBox4.DataBindings.Add(
                        "text", table, table.Columns[3].ColumnName);
                }
        }

        #region HELPER functions
        /// <summary>
        /// Displays the Details of a SqlCeException
        /// </summary>
        public static void ShowErrors(SqlCeException e)
        {
            SqlCeErrorCollection errorCollection = e.Errors;

            StringBuilder bld = new StringBuilder();
            Exception inner = e.InnerException;

            foreach (SqlCeError err in errorCollection)
            {
                bld.Append("\n Error Code: " + err.HResult.ToString("X",
                    System.Globalization.CultureInfo.CurrentCulture));
                bld.Append("\n Message   : " + err.Message);
                bld.Append("\n Minor Err.: " + err.NativeError);
                bld.Append("\n Source    : " + err.Source);

                foreach (int numPar in err.NumericErrorParameters)
                {
                    if (0 != numPar) bld.Append("\n Num. Par. : " + numPar);
                }

                foreach (string errPar in err.ErrorParameters)
                {
                    if (String.Empty != errPar) bld.Append("\n Err. Par. : " + errPar);
                }
            }
            MessageBox.Show(bld.ToString());
        }
        #endregion

    }
}