C#使用Command将dataGrideView表格内数据与数据库交互

    本文主要介绍通过Command类使用SQL插入指令insert与查询指令select将dataGrideView表格内添加至数据库，与从数据库读出数据存放在dataGrideView表格中。

    C#制作的界面如下：(制作过程不再详述)

• 点击"创建数据"按钮，将姓名、性别、年龄文本框中的数据写入表格；
• 点击"存入数据库"按钮，将表格中数据存入数据库，存入成功后将表格内容清除；
• 点击"读取数据"按钮，将读取数据库中的内容并添加到表格中。

在编程之前向使用SQL Server创建数据库：mydb001，并在数据库中创建数据表：mytable001;

在数据表mytable001中添加列信息：ID(主键、自动赋值)、姓名(nvarchar(20))、性别(nvarchar(5))、年龄(int)。

如图：

 

C#代码如下：

  1 using System;
  2 using System.Windows.Forms;
  3 using System.Data.SqlClient;
  4 
  5 namespace 使用Command将表格中数据写入数据库
  6 {
  7     public partial class Form1 : Form
  8     {
  9         private SqlConnection myConnection = new SqlConnection();
 10         public Form1()
 11         {
 12             InitializeComponent();
 13         }
 14 
 15         private void Form1_Load(object sender, EventArgs e)
 16         {
 17             //定义字符串
 18             string connectionStr = "Server=localhost;integrated security=SSPI;Initial Catalog=mydb001";
 19             //创建连接对象
 20             myConnection = new SqlConnection(connectionStr);
 21             //测试是否连接成功
 22             try
 23             {
 24                 myConnection.Open();//打开数据库
 25             }
 26             catch
 27             {
 28                 MessageBox.Show("数据库连接错误","错误提示");
 29             }
 30             finally
 31             {
 32                 myConnection.Close();//关闭数据库
 33             }
 34             listGender.Items.Add("男");
 35             listGender.Items.Add("女");
 36             listGender.SelectedIndex = 0;
 37             textName.Text = "";
 38             toolStripStatusLabel1.Text = "";
 39         }
 40 
 41         private void btnCreatData_Click(object sender, EventArgs e)
 42         {
 43             if (textName.Text.Trim() == "")
 44             {
 45                 MessageBox.Show("请输入姓名！","错误提示");
 46                 return;
 47             }
 48             int index = dataGridView1.Rows.Add();
 49             dataGridView1.Rows[index].Cells[0].Value = textName.Text.Trim();
 50             dataGridView1.Rows[index].Cells[1].Value = listGender.SelectedItem.ToString();
 51             dataGridView1.Rows[index].Cells[2].Value = numAge.Value;
 52             toolStripStatusLabel1.Text = "向表格添加数据完成！";
 53         }
 54 
 55         private void btnSaveData_Click(object sender, EventArgs e)
 56         {
 57             bool result = true;
 58             try
 59             {
 60                 myConnection.Open();//打开数据库
 61                 for (int i = 0; i < dataGridView1.RowCount; i++)
 62                 {
 63                     string commandSQLStr = "";//命令字符串
 64                     string commandSQLStr1 = "insert into mytable001(";//命令字符串
 65                     string commandSQLStr2 = ")values(";//命令字符串
 66                     string commandSQLStr3 = ")";//命令字符串
 67                     for (int j = 0; j < dataGridView1.ColumnCount; j++)
 68                     {
 69                         if (j == 0)
 70                         {
 71                             commandSQLStr1 = commandSQLStr1 + dataGridView1.Columns[j].HeaderText.ToString();
 72                             if (dataGridView1.Rows[i].Cells[j].Value.GetType() == typeof(string))
 73                             {
 74                                 commandSQLStr2 = commandSQLStr2 +"\'"+ dataGridView1.Rows[i].Cells[j].Value.ToString() + "\'";
 75                             }
 76                             else
 77                             {
 78                                 commandSQLStr2 = commandSQLStr2 + dataGridView1.Rows[i].Cells[j].Value.ToString();
 79                             }
 80                         }
 81                         else
 82                         {
 83                             commandSQLStr1 = commandSQLStr1 + "," + dataGridView1.Columns[j].HeaderText.ToString();
 84                             if (dataGridView1.Rows[i].Cells[j].Value.GetType() == typeof(string))
 85                             {
 86                                 commandSQLStr2 = commandSQLStr2 + "," + "\'" + dataGridView1.Rows[i].Cells[j].Value.ToString() + "\'";
 87                             }
 88                             else
 89                             {
 90                                 commandSQLStr2 = commandSQLStr2 + "," + dataGridView1.Rows[i].Cells[j].Value.ToString();
 91                             }
 92                         }
 93                     }
 94                     commandSQLStr = commandSQLStr1 + commandSQLStr2 + commandSQLStr3;
 95                     SqlCommand myCommand = new SqlCommand(commandSQLStr, myConnection);//创建Command对象
 96                     myCommand.CommandTimeout = 5;//设置超时时间为5秒
 97                     myCommand.ExecuteNonQuery();
 98                 }
 99             }
100             catch
101             {
102                 result = false;
103             }
104             finally
105             {
106                 myConnection.Close();//关闭数据库
107             }
108 
109             if (result)
110             {
111                 dataGridView1.Rows.Clear();
112                 toolStripStatusLabel1.Text = "写入数据库成功！";
113             }
114             else
115             {
116                 toolStripStatusLabel1.Text = "写入数据库失败！";
117             }
118         }
119 
120         private void btnReadData_Click(object sender, EventArgs e)
121         {
122             try
123             {
124                 myConnection.Open();
125                 string commandSQLStr = "select * from mytable001";//命令字符串
126                 SqlCommand myCommand = new SqlCommand(commandSQLStr, myConnection);//创建Command对象
127                 myCommand.CommandTimeout = 5;//设置超时时间为5秒
128                 SqlDataReader myDataReader = myCommand.ExecuteReader();
129                 while (myDataReader.Read())
130                 {
131                     int index = dataGridView1.Rows.Add();
132                     dataGridView1.Rows[index].Cells[0].Value = myDataReader.GetValue(1);
133                     dataGridView1.Rows[index].Cells[1].Value = myDataReader.GetValue(2);
134                     dataGridView1.Rows[index].Cells[2].Value = myDataReader.GetValue(3);
135                 }
136                 myDataReader.Close();
137                 toolStripStatusLabel1.Text = "从数据库读取数据完成！";
138             }
139             catch
140             {
141                 toolStripStatusLabel1.Text = "从数据库读取数据失败！";
142             }
143             finally
144             {
145                 myConnection.Close();
146             }
147         }
148     }
149 }

 

转载于:https://www.cnblogs.com/nicewe/p/8580892.html