using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SqlClient;
using System.Data;


namespace Wpf
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    /// 
   
    public partial class MainWindow : Window 
    {
        //对象和变量声明
        //Connection 对象和 Command对象
        private SqlConnection objSqlConnection;
        private SqlCommand objSqlCommand;
        //类变量
        private string insCmd;
        private string modCmd;
        private string delCmd;
        private string selCmd;
        public MainWindow()
        {
            InitializeComponent();
        }
        //在load事件中,进行连接
        private void frmFlight_Loaded(object sender, RoutedEventArgs e)
        {
            //初始化connecton对象
            objSqlConnection = new SqlConnection("server = QH-20150109JHTM; database =FirstDataBase; uid=sa;pwd=123456");
            if (objSqlConnection != null)
            {
                //MessageBox.Show("Connection Success");
            }
            //将座位数量添加到组合框中
            this.comboBoxSeats.Items.Clear();
            this.comboBoxSeats.Items.Add("100");
            this.comboBoxSeats.Items.Add("150");
            this.comboBoxSeats.Items.Add("200");
            //this.buttonModify.IsEnabled = false;
            //this.buttonDelete.IsEnabled = false;
        }

         //添加记录代码
        private void buttonAdd_Click(object sender, RoutedEventArgs e)
        {

            if (this.comboBoxSeats.SelectedIndex == -1)
            {
                MessageBox.Show("请选择座位数量");
                return;
            }
            //插入命令
            insCmd = "insert into FlightDetails values ("+this.textBoxFlight.Text+","+this.textBoxAirline.Text
                +","+this.comboBoxSeats.SelectedItem.ToString()+","+this.textBoxDestination.Text+","+this.textBoxSource.Text+
                ","+this.textBoxDeparture.Text+","+this.textBoxArrival.Text+")";  
            objSqlCommand = new SqlCommand();
            objSqlCommand.CommandText = insCmd;
            objSqlCommand.Connection = objSqlConnection;
            
            try
            {
                //打卡连接
                objSqlConnection.Open();
                //执行插入语句
                if (objSqlCommand.ExecuteNonQuery() == 1)
                {
                    MessageBox.Show("已经成功添加记录");
                }
                //启用和禁用按钮
                //this.buttonModify.IsEnabled = true;
                //this.buttonDelete.IsEnabled = true;
                //this.buttonAdd.IsEnabled = false;

            }
            catch (SqlException sqle)
            {
                //关闭连接
                MessageBox.Show(sqle.Message);
            }
            finally {

                objSqlConnection.Close();
            }
        }

        private void buttonDelete_Click(object sender, RoutedEventArgs e)
        {
           
            delCmd = "delete from FlightDetails where FlightCode like '" + this.textBoxFlight.Text + "'";
            MessageBox.Show(delCmd);
            objSqlCommand = new SqlCommand(delCmd, objSqlConnection);
            selCmd = "select Count(*) from FlightDetails";
            SqlCommand selCommand = new SqlCommand(selCmd,objSqlConnection);
            
            try
            {
                objSqlConnection.Open();
                string recordCount=selCommand.ExecuteScalar().ToString();
                MessageBox.Show("FlightDetails表中记录的数目是： " + recordCount);
                MessageBoxResult dialogResult=MessageBox.Show("您确定删除记录吗？","确认",MessageBoxButton.YesNo);
                if (dialogResult == MessageBoxResult.Yes) {
                    
                    if (objSqlCommand.ExecuteNonQuery() == 1) {
                        MessageBox.Show("已经删除记录");
                        this.buttonModify.IsEnabled = false;
                    }
                }

            }catch(Exception ehere)
            {
                MessageBox.Show(ehere.Message);
            }
            finally
            {
                objSqlConnection.Close();
            }
        }

        private void buttonSelect_Click(object sender, RoutedEventArgs e)
        {
            selCmd = "select * from FlightDetails where FlightCode like '" + this.textBoxFlight.Text + "'";
            MessageBox.Show(selCmd);
            try
            {
                objSqlConnection.Open();
                SqlCommand selFlightCommand = new SqlCommand(selCmd, objSqlConnection);
                SqlDataReader flightMessage = selFlightCommand.ExecuteReader();
                string columnName = null;
                //结果集列的名称
                for (int i = 0; i < flightMessage.FieldCount; ++i) {
                    columnName += flightMessage.GetName(i)+" ";
                }
                MessageBox.Show(columnName);
                //Read()方法负责前进到下一条记录
                while (flightMessage.Read()) {

                    this.textBoxAirline.Text = flightMessage[1].ToString();
                    this.comboBoxSeats.Text = flightMessage[2].ToString();
                    this.textBoxDestination.Text = flightMessage[3].ToString();
                    this.textBoxSource.Text = flightMessage[4].ToString();
                    
                    this.textBoxDeparture.Text = flightMessage[5].ToString();
                    this.textBoxArrival.Text = flightMessage[6].ToString();

                }
                //关闭DataReader对象
                flightMessage.Close();
            }
            catch (SqlException sqle)
            {
                MessageBox.Show(sqle.Message);
            }
            finally 
            {
                
                objSqlConnection.Close();
            }
        }
        //通过DataSet访问数据库数据源 
        private void buttonDataSet_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (this.textBoxFlight.Text == "") {
                    MessageBox.Show("航班号不能为空");
                    return;
                }
                objSqlConnection.Open();
                //string selString="select * from FlightDetails where  FlightCode like'"+this.textBoxFlight.Text+"'";
                string selString="select * from FlightDetails";
                SqlCommand myCommand = new SqlCommand(selString, objSqlConnection);
                SqlDataAdapter myAdaptor = new SqlDataAdapter(); 
                myAdaptor.SelectCommand = myCommand;
                DataSet myDataSet = new DataSet();
                myAdaptor.Fill(myDataSet);

                DataTable myTable = myDataSet.Tables[0];
                //MessageBox.Show(myDataSet.Tables[0].TableName);
                string columnName = null;
                foreach (DataColumn myColumn in myTable.Columns) {
                    columnName += myColumn.ColumnName+" ";
                }
                MessageBox.Show(columnName);
                string dataMessage = null;
                //第一个循环用于读取DataTable中的每一行,第二个循环用于输出行中的每一个字段的值
                foreach (DataRow myRow in myTable.Rows) {
                    foreach (DataColumn myColumn in myTable.Columns) {
                        dataMessage += myRow[myColumn] + " ";
                    }
                }
                MessageBox.Show(dataMessage);
               
            }
            catch (SqlException sqle)
            {
                MessageBox.Show(sqle.Message);
            }
            finally {
                objSqlConnection.Close();
            }
        }

       
    }
}

C# ADO.NET数据库操作