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();
}
}
}
留言列表
