学习笔记ADO.Net方面

创建table 
create table customers1 as select * from customers   --order 数据库可行

select * into customers1 from customers      ---ms 可行

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.Data.SqlClient;
using System.Threading;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
using System.Xml;

namespace DataSetTest2005
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();

            ds.Tables.Add("BigTable");
            ds.Tables[0].Columns.Add("ID", Type.GetType("System.Int32"));
            ds.Tables[0].Columns["ID"].Unique = true;
            ds.Tables[0].Columns.Add("Value", Type.GetType("System.Int32"));

            Cursor.Current = Cursors.WaitCursor;

            DateTime datBegin = DateTime.Now;

            Random rand = new Random();
            int i, intValue;
            DataRow dr;

            for (i = 1; i <= 500000; i++)
            {
                try
                {
                    intValue = rand.Next();

                    dr = ds.Tables[0].NewRow();

                    dr["ID"] = intValue;
                    dr["Value"] = intValue;

                    ds.Tables[0].Rows.Add(dr);
                }
                catch { }
            }

            Cursor.Current = Cursors.Default;

            MessageBox.Show("Elapsed Time: " + (DateTime.Now - datBegin).Seconds.ToString());
            MessageBox.Show("count = " + ds.Tables[0].Rows.Count.ToString());

        }

        private void button2_Click(object sender, EventArgs e)
        {
            //// obtain connection strings from configuration files or
            //// similar facility
            //// NOTE: these connection strings have to include "async=true", for
            //// example:
            //// "server=myserver;database=mydb;integrated security=true;async=true"
            //string connstrAccouting = GetConnString("accounting");
            //string connstrHR = GetConnString("humanresources");
            //// define two connection objects, one for each database
            //using (SqlConnection connAcc = new SqlConnection(connstrAccounting))
            //using (SqlConnection connHumanRes = new SqlConnection(connstrHR))
            //{
            //    // open the first connection
            //    connAcc.Open();
            //    // start the execution of the first query contained in the
            //    // "employee_info" stored-procedure
            //    SqlCommand cmdAcc = new SqlCommand("employee_info", connAcc);
            //    cmdAcc.CommandType = CommandType.StoredProcedure;
            //    cmdAcc.Parameters.AddWithValue("@empl_id", employee_id);
            //    IAsyncResult arAcc = cmdAcc.BeginExecuteReader();
            //    // at this point, the "employee_info" stored-proc is executing on
            //    // the server, and this thread is running at the same time
            //    // now open the second connection
            //    connHumanRes.Open();
            //    // start the execution of the second stored-proc against
            //    // the human-resources server
            //    SqlCommand cmdHumanRes = new SqlCommand("employee_hrinfo",
            //                                            connHumanRes);
            //    cmdHumanRes.Parameters.AddWithValue("@empl_id", employee_id);
            //    IAsyncResult arHumanRes = cmdHumanRes.BeginExecuteReader();
            //    // now both queries are running at the same time
            //    // at this point; more work can be done from this thread, or we
            //    // can simply wait until both commands finish - in our case we'll
            //    // wait
            //    SqlDataReader drAcc = cmdAcc.EndExecuteReader(arAcc);
            //    SqlDataReader drHumanRes = cmdHumanRes.EndExecuteReader(arHumanRes);
            //    // now we can render the results, for example, bind the readers to an ASP.NET
            //    // web control, or scan the reader and draw the information in a
            //    // WebForms form.
            //}

            string custid = "ALFKI";
            string orderid = "10643";

            // NOTE: connection strings denoted by "connstring" have to include
            // "async=true", for example:
            string connstring = "server=(local);database=northwind;integrated security=true;async=true";
            // we'll use three connections for this
            using (SqlConnection c1 = new SqlConnection(connstring))
            using (SqlConnection c2 = new SqlConnection(connstring))
            using (SqlConnection c3 = new SqlConnection(connstring))
            {
                // get customer info
                c1.Open();
                SqlCommand cmd1 = new SqlCommand(
                  "SELECT CustomerID, CompanyName, ContactName FROM Customers WHERE CustomerID=@id", c1);
                cmd1.Parameters.Add("@id", SqlDbType.Char, 5).Value = custid;
                IAsyncResult arCustomer = cmd1.BeginExecuteReader();
                // get orders
                c2.Open();
                SqlCommand cmd2 = new SqlCommand("SELECT * FROM Orders WHERE CustomerID=@id", c2);
                cmd2.Parameters.Add("@id", SqlDbType.Char, 5).Value = custid;
                IAsyncResult arOrders = cmd2.BeginExecuteReader();
                // get order detail if user picked an order
                IAsyncResult arDetails = null;
                SqlCommand cmd3 = null;
                if (null != orderid)
                {
                    c3.Open();
                    cmd3 = new SqlCommand("SELECT * FROM [Order Details] WHERE OrderID=@id", c3);
                    cmd3.Parameters.Add("@id", SqlDbType.Int).Value = int.Parse(orderid);
                    arDetails = cmd3.BeginExecuteReader();
                }
                // build the wait handle array for WaitForMultipleObjects
                WaitHandle[] handles = new WaitHandle[null == arDetails ? 2 : 3];
                handles[0] = arCustomer.AsyncWaitHandle;
                handles[1] = arOrders.AsyncWaitHandle;
                if (null != arDetails)
                    handles[2] = arDetails.AsyncWaitHandle;
                // wait for commands to complete and render page controls as we
                // get data back
                SqlDataReader r;
                DataTable dt;
                for (int results = (null == arDetails) ? 1 : 0; results < 3; results++)
                {
                    // wait for any handle, then process results as they come
                    int index = WaitHandle.WaitAny(handles, 5000, false); // 5 secs
                    if (WaitHandle.WaitTimeout == index)
                        throw new Exception("Timeout");
                    switch (index)
                    {
                        case 0: // customer query is ready
                            r = cmd1.EndExecuteReader(arCustomer);
                            if (!r.Read())
                                continue;
                            lblCustomerID.Text = r.GetString(0);
                            lblCompanyName.Text = r.GetString(1);
                            lblContact.Text = r.GetString(2);
                            r.Close();
                            break;
                        case 1: // orders query is ready
                            r = cmd2.EndExecuteReader(arOrders);
                            dt = new DataTable();
                            dt.Load(r);
                            dgOrders.DataSource = dt; // data-bind to the orders grid
                            dgOrders.Refresh();
                            r.Close();
                            break;
                        case 2: // details query is ready
                            r = cmd3.EndExecuteReader(arDetails);
                            dt = new DataTable();
                            dt.Load(r);
                            dgDetails.DataSource = dt; // data-bind to the details grid
                            dgDetails.Refresh();
                            r.Close();
                            break;

                    }
                }
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button3_Click(object sender, EventArgs e)
        {
            string connstr = "server=(local);database=northwind;integrated security=true;async=true";
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            SqlCommand cmd1 = new SqlCommand("select * from customers", conn);
            SqlCommand cmd2 = new SqlCommand("select * from orders", conn);
            SqlDataReader rdr1 = cmd1.ExecuteReader();
            // next statement causes an error prior to SQL Server 2005
            SqlDataReader rdr2 = cmd2.ExecuteReader();
            // now you can reader from rdr1 and rdr2 at the same time.

        }

        private void button4_Click(object sender, EventArgs e)
        {
            string connstr = "server=(local);database=northwind;integrated security=true;async=true";
            // Fill up a DataSet
            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection(connstr);
            SqlDataAdapter dadp = new SqlDataAdapter("select * from customers", conn);
            dadp.Fill(ds);
            // Copy the Data to SqlServer
            SqlBulkCopy bcp = new SqlBulkCopy(connstr);          
            bcp.DestinationTableName = "customers1";
            bcp.WriteToServer(ds.Tables[0]);
        }

        //序列化
        private void button5_Click(object sender, EventArgs e)
        {
            string connstr = "server=(local);database=northwind;integrated security=true;async=true";

            DataSet ds = new DataSet();
            SqlDataAdapter dadpt = new SqlDataAdapter("select * from [order details]", connstr);
            dadpt.Fill(ds);

            BinaryFormatter bf = new BinaryFormatter();
            FileStream fs = new FileStream(@"c:\xml1.txt",FileMode.OpenOrCreate);

            ds.RemotingFormat = SerializationFormat.Binary;

            bf.Serialize(fs,ds);                       
        }

        private void button6_Click(object sender, EventArgs e)
        {
            string connstr = "server=(local);database=northwind;integrated security=true;async=true";
            SqlDataAdapter dadpt = new SqlDataAdapter("select * from [order details]", connstr);
            DataTable dt = new DataTable("Customer");
            dadpt.Fill(dt);

            dt.WriteXml(@"c:\DataTable.xml",true);
            dt.WriteXmlSchema(@"c:\DataTableSchema.xml");
        }

        private void button7_Click(object sender, EventArgs e)
        {
            string connstr = "server=(local);database=northwind;integrated security=true;async=true";
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            SqlCommand cmd = new SqlCommand("select * from [order details]", conn);
            SqlDataReader dr = cmd.ExecuteReader();

            DataTable dt = new DataTable("Customer");
            dt.Load(dr);    //load 把dr中的数据都写到dt中
           
            this.dataGridView1.DataSource = dt;

        }

        private void button8_Click(object sender, EventArgs e)
        {
            StreamReader sr = new StreamReader(@"C:\DataTableSchema.xml");

            DataTable dt = new DataTable();
            dt.ReadXmlSchema(sr);

            dt.ReadXml(new StreamReader(@"c:\dataTable.xml"));

            this.dataGridView1.DataSource = dt;
        }

        private void button9_Click(object sender, EventArgs e)
        {
            string connstr = "server=(local);database=northwind;integrated security=true;async=true";
            SqlDataAdapter dadpt = new SqlDataAdapter("select * from customers", connstr);
            DataTable dt = new DataTable("Customer");
            dadpt.Fill(dt);

            SqlDataAdapter dadpt1 = new SqlDataAdapter("select * from customers", connstr);
            DataTable dt1 = new DataTable("Customer1");
            dadpt1.Fill(dt1);

            dt.Merge(dt1);  //合并表

            this.dataGridView1.DataSource = dt;
        }

        private void button10_Click(object sender, EventArgs e)
        {
         }

    }
}