frmcodedemos.cs

< SQL Server2005程序设计>

using System;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Chapter08
{
    public partial class frmCodeDemos : Form
    {

        public frmCodeDemos()
        {
            InitializeComponent();
        }

        private String ConnString = Properties.Settings.Default.AdventureWorksConnectionString;

        private void btnLoadNormal_Click(object sender, EventArgs e)
        {
            SqlConnection cn = new SqlConnection(ConnString);
            SqlCommand cm = new SqlCommand("Select LastName, EmailAddress from Person.Contact", cn);
            SqlDataAdapter da = new SqlDataAdapter(cm);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dgvContacts.DataSource = dt;
        }


#region Query Notifications

        private delegate void RefreshData();

        private void btnNotifications_Click(object sender, EventArgs e)
        {
            LoadData();
        }

        private void LoadData()
        {
            SqlConnection cn = new SqlConnection(ConnString);
            SqlCommand cm = new SqlCommand("Select LastName, EmailAddress from Person.Contact", cn);
            SqlDataAdapter da = new SqlDataAdapter(cm);
            DataTable dt = new DataTable();

            SqlDependency sd = new SqlDependency(cm);
            sd.OnChange += new OnChangeEventHandler(ContactInfoChanged);
            SqlDependency.Start(ConnString);
            
            da.Fill(dt);

            dgvContacts.DataSource = dt;

        }

        private void ContactInfoChanged(object caller, SqlNotificationEventArgs e)
        {
            Invoke(new RefreshData(LoadData));
        }

        private void frmBindingTest_FormClosing(object sender, FormClosingEventArgs e)
        {
            SqlDependency.Stop(ConnString);
        }

#endregion


#region Async Query

        private SqlCommand _cm;
        private delegate void DataBind(DataTable dt);

        private void btnLoadAsync_Click(object sender, EventArgs e)
        {
            LoadDataAsync();
            MessageBox.Show("Data is loading...");
        }

        private void LoadDataAsync()
        {
            SqlConnection cn = new SqlConnection(Properties.Settings.Default.AdventureWorksConnectionStringAsync);
            
            _cm = new SqlCommand("Select LastName, EmailAddress from Person.Contact", cn);
            cn.Open();
            
            _cm.BeginExecuteReader(new AsyncCallback(Callback), new Object(), CommandBehavior.CloseConnection);
        }


        private void Callback(System.IAsyncResult oResult)
        {
            SqlDataReader dr = _cm.EndExecuteReader(oResult);
            DataTable dt = new DataTable();
            dt.Columns.Add("LastName", System.Type.GetType("System.String"));
            dt.Columns.Add("EmailAddress", System.Type.GetType("System.String"));

            while (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = dr[0];
                drow[1] = dr[1];
                dt.Rows.Add(drow);
            }
            dr.Close();

            Invoke(new DataBind(DataBindProc), dt);
        }

        private void DataBindProc(DataTable dt)
        {
            dgvContacts.DataSource = dt;
        }

#endregion

        private void btnBulkCopy_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;

            SqlConnection cn = new SqlConnection(ConnString);
            SqlCommand cm = new SqlCommand("uspGetNamesAndEmails", cn);
            cm.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cm);
            DataTable dt = new DataTable();

            da.Fill(dt);

            cn.Open();
            SqlCommand cmDelete = new SqlCommand("Delete from tblBulkData", cn);
            cmDelete.ExecuteNonQuery();

            SqlBulkCopy bdQueryData = new SqlBulkCopy(cn);
            bdQueryData.DestinationTableName = "tblBulkData";
            bdQueryData.WriteToServer(dt);

            bdQueryData.Close();
            cn.Close();
            
            this.Cursor = Cursors.Default;
            MessageBox.Show("Bulk copy complete!");
        }

        private void btnXML_Click(object sender, EventArgs e)
        {
            SqlConnection cn = new SqlConnection(ConnString);
            SqlCommand cm = new SqlCommand("Select AdditionalContactInfo from Person.Contact where ContactID = 1", cn); 
            cn.Open();
            SqlDataReader dr = cm.ExecuteReader(CommandBehavior.CloseConnection);

            if (dr.Read())
            {
                System.Xml.XmlReader xrContactInfo = dr.GetSqlXml(0).CreateReader();

                System.Xml.XmlDocument xdcContactInfo = new System.Xml.XmlDocument();
                xdcContactInfo.Load(xrContactInfo);
                MessageBox.Show(xdcContactInfo.InnerText);
            }
            dr.Close();
        }

        private void btnUDT_Click(object sender, EventArgs e)
        {
            SqlConnection cn = new SqlConnection(ConnString);
            SqlCommand cm = new SqlCommand("SELECT ID, ThePoint FROM tblPoint", cn);
            cn.Open();
            SqlDataReader dr = cm.ExecuteReader();

            while (dr.Read())
            {
                MessageBox.Show(String.Format("ID={0}, X={1}, Y={2}", dr[0], ((typPoint)dr[1]).X, ((typPoint)dr[1]).Y));
                MessageBox.Show(typPoint.Sum((typPoint)dr[1]).ToString());
            }

            dr.Close();
            cn.Close();
        }

    }
}