using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;
namespace WindowsFormsApp1
{
public partial class SQLServer高效插入 : Form
{
public string dbCon = ConfigurationManager.AppSettings["conn"];
DataTable dtAll = new DataTable();
DataTable dt1 = new DataTable();
public SQLServer高效插入()
{
InitializeComponent();
//全部数据
dtAll = loadUserinfo();
dgv1.DataSource = dtAll;
//禁止列排序
for (int i = 0; i < dgv1.Columns.Count; i++)
{
dgv1.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable;
}
//linq 选择部分数据
IEnumerable<DataRow> data2 = from r in dtAll.AsEnumerable()
where r.Field<string>("UserName") == "李四"
select r;
dgv2.DataSource = data2.CopyToDataTable();
//Linq 增加一些信息,自定义列
var datas3 = from r in dtAll.AsEnumerable()
select new //自定义列
{
序号 = r.Field<int>("UserId").ToString(),
姓名 = r.Field<string>("UserName"),
密码 = r.Field<string>("UserPwd"),
密码new = r.Field<string>("UserPwd") + r.Field<string>("UserName")//
};
dgv3.DataSource = datas3.ToList();
}
private DataTable loadUserinfo()
{
DataTable dataTable = new DataTable();
SqlConnection sqlConn = new SqlConnection(dbCon);
sqlConn.Open();
string sql = "select * from UserInfo";
SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlConn);
adapter.Fill(dataTable);
return dataTable;
}
//事务插入
private void btnInsert_Click(object sender, EventArgs e)
{
SqlConnection sqlConn = new SqlConnection(dbCon);
sqlConn.Open();
//计时
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
//开启事务
SqlTransaction sqlTransaction = sqlConn.BeginTransaction();
DateTime dtnow = DateTime.Now;
for (int i = 0; i < 5000; i++)
{
SqlCommand cmd = new SqlCommand($"insert into StudentInfo (StuName,Phone,ClassId,Sex) values('wyh','{i.ToString()}',2,'男')", sqlConn, sqlTransaction);
cmd.ExecuteNonQuery();
}
//执行事务
sqlTransaction.Commit();
stopwatch.Stop();
MessageBox.Show("耗时(秒):" + ((float)stopwatch.ElapsedMilliseconds / 1000).ToString());
}
//批量插入
private void btnInsertBulk_Click(object sender, EventArgs e)
{
SqlConnection sqlConn = new SqlConnection(dbCon);
sqlConn.Open();
//计时
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
//开启事务
DataTable dataTable = new DataTable();
dataTable.Columns.Add("StuId");
dataTable.Columns.Add("StuName");
dataTable.Columns.Add("Phone");
dataTable.Columns.Add("ClassId");
dataTable.Columns.Add("CreateTime");
dataTable.Columns.Add("Sex");
for (int i = 0; i < 5000; i++)
{
dataTable.Rows.Add(i, "wyh", i.ToString(), "1", DateTime.Now, "男");
}
sqlBulkCopyByDatatable(dbCon, "StudentInfo", dataTable);
stopwatch.Stop();
MessageBox.Show("耗时(秒):" + ((float)stopwatch.ElapsedMilliseconds / 1000).ToString());
}
//批量拷贝
void sqlBulkCopyByDatatable(string conString, string dataTable, DataTable dt)
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conString, SqlBulkCopyOptions.UseInternalTransaction))
{
try
{
sqlBulkCopy.DestinationTableName = dataTable;
for (int i = 0; i < dt.Columns.Count; i++)
{
sqlBulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
sqlBulkCopy.WriteToServer(dt);
}
catch (Exception)
{
throw;
}
}
}
}
}