本文详细介绍如何使用C#连接SQLServer数据库，并在Form窗格中实现数据的增删查改操作。包括数据库连接、数据查询、修改、插入和删除的具体代码实现。
C# 连接数据库SQlServer+Form窗格实现简单动态增删查改操作
一、数据库连接
数据库连接部分学习自该博主原创博客，点击即可跳转
再附上该博主的博客链接：https://blog.csdn.net/kiss__soul/article/details/80316788
该部分通过上方博主的方法实现的，讲得非常的详细，所以在这里就不再进行说明了。
只是将其中的类名修改成了SQLServerText。
二、增删查改操作
在数据库连接成功后，在Form窗格中进行设计；
2.1数据库表名和字段
2.1全局变量
SQLServerTest server = new SQLServerTest();
DataTable data = new DataTable();
static int rowindex;
static string value0 = "";//数据库中只有三个字段，所以定义三个变量，需要的可以自增
static string value1 = "";
static string value2 = "";
2.3获取DataGridview中的行数据
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
rowindex = e.RowIndex;
try
{
//获得当前行的第0列的值
value0 = dataGridView1.Rows[rowindex].Cells[0].Value.ToString();
//获得当前行的第一列的值
value1 = dataGridView1.Rows[rowindex].Cells[1].Value.ToString();
//获得当前行的第二列的值
value2 = dataGridView1.Rows[rowindex].Cells[2].Value.ToString().Trim();
MessageBox.Show("value0："+value0+"value1:"+value1+"value2:"+value2);//如果在测试修改数据时这条语句最好屏蔽，因为它会很烦人
}
catch (Exception exc) { }
}
2.4数据查询（第一种方法）
private void button1_Click(object sender, EventArgs e)
{
//清除按钮绑定，在一次运行多次点击查询按钮时会导致列不断往后添加修改和删除按钮
dataGridView1.Columns.Clear();
string sql = "select * from User1 order by id asc";//以升序排列
data=server.ExecuteQuery(sql);
dataGridView1.DataSource = data;
DataGridViewButtonColumn btn = new DataGridViewButtonColumn();
btn.Name = "btninsert";
btn.HeaderText = "btninsert";
btn.DefaultCellStyle.NullValue = "确认修改";
dataGridView1.Columns.Add(btn);//添加修改按钮
DataGridViewButtonColumn btn2 = new DataGridViewButtonColumn();
btn2.Name = "btndelete";
btn.HeaderText = "btndelete";
btn2.DefaultCellStyle.NullValue = "删除";
dataGridView1.Columns.Add(btn2);//添加删除按钮
}
2.5数据查询（第二种方法）
private DataTable getselectMessage()
{
DataTable dataTable = new DataTable();
string P_Str_ConnectionStr = string.Format(
@"Data Source=(local);Initial Catalog=Test1;Integrated Security=True");
//点击服务器资源管理器，右键数据库选择属性，在属性中可以看见“连接字符串”一栏，将该行的数据复制粘贴到此即可
string P_Str_SqlStr = string.Format("select * from User1 order by id asc");
SqlDataAdapter P_SqlDataAdapter = new SqlDataAdapter(
P_Str_SqlStr,P_Str_ConnectionStr);
P_SqlDataAdapter.Fill(dataTable );
return dataTable ;
}
private void button2_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = getselectMessage();
}
2.6修改、插入、删除数据
private void dataGridView1_CellContentClick_1(object sender, DataGridViewCellEventArgs e)
{
DataGridViewButtonColumn btnCol = dataGridView1.Columns[e.ColumnIndex] as DataGridViewButtonColumn;
if (btnCol != null)
{
MessageBox.Show("当前点击的列是：" + btnCol.HeaderText);
string statusbtn = btnCol.HeaderText;//获取当前点击的按钮Text
if (statusbtn == "btndelete")
{
//删除
this.dataGridView1.Rows.RemoveAt(e.RowIndex);//删除当前行
MessageBox.Show("0:" + value0 + ",1:" + value1 + ",2:" + value2);//查看在点击按钮时能不能获取到该行的所有数据；如果不想写多余代码也可以用debug查看。
string sqldel = "delete from User1 where id = " +value0+"and name ='"+value1+"' and "+"age = '"+value2+"'";//删除选中行数据
int deli = server.ExecuteUpdate(sqldel);
if (deli > 0)
{
MessageBox.Show("动态删除成功!");
}else
{
MessageBox.Show("动态删除失败!");
}
}
else if (statusbtn == "btninsert")
{
//修改和插入
if (dataGridView1.Columns[e.ColumnIndex] is DataGridViewButtonColumn && e.RowIndex > -1)
{
string sql2 = "select * from User1 where id = " + value0;
MessageBox.Show(sql2);
DataTable dse = server.ExecuteQuery(sql2);
//如果在数据库表中没有找到id为value0的数据那么就默认它是新增的，进入插入数数代码块
if (dse.Rows.Count <= 1)
{
//插入数据
string insertsql = "insert into User1(id,name,age) values(" + value0 + ",'" + value1 + "','" + value2 + "'" + ")";
MessageBox.Show(insertsql);
int inserti = server.ExecuteUpdate(insertsql);
if (inserti > 0)
{
MessageBox.Show("动态插入成功!");
}
else
{
MessageBox.Show("动态插入失败!");
}
}
else//如果查询时获取到了该id为value0的数据，那么就进行修改
{
string sql = "update User1 set name ='" + value1 + "',age='" + value2 + "' where id = " + value0;
MessageBox.Show(sql);
int i = server.ExecuteUpdate(sql);
if (i > 0)
{
MessageBox.Show("动态修改成功!");
}
else
{
MessageBox.Show("动态修改失败!");
}
}
}
}
}
}
2.7附上Form.cs的设计代码
窗格的标题不同是因为写到后面修改了一下。
namespace 增删查改操作
{
partial class Form1
{
/// <summary>
/// 必需的设计器变量。
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
/// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows 窗体设计器生成的代码
/// <summary>
/// 设计器支持所需的方法 - 不要修改
/// 使用代码编辑器修改此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.dataGridView1 = new System.Windows.Forms.DataGridView();
this.button1 = new System.Windows.Forms.Button();
this.button2 = new System.Windows.Forms.Button();
this.button3 = new System.Windows.Forms.Button();
this.button4 = new System.Windows.Forms.Button();
((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
this.SuspendLayout();
//
// dataGridView1
//
this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
this.dataGridView1.Location = new System.Drawing.Point(20, 12);
this.dataGridView1.Name = "dataGridView1";
this.dataGridView1.RowTemplate.Height = 23;
this.dataGridView1.Size = new System.Drawing.Size(577, 330);
this.dataGridView1.TabIndex = 0;
this.dataGridView1.CellClick += new System.Windows.Forms.DataGridViewCellEventHandler(this.dataGridView1_CellClick);
this.dataGridView1.CellContentClick += new System.Windows.Forms.DataGridViewCellEventHandler(this.dataGridView1_CellContentClick_1);
//
// button1
//
this.button1.Location = new System.Drawing.Point(74, 348);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(115, 30);
this.button1.TabIndex = 1;
this.button1.Text = "查询所有数据";
this.button1.UseVisualStyleBackColor = true;
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// button2
//
this.button2.Location = new System.Drawing.Point(191, 349);
this.button2.Name = "button2";
this.button2.Size = new System.Drawing.Size(133, 29);
this.button2.TabIndex = 2;
this.button2.Text = "查询所有数据2";
this.button2.UseVisualStyleBackColor = true;
this.button2.Click += new System.EventHandler(this.button2_Click);
//
// button3
//
this.button3.Location = new System.Drawing.Point(330, 349);
this.button3.Name = "button3";
this.button3.Size = new System.Drawing.Size(130, 29);
this.button3.TabIndex = 3;
this.button3.Text = "修改id2数据";
this.button3.UseVisualStyleBackColor = true;
this.button3.Click += new System.EventHandler(this.button3_Click);
//
// button4
//
this.button4.Location = new System.Drawing.Point(466, 351);
this.button4.Name = "button4";
this.button4.Size = new System.Drawing.Size(71, 27);
this.button4.TabIndex = 4;
this.button4.Text = "添加一行";
this.button4.UseVisualStyleBackColor = true;
this.button4.Click += new System.EventHandler(this.button4_Click);
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(617, 394);
this.Controls.Add(this.button4);
this.Controls.Add(this.button3);
this.Controls.Add(this.button2);
this.Controls.Add(this.button1);
this.Controls.Add(this.dataGridView1);
this.Name = "Form1";
this.Text = "增删查改操作";
((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
this.ResumeLayout(false);
}
#endregion
private System.Windows.Forms.DataGridView dataGridView1;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.Button button2;
private System.Windows.Forms.Button button3;
private System.Windows.Forms.Button button4;
}
}
三、结尾
好啦，以上就是完整的一套简单增删查改代码啦，第一次写博客，还请大家多多包涵多多指教~~~
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
