【c#第四次作业】c#操作SQLServer数据库

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;

namespace theFourthHomework
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        DataTable dt = new DataTable();
        private void Form1_Load(object sender, EventArgs e)
        {
            // 连接数据库
            String con = @"Data Source=LONG-PC\LONG;initial Catalog=excel;User id=sa;password=123456;";
            SqlConnection conn = new SqlConnection(con);

            // 定义数据集
            DataSet ds = new DataSet();
            conn.Open();
            String select = "select * from em03_demo";

            SqlDataAdapter adapter = new SqlDataAdapter(select, con);
            adapter.Fill(ds);
            conn.Close();

            // 提取数据表单
            dt = ds.Tables[0];
            this.dataGridView1.DataSource = dt;

            // 调整最后一列的宽度
            dataGridView1.AutoResizeColumn(3, DataGridViewAutoSizeColumnMode.AllCells);

        }

        //添加一行数据
        private void button1_Click(object sender, EventArgs e)
        {
            //添加空行
            DataRow dr = dt.NewRow();       
            dt.Rows.Add(dr);

            this.dataGridView1.Rows[this.dataGridView1.Rows.Count - 2].Cells[0].Value = this.textBox1.Text;
            this.dataGridView1.Rows[this.dataGridView1.Rows.Count - 2].Cells[1].Value = this.textBox2.Text;
            this.dataGridView1.Rows[this.dataGridView1.Rows.Count - 2].Cells[2].Value = this.textBox3.Text;
            this.dataGridView1.Rows[this.dataGridView1.Rows.Count - 2].Cells[3].Value = this.textBox4.Text;           
        }

        //删除选中行
        private void button2_Click(object sender, EventArgs e)
        {
            int RowNumber;
            RowNumber = dataGridView1.CurrentCell.RowIndex;
            dataGridView1.Rows.RemoveAt(RowNumber);
        }

        //修改后的dataGridView1存入数据库
        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                //建立数据库连接
                String con = @"server=LONG-PC\LONG;database=dataGridView;user id=sa;password=123456";
                SqlConnection conn = new SqlConnection(con);

                //定义sql语句
                StringBuilder strsql = new StringBuilder();
                conn.Open();
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    strsql.Append("insert into em03_demo(班级,学号,姓名,作业网址)");
                    strsql.Append("values(");
                    strsql.Append("'" + dataGridView1.Rows[i].Cells[0].Value + "',");
                    strsql.Append("'" + dataGridView1.Rows[i].Cells[1].Value + "',");
                    strsql.Append("'" + dataGridView1.Rows[i].Cells[2].Value + "',");
                    strsql.Append("'" + dataGridView1.Rows[i].Cells[3].Value + "')");
                    using (SqlCommand cmd = new SqlCommand(strsql.ToString(), conn)) //定义连接命令
                    {
                        cmd.ExecuteNonQuery();//执行sql语句      
                    }
                    strsql.Clear();
                }
                conn.Close(); //关闭数据库
                conn.Dispose(); //释放内存空
                MessageBox.Show("已存入数据库");
            }
            catch (Exception ex)
            {
                MessageBox.Show("学号冲突"+ex.Message);
            };
        }
    }
}

一下是界面设计：

在左侧输入要添加的数据，学号为主键，不能为空，否则存了数据库是会出错。

由于dataGridView控件太强大，实现修改功能类似excel，点击单元格进行该单元格修改即可，无需代码修改。

实现删除功能，鼠标点击该行，左边的三角符号位index位置，点击删除即可删除该行数据。

成功存入数据库

难点分心：

 this.dataGridView1.DataSource = dt;

DataRow dr = dt.NewRow();       
            dt.Rows.Add(dr);

            this.dataGridView1.Rows[this.dataGridView1.Rows.Count - 2].Cells[0].Value = this.textBox1.Text;
            this.dataGridView1.Rows[this.dataGridView1.Rows.Count - 2].Cells[1].Value = this.textBox2.Text;
            this.dataGridView1.Rows[this.dataGridView1.Rows.Count - 2].Cells[2].Value = this.textBox3.Text;
            this.dataGridView1.Rows[this.dataGridView1.Rows.Count - 2].Cells[3].Value = this.textBox4.Text;   

由于，datasource对数据库进行了绑定，添加数据比较麻烦。在dataGridView属性中AllowUserToAddRows要改为true.

dataGridView1.rows.count和dt.rows.count为只读，不能操作。

得用dt.rows.add(newRow());添加空行。

“ * ”符号这行算作一行，但不能操作，所以用rows[this.dataGridView.rows.count - 2] 来添加数据。