?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.Data.OleDb;

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

        private void button1_Click(object sender, EventArgs e)
        {
            string ConnectionString;

            if (ServiceIp.Text.Trim() == "")
            {
                MessageBox.Show("請填寫服務IP地址或者服務器名稱！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (dataName.Text.Trim() == "")
            {
                MessageBox.Show("請填寫相對應的數據庫名稱！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            ConnectionString = "database=" + dataName.Text.Trim() + ";Server=" + ServiceIp.Text.Trim() + "," + ServicePort.Text.Trim() + ";User ID=" + UserName.Text.Trim() + ";Password=" + PassWord.Text.Trim() + ";Persist Security Info=True";


            SqlConnection mySqlConncetion = new SqlConnection(ConnectionString);

            try
            {
                mySqlConncetion.Open();
                MessageBox.Show("連接測試成功", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch
            {
                MessageBox.Show("連接失敗！", "出錯了！", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                mySqlConncetion.Close();
            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                ExcelFileName.Text = openFileDialog1.FileName;
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string ConnectionString = "database=" + dataName.Text.Trim() + ";Server=" + ServiceIp.Text.Trim() + "," + ServicePort.Text.Trim() + ";User ID=" + UserName.Text.Trim() + ";Password=" + PassWord.Text.Trim() + ";Persist Security Info=True";
            TransferData(ExcelFileName.Text.ToString().Trim(), "Sheet1", ConnectionString);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="excelFile"></param>
        /// <param name="sheetName"></param>
        /// <param name="connectionString"></param>
        private void TransferData(string excelFile, string sheetName, string connectionString)
        {
            DataSet ds = new DataSet();
            try
            {
                string ExcelStrConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection ExcelConn = new OleDbConnection(ExcelStrConn);
                ExcelConn.Open();
                string StrExcel = string.Format("select * from [{0}$]", sheetName);
                OleDbDataAdapter adapter = new OleDbDataAdapter(StrExcel, ExcelConn);
                adapter.Fill(ds, sheetName);

                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();

                progressBar1.Maximum = ds.Tables[0].Rows.Count;
                progressBar1.Minimum = 0;
                progressBar1.Step = 1;
                SqlCommand comm = null;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    progressBar1.Value++;
                    string CompanyName = dr[0].ToString().Trim();
                    if (CompanyName == "")
                        continue;
                    comm = new SqlCommand("select count(*) from EnterpriseDirectory where CompanyName='" + CompanyName + "'", conn);
                    if (Convert.ToInt16(comm.ExecuteScalar())==0)
                    {
                        comm = new SqlCommand("insert into EnterpriseDirectory(CompanyName,Contacts,ContactsPosts,Corporate,Tel,Phone,Fax,Address,Zip,NumberOfEmployees,Established,Annualturnover,Email,MainProducts,MainIndustry,CompanyType,BusinessModel,RegisteredCapital,RegisteredAddress,HomeUrl,CompanyProfile) values(@CompanyName,@Contacts,@ContactsPosts,@Corporate,@Tel,@Phone,@Fax,@Address,@Zip,@NumberOfEmployees,@Established,@Annualturnover,@Email,@MainProducts,@MainIndustry,@CompanyType,@BusinessModel,@RegisteredCapital,@RegisteredAddress,@HomeUrl,@CompanyProfile)", conn);

                        SqlParameter mypar = new SqlParameter("@CompanyName", CompanyName);
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@Contacts", dr[1].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@ContactsPosts", dr[2].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@Corporate", dr[3].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@Tel", dr[4].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@Phone", dr[5].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@Fax", dr[6].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@Address", dr[7].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@Zip", dr[8].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@NumberOfEmployees", dr[9].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@Established", dr[10].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@Annualturnover", dr[11].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@Email", dr[12].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@MainProducts", dr[13].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@MainIndustry", dr[14].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@CompanyType", dr[15].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@BusinessModel", dr[16].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@RegisteredCapital", dr[17].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@RegisteredAddress", dr[18].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@HomeUrl", dr[19].ToString().Trim());
                        comm.Parameters.Add(mypar);
                        mypar = new SqlParameter("@CompanyProfile", dr[20].ToString().Trim());
                        comm.Parameters.Add(mypar);

                        comm.ExecuteNonQuery();

                    }
                }
                comm.Dispose();
                conn.Dispose();
                conn.Close();
                ExcelConn.Dispose();
                ExcelConn.Close();

                //如果數據庫里沒有表，就創建表
                //string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);
                //foreach (DataColumn c in ds.Tables[0].Columns)
                //{
                //    strSql += string.Format("[{0}] text,", c.ColumnName);
                //}
                //strSql = strSql.Trim(',') + ")";

                //using (SqlConnection sqlconn = new SqlConnection(connectionString))
                //{
                //    sqlconn.Open();
                //    SqlCommand command = sqlconn.CreateCommand();
                //    command.CommandText = strSql;
                //    command.ExecuteNonQuery();
                //    sqlconn.Close();
                //}
                //用bcp導入數據庫
                //using (SqlBulkCopy bcp = new SqlBulkCopy(connectionString))
                //{

                //    bcp.SqlRowsCopied += new SqlRowsCopiedEventHandler(bcp_SqlRowCopied);
                //    bcp.BatchSize = 100;
                //    bcp.NotifyAfter = 100;
                //    bcp.DestinationTableName = sheetName;
                //    bcp.WriteToServer(ds.Tables[0]);

                //}


                MessageBox.Show("數據導入成功!", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
                Application.Exit();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString(), "出錯", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }
        
        /// <summary>
        /// 進度條
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void bcp_SqlRowCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            this.Text = e.RowsCopied.ToString();
            this.Update();
        }

    }
}