测试窗体为：
直接上代码：
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.Data.SqlClient;
using System.Diagnostics;
namespace SqlDatabase
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 初始化参数，数据库连接字段
/// </summary>
/// //Windows身份验证Sql连接语句为:"Data Source=DESKTOP-KJ6QD3R;Initial Catalog=Test2;Integrated Security=True";
///数据库用户密码验证：string connectionString="server=.;database=Sql;uid=sa; pwd=123456";
public static string StrConnec = "Data Source=DESKTOP-KJ6QD3R;Initial Catalog=Test2;Integrated Security=True";
public static string StrConnec1 = "Data Source=DESKTOP-KJ6QD3R;Initial Catalog=Test2;uid=sa; pwd=614823";
SqlConnection Sqlcon = new SqlConnection(StrConnec1);
DataTable DT = null;
/// <summary>
/// 测试是否连接数据库成功
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
try
{
Sqlcon.Open();
MessageBox.Show("连接成功");
}
catch
{
MessageBox.Show("连接失败");
}
}
/// <summary>
/// 查询整表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_query_Click(object sender, EventArgs e)
{
//Access 语句为： string sql = "select * from UserInfo "，可以很明显的看出两者的区别
string sql = "use Test2 SELECT * FROM tb1 ";
SqlDataAdapter Sqladpter = new SqlDataAdapter(sql, Sqlcon);
DT = new DataTable();
Sqladpter.Fill(DT);
dataGridView1.DataSource = DT;
}
/// <summary>
/// 查询满足条件的内容
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_conditionquery_Click(object sender, EventArgs e)
{
//************************条件查询语句*************************
/*
* 只显示固定例："use Test2 SELECT name ,sex FROM tb1" ;
* 保留country唯一值： SELECT DISTINCT country FROM Websites;
* 精确=查询："use Test2 SELECT * FROM tb1 where name = ‘liu‘";
* 两个都成立条件查询：SELECT * FROM Websites WHERE country=‘CN‘AND alexa > 50;
* 两个成立一个条件查询：SELECT * FROM Websites WHERE country=‘CN‘or alexa > 50;
* 查询并根据一个列值排序：SELECT * FROM Websites ORDER BY alexa; order by A ,B desc/asc(根据列名降序/升序排列)
* */
string sql = "use Test2 SELECT * FROM tb1 where name = ‘liu‘";
SqlDataAdapter Sqladpter = new SqlDataAdapter(sql, Sqlcon);
DT = new DataTable();
Sqladpter.Fill(DT);
dataGridView1.DataSource = DT;
}
/// <summary>
/// 只查询固定字段
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
string sql = "use Test2 SELECT name ,sex FROM tb1";
SqlDataAdapter Sqladpter = new SqlDataAdapter(sql, Sqlcon);
DT = new DataTable();
Sqladpter.Fill(DT);
dataGridView1.DataSource = DT;
}
Stopwatch WatchDog = new Stopwatch();
/// <summary>
/// 单条插入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_insert_Click(object sender, EventArgs e)
{
//Use Test2 insert into tb1 (name,sex,birthday,birthplace)values(‘" + "name0" + "‘,‘" + "sex0" + "‘,‘" + "birthpday0" + "‘,‘" + "birthplace" + "‘)";
string sql = " insert into [Test2].[dbo].[tb1] (name,sex,birthday,birthplace)values(‘" + "name0" + "‘,‘" + "sex0" + "‘,‘" + "birthpday0" + "‘,‘" + "birthplace" + "‘)";
SqlCommand command = new SqlCommand("", Sqlcon);
command.CommandText = sql;
if( command.ExecuteNonQuery()>0)
{
MessageBox.Show("插入成功");
}
}
/// <summary>
/// 这个插入990条花了大概2345毫秒
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button5_Click(object sender, EventArgs e)
{
BulkInsert();
}
/// <summary>
/// 以下四个函数为批量插入数据
/// </summary>
public void 合并多语句插入()
{
WatchDog.Reset();
SqlCommand command = new SqlCommand("", Sqlcon); ;
string sql = "insert into [Test2].[dbo].[tb1] (name,sex,birthday,birthplace)values";
string sql1 = string.Empty;
for (int i = 0; i <= 998; i++)
{
sql1 += " (‘" + "name0" + i.ToString() + "‘, ‘" + "sex0" + i.ToString() + "‘, ‘" + "birthpday0" + i.ToString() + "‘, ‘" + "birthplace" + i.ToString() + "‘)";
if (i < 998)
{
sql1 += ",";
}
}
WatchDog.Start();
command.CommandText = sql + sql1;
command.ExecuteNonQuery();
WatchDog.Stop();
richTextBox1.AppendText(WatchDog.ElapsedMilliseconds.ToString());
}
public void For循环逐条插入()
{
WatchDog.Reset();
WatchDog.Start();
SqlCommand command = new SqlCommand("", Sqlcon); ;
for (int i = 0; i <= 10000; i++)
{
command.CommandText = "insert into [Test2].[dbo].[tb1] (name,sex,birthday,birthplace)values (‘" + "name00" + i.ToString() + "‘, ‘" + "sex00" + i.ToString() + "‘, ‘" + "birthpday00" + i.ToString() + "‘, ‘" + "birthplace00" + i.ToString() + "‘)";
command.ExecuteNonQuery();
}
WatchDog.Stop();
richTextBox1.AppendText(WatchDog.ElapsedMilliseconds.ToString());
}
//bulkcopy批量插入,速度最快，1000条只用了30ms左右
public void BulkInsert()
{
WatchDog.Reset();
DataTable dt = GetTableSchema();
SqlBulkCopy bulkCopy = new SqlBulkCopy(Sqlcon);
bulkCopy.DestinationTableName = "[Test2].[dbo].[tb1]";
bulkCopy.BatchSize = dt.Rows.Count;
WatchDog.Start();
//把所有的信息加入到表中
for (int i = 0; i <998; i++)
{
DataRow dr = dt.NewRow();
dr["name"] = "NameE" + i.ToString();
dr["sex"] = "sexE" + i.ToString();
dr["birthday"] = "birthdayE" + i.ToString();
dr["birthplace"] = "birthplaceE" + i.ToString();
dr["date"] = DateTime.Now;
dt.Rows.Add(dr);
}
//执行插入表
WatchDog.Start();
bulkCopy.WriteToServer(dt);
WatchDog.Stop();
richTextBox1.AppendText(WatchDog.ElapsedMilliseconds.ToString());
}
//准备好一个Datatable,字段与数据库里的相对应
static DataTable GetTableSchema()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] {
new DataColumn("name",typeof(string)),
new DataColumn("sex",typeof(string)),
new DataColumn("birthday",typeof(string)),
new DataColumn("birthplace",typeof(string)),
new DataColumn("date",typeof(DateTime))
});
return dt;
}
/// <summary>
/// 修改数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_modify_Click(object sender, EventArgs e)
{
string sql = "UPDATE [Test2].[dbo].[tb1] SET name = ‘jiba‘ WHERE name like ‘%%‘";
SqlCommand command = new SqlCommand("", Sqlcon);
//也可以用SqlCommand command = new SqlCommand(sql , Sqlcon);但每次都要新建一个连接对象
command.CommandText = sql;
int n = command.ExecuteNonQuery();
if (n > 0)
{
MessageBox.Show("修改成功");
richTextBox1.AppendText(n.ToString());
}
}
/// <summary>
/// 删除满足条件的信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_delete_Click(object sender, EventArgs e)
{
String sql = " Delete From [Test2].[dbo].[tb1] Where name Like ‘%name%‘";
SqlCommand command = new SqlCommand("", Sqlcon);
//也可以用SqlCommand command = new SqlCommand(sql , Sqlcon);但每次都要新建一个连接对象
command.CommandText = sql;
int n = command.ExecuteNonQuery();
if ( n> 0)
{
MessageBox.Show("删除成功");
richTextBox1.AppendText(n.ToString());
}
}
/// <summary>
/// 按日期条件查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button4_Click(object sender, EventArgs e)
{
//也可以用下面这个语句：
//string sql=SELECT * FROM [Test2].[dbo].[tb1] where date >‘" + dateTimePicker1.Value.ToString()+"‘AND date < ‘" + dateTimePicker2.Value.ToString()+"‘";
string sql = "use Test2 SELECT * FROM tb1 where date >‘" + dateTimePicker1.Value.ToString()+"‘AND date < ‘" + dateTimePicker2.Value.ToString()+"‘";
SqlDataAdapter Sqladpter = new SqlDataAdapter(sql, Sqlcon);
DT = new DataTable();
Sqladpter.Fill(DT);
dataGridView1.DataSource = DT;
}
private void Form1_Load(object sender, EventArgs e)
{
}
}
}