昶達的部落格

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace WindowsFormsTEST3

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void Form1_Load(object sender, EventArgs e)

        {

            //製造dataGridView的100筆資料

            dataGridView1.Columns.Add("LastName", "LastName");

            dataGridView1.Columns.Add("FirstName", "FirstName");

            dataGridView1.Columns.Add("Title", "Title");

            dataGridView1.Columns.Add("Address", "Address");

            dataGridView1.Columns.Add("City", "City");

            for (var i = 0; i < 100; i++)

            {

                dataGridView1.Rows.Add(string.Format("LastName_test{0}", i), string.Format("First{0}", i), string.Format("Title{0}", i), string.Format("Address{0}", i), string.Format("City{0}", i));

            }

        }

        private void button1_Click(object sender, EventArgs e)

        {

            SqlConnection mySQLConnection = new SqlConnection("User ID=sa;password=password;Initial Catalog=NorthWind;Data Source=192.168.1.1");

            mySQLConnection.Open();

            string mySQLQuery0 = null;

            SqlCommand mySQLCommand0 = new SqlCommand();

            //先刪除資料

            mySQLQuery0 = "Delete from Employees where LastName LIKE 'LastName_test%'";

            mySQLCommand0.Connection = mySQLConnection;

            mySQLCommand0.CommandText = mySQLQuery0;

            mySQLCommand0.ExecuteNonQuery();

            SqlTransaction tran;

            tran = mySQLConnection.BeginTransaction();

            //寫入資料

            for (int i = 0; i < dataGridView1.RowCount; i++)

            {

                try

                {

                    if (dataGridView1.Rows[i].Cells[0].Value != null)

                    {

                        mySQLCommand0 = new SqlCommand("INSERT INTO Employees (LastName, FirstName, Title, Address, City) VALUES (@LastName, @FirstName, @Title, @Address, @City)", mySQLConnection);

                        mySQLCommand0.Parameters.AddWithValue("@LastName", dataGridView1.Rows[i].Cells[0].Value.ToString());

                        mySQLCommand0.Parameters.AddWithValue("@FirstName", dataGridView1.Rows[i].Cells[1].Value.ToString());

                        mySQLCommand0.Parameters.AddWithValue("@Title", dataGridView1.Rows[i].Cells[2].Value.ToString());

                        mySQLCommand0.Parameters.AddWithValue("@Address", dataGridView1.Rows[i].Cells[3].Value.ToString());

                        mySQLCommand0.Parameters.AddWithValue("@City", dataGridView1.Rows[i].Cells[4].Value.ToString());

                        mySQLCommand0.Transaction = tran;

                        mySQLCommand0.ExecuteNonQuery();

                    }

                }

                catch (Exception ex)

                {

                    MessageBox.Show(ex.ToString());

                }

            }

            tran.Commit();

            mySQLConnection.Close();

        }

    }

}