C#-数据库数据表数据增删改查。

记录一下demo

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ConnectionSQLServer
{
public partial class Form1 : Form
{
//string conStr = “server = 192.168.100.146;uid = sa;pwd = sasa;database = ReportServer$SQLEXPRESS;”;
string conStr = “server = 192.168.0.31;uid = sa;pwd = sasa;database = bookTest;”;
SqlConnection con;

    public Form1()
    {
        InitializeComponent();
        con = new SqlConnection(conStr);
     }

    private void button1_Click(object sender, EventArgs e)//增加数据
    {
        string insert = "insert into book(bookID,deptCode,mainName,amount)" +
                        "values('" + Convert.ToString(textBox2.Text) + "','"+Convert.ToString(textBox3.Text)+ "'" +
                        ",'" + Convert.ToString(textBox4.Text) + "','" + Convert.ToString(textBox5.Text) + "')";

        SqlCommand command = new SqlCommand(insert, con);

        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            command.ExecuteNonQuery();

            /*SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

            DataSet dataSet = new DataSet();

            dataAdapter.Fill(dataSet,"book");

            dataGridView1.DataSource = dataSet;

            dataGridView1.DataMember = "book";*/

            button4_Click(sender, e);

            con.Close();

            //MessageBox.Show(insert,"添加成功");
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        
    }

    private void button2_Click(object sender, EventArgs e)//删除数据
    {
        //SqlConnection con = new SqlConnection("server = 192.168.0.31;uid = sa;pwd = sasa;database = fzgllib;");

        string delete = "Delete from book where bookID = " + textBox2.Text + "and mainName = '" + textBox4.Text + "'";

        SqlCommand command = new SqlCommand(delete, con);

        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            command.ExecuteNonQuery();

            button4_Click(sender, e);

            con.Close();

            //MessageBox.Show(delete, "删除成功");
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        
    }

    private void button3_Click(object sender, EventArgs e)//修改数据
    {
        //SqlConnection con = new SqlConnection("server = 192.168.0.31;uid = sa;pwd = sasa;database = fzgllib;");

        string bookID = textBox2.Text;
        string deptCode = textBox3.Text;
        string mainName = textBox4.Text;

        string upDate = "UPDATE book SET deptCode = '" + Convert.ToString(textBox3.Text) + "'" +
                        ",mainName = '" + Convert.ToString(textBox4.Text) + "' where bookID = " + textBox2.Text;

        SqlCommand command = new SqlCommand(upDate, con);

        try
        {
            if(con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            command.ExecuteNonQuery();

            button4_Click(sender, e);

            con.Close();

            //MessageBox.Show(upDate,"修改成功");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        
    }

    private void button4_Click(object sender, EventArgs e)//查询数据
    {
        //SqlConnection con = new SqlConnection("server = 192.168.0.31;uid = sa;pwd = sasa;database = fzgllib;");

        /*string query = "SELECT *from book where deptCode = '" + Convert.ToString(textBox3.Text) + "' " +
                       "or mainName = '" + Convert.ToString(textBox4.Text) + "'";*/

        string query = "SELECT *from student";

        SqlCommand command = new SqlCommand(query, con);//数据库操作指令
        
        SqlDataAdapter dataAdapter = new SqlDataAdapter();//数据适配器

        dataAdapter.SelectCommand = command;//数据适配器操作指令

        DataSet dataSet = new DataSet();//数据集

        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            dataAdapter.SelectCommand.ExecuteNonQuery();//执行数据库查询指令

            dataAdapter.Fill(dataSet);//填充数据集

            dataGridView1.DataSource = dataSet.Tables[0].DefaultView;//连接数据表格，显示数据

            con.Close();

            //MessageBox.Show("查询成功");
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

    private void button5_Click(object sender, EventArgs e)//创建数据库
    {
        string createDatabase = "create database bookTest";

        SqlCommand command = new SqlCommand(createDatabase, con);

        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        command.ExecuteNonQuery();

        con.Close();

        MessageBox.Show("创建数据库成功");
    }

    private void button6_Click(object sender, EventArgs e)//创建数据表
    {
        con.Open();//打开连接
        
        string createTable = "create table student" + 
                            "(myld INTEGER CONSTRAINT PKeyMyld PRIMARY KEY," + 
                            "myName CHAR(50),myAddress CHAR(255))";             //创建一张student数据表

        SqlCommand command = new SqlCommand(createTable, con);

        command.ExecuteNonQuery();

        try
        {
            string insert = "insert into student (myld,myName,myAddress)" + "VALUES(1001,'CHENP PEI','666 AT 999')";    //插入数据

            command = new SqlCommand(insert, con);

            command.ExecuteNonQuery();

            con.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }
}

}