C#操作Sql数据库之对数据库进行增删改查

直接上代码：

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)
        {

        }
    }
}

C#操作Sql数据库之对数据库进行增删改查

标签：init   namespace   wing   bsp   sed   ali   client   根据   grid