using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace winform1
{
public partial class Form3 : Form
{
public Form3()
{
InitializeComponent();
}
private SqlConnection cn;
private SqlCommandBuilder builder;
private SqlDataAdapter da;
private DataSet ds;
//查找事件,点击页面“查找”,
private void butSearch_Click(object sender, EventArgs e)
{
cn = new SqlConnection("server=.;database=test;uid=sa;pwd=123");
cn.Open();
ds = new DataSet();
SqlCommand cmd = new SqlCommand("select * from Test", cn);
da = new SqlDataAdapter(cmd);
//添加必要的列和主键信息以守成架构
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
builder = new SqlCommandBuilder(da);
da.Fill(ds, "Test");//表名一定不能少哦。。。
this.dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
//更新事件
private void butUpdate_Click(object sender, EventArgs e)
{
int row = da.Update(ds, "Test");
MessageBox.Show("更新完成" + row + builder.GetUpdateCommand().CommandText);
}
//插入新记录事件
private void btnInsert_Click(object sender, EventArgs e)
{
DataRow findRow = ds.Tables[0].Rows.Find("1111");//获取包含指定主键值的行
if (findRow != null)
{
MessageBox.Show("已有这个记录");
return;
}
DataRow dr = this.ds.Tables[0].NewRow();
dr["StuId"] = this.texStuId.Text;
dr["StuName"] = this.texStuName.Text;
dr["StuScore "] = "100";
this.ds.Tables[0].Rows.Add(dr);
int row = da.Update(ds, "Test");
MessageBox.Show("添加完成" + row + builder.GetInsertCommand().CommandText);
}
//删除选中记录
private void btnDelete_Click(object sender, EventArgs e)
{
ds.Tables[0].Rows[this.dataGridView1.CurrentRow.Index].Delete();
int row = da.Update(ds, "Test");
MessageBox.Show("删除完成" + row + builder.GetDeleteCommand().CommandText);
}
//查询事件
private void btnSearch_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("select * from Test where StuId=@StuId", cn);
cmd.Parameters.Add("@StuId", SqlDbType.Char, 8);
cmd.Parameters["@StuId"].Value = this.texId.Text;
da = new SqlDataAdapter(cmd);
ds.Clear();
da.Fill(ds, "Test");
this.dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
}
}
PS;还一种更新数据方法,但些方法显得很原始但也可以作为一种思路,希望对大家有用:
//遍历dataGridView所有数据行并修改
string update_SQL="";
for(int i=0;i<=dataGridVivew1.VisibleRowCount-2;i++)
{
update_SQL="update Client_Manage set Client_Name=''''"+dataGridView1[i,1].ToString()+"'''',Client_Phone=''''"+dataGridView1[i,2].ToString()+"'''',Client_Address=''''"+dataGridView1[i,3].ToString()+"'''' where Client_ID="+dataGridView1[i,0].ToString();
comm_update.CommandText=update_SQL;
comm_update.ExecuteNonQuery();
}