resultsetsample.cs

远程数据访问RDA等

//---------------------------------------------------------------------
//  This file is part of the Microsoft .NET Framework SDK Code Samples.
// 
//  Copyright (C) Microsoft Corporation.  All rights reserved.
// 
//This source code is intended only as a supplement to Microsoft
//Development Tools and/or on-line documentation.  See these other
//materials for detailed information regarding Microsoft code samples.
// 
//THIS CODE AND INFORMATION ARE PROVIDED AS IS WITHOUT WARRANTY OF ANY
//KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
//IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//PARTICULAR PURPOSE.
//---------------------------------------------------------------------

#region Using directives

using System;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlServerCe;
using System.Text;
using System.IO;
using System.Text.RegularExpressions;

#endregion

namespace Microsoft.Samples.ResultSetSample
{
    /// <summary>
    /// Main Application Form Class.
    /// </summary>
    public partial class ResultSetSample : System.Windows.Forms.Form
    {
        #region Variables
        private SqlCeConnection connection = null;
        private SqlCeCommand command = null;
        private SqlCeResultSet resultSet = null;
        private ResultSetView view1 = null;
        private ResultSetView view2 = null;

        private DataTable table = null;
        #endregion

        //Constructor
        public ResultSetSample()
        {
            //
            // Required for Windows Form Designer support
            //
            InitializeComponent();

            // The connection is initialized just after the form is created
            if (null == this.connection)
            {
                string _connString = String.Format(System.Globalization.CultureInfo.InvariantCulture,
                @"Data Source = {0}\Northwind.sdf",Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase));
                this.connection = new SqlCeConnection(_connString);
                this.connection.Open();
                this.menuItemUpdatable.Checked = true;
            }
        }


        #region EVENT_HANDLERS
        /// <summary>
        /// Click Event handler for the Clear Menu Item
        /// </summary>
        private void menuItemClear_Click(object sender, EventArgs e)
        {
            //Dispose the existing Result Set
            if (null != this.resultSet)
                this.resultSet.Dispose();

            //Clear the DataSource of the DataGrid.
            this.dataGrid.DataSource = null;

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

        }

        /// <summary>
        /// Click Event handler for the Exit Menu Item
        ///  - Exits the Application
        /// </summary>
        private void menuItemExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        /// <summary>
        /// Click Event handler for the Use DataSet Menu Item
        ///  - Uses a DataSet for binding instead of the SqlCeResultSet
        ///  - if this menu item is checked
        /// </summary>
        private void menuItemUseDataSet_Click(object sender, EventArgs e)
        {
            this.menuItemUseDataSet.Checked = !(this.menuItemUseDataSet.Checked);
            if (menuItemUseDataSet.Checked)
            {
                this.menuItemUpdatable.Enabled = false;
            }
            else
            {
                this.menuItemUpdatable.Enabled = true;
            }

        }

        /// <summary>
        /// Click Event handler for the Use Updateable Menu Item
        ///  - Allows the SqlCeResultSet to be Updateable 
        ///  - if this menu item is checked
        /// </summary>
        private void menuItemUpdatable_Click(object sender, EventArgs e)
        {
            this.menuItemUpdatable.Checked = !(this.menuItemUpdatable.Checked);
        }

        /// <summary>
        /// Click Event handler for the Query and Bind button
        /// </summary>
        private void btnExecute_Click(object sender, EventArgs e)
        {
            //Clear the text in the filter textbox above the datagrid
            tbCommand.Text = String.Empty;
            // Disable the button till we are done quering and binding
            btnExecute.Enabled = false;
            try
            {

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

                // Dispose previous SqlCeCommand and previous SqlCeResultSet
                if (null != this.command) this.command.Dispose();
                if (null != this.resultSet) this.resultSet.Dispose();

                //Creates a Command with the associated connection
                this.command = this.connection.CreateCommand();

                // Use the SqlCeResultSet if the "Use DataSet" menu item is not
                // checked
                if (false == this.menuItemUseDataSet.Checked)
                {
                    // Queury the Orders table in the Northwind database
                    this.command.CommandText = "SELECT * FROM Orders";
                    ResultSetOptions options = ResultSetOptions.Scrollable | ResultSetOptions.Sensitive;

                    if (this.menuItemUpdatable.Checked) options |= ResultSetOptions.Updatable;

                    this.resultSet = this.command.ExecuteResultSet(options);

                    this.dataGrid.DataSource = null;
                    // Bind the result set to the controls
                    this.BindData();
                }
                else
                {
                    //Retrieve the columns we are interested in from the Orders table
                    //Note that we do not specify this in the SqlCeResultSet queury above
                    // because we demonstrate the use of the Ordinals property in the 
                    // ResultSetView.
                    string query = @"SELECT [Customer ID], [Ship Name],[Ship City]
                                    ,[Ship Country] FROM Orders";
                    this.command.CommandText = query;
                    table = new DataTable("Orders");
                    table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                    SqlCeDataAdapter adapter = new SqlCeDataAdapter(this.command);

                    adapter.FillSchema(table, SchemaType.Source);
                    adapter.Fill(table);

                    this.dataGrid.DataSource = null;
                    this.BindData();
                }
                btnExecute.Enabled = true;
            }
            catch (InvalidOperationException ex)
            {
                btnExecute.Enabled = true;
                MessageBox.Show(String.Format(System.Globalization.CultureInfo.CurrentCulture,
                    "Exception while Performing Query/Bind: \n {0}",ex.ToString()));
            }
            catch (SqlCeException ex)
            {
                btnExecute.Enabled = true;
                ShowErrors(ex);
            }
        }
        /// <summary>