c# 一次性插入多条数据
private void Form1_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection("Server=.;database=northwind;uid=sa"))
{
SqlDataAdapter sda = new SqlDataAdapter("Select top 5 * from Employees", conn);
sda.Fill(dt);
}
dataGridView1.DataSource = dt;
}
public bool ExecuteNone(string[] sql)
{
bool result;
SqlConnection conn = new SqlConnection("Server=.;database=tempdb;uid=sa;pwd=;");
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
try
{
for (int i = 0; i < sql.Length; i++)
{
if (sql[i] == null || sql[i] == "")
{
continue;
}
cmd.CommandText = sql[i];
cmd.ExecuteNonQuery();
}
tran.Commit();
result = true;
}
catch (System.Exception)
{
tran.Rollback();
result = false;
}
conn.Close();
return result;
}
private void button1_Click(object sender, EventArgs e)
{
string[] sql = new string[dataGridView1.Rows.Count];
int index = 0;
foreach (DataGridViewRow gvr in dataGridView1.Rows)
{
if (gvr.Cells[0].Value == null)
{
continue;
}
sql[index] = "INSERT INTO EmployeesCopy (employeeid,firstname,lastname) VALUES (" + gvr.Cells[0].Value + ",'" + gvr.Cells[1].Value + "','" + gvr.Cells[2].Value + "')";
index++;
}
if (ExecuteNone(sql))
{
MessageBox.Show("插入成功!");
}
else
{
MessageBox.Show("插入失败!");
}
}
//就可以说是多条插入的例子了。你鉴赏鉴赏!!
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection("Server=.;database=northwind;uid=sa"))
{
SqlDataAdapter sda = new SqlDataAdapter("Select top 5 * from Employees", conn);
sda.Fill(dt);
}
dataGridView1.DataSource = dt;
}
public bool ExecuteNone(string[] sql)
{
bool result;
SqlConnection conn = new SqlConnection("Server=.;database=tempdb;uid=sa;pwd=;");
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
try
{
for (int i = 0; i < sql.Length; i++)
{
if (sql[i] == null || sql[i] == "")
{
continue;
}
cmd.CommandText = sql[i];
cmd.ExecuteNonQuery();
}
tran.Commit();
result = true;
}
catch (System.Exception)
{
tran.Rollback();
result = false;
}
conn.Close();
return result;
}
private void button1_Click(object sender, EventArgs e)
{
string[] sql = new string[dataGridView1.Rows.Count];
int index = 0;
foreach (DataGridViewRow gvr in dataGridView1.Rows)
{
if (gvr.Cells[0].Value == null)
{
continue;
}
sql[index] = "INSERT INTO EmployeesCopy (employeeid,firstname,lastname) VALUES (" + gvr.Cells[0].Value + ",'" + gvr.Cells[1].Value + "','" + gvr.Cells[2].Value + "')";
index++;
}
if (ExecuteNone(sql))
{
MessageBox.Show("插入成功!");
}
else
{
MessageBox.Show("插入失败!");
}
}
//就可以说是多条插入的例子了。你鉴赏鉴赏!!