程序代码:
using System;
using System.Collections.Generic;
using using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
namespace DataGridViewDemo
{
public partial class Form1 : Form
{
public string connStr = @"server=(local)\sqlexpress;Trusted_Connection=SSPI;Database=CourseWebsite";
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
//绑定数据源
BindData();
}
public void BindData()
{
string sql = "select * from admin";
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
adapter.Fill(ds, "admin");
dataGridView1.DataSource = ds.Tables[0];
dataGridView1.Columns[0].ReadOnly = true;//默认ID为不可编辑
dataGridView1.AllowUserToAddRows = true;
}
private void BtnUpdate_Click(object sender, EventArgs e)
{
int count = dataGridView1.Rows.Count - 1; //进行更新操作时dataGridView控件会自动新增一行,故总行数-1
for (int i = 0; i < count; i++)
{
string id = dataGridView1.Rows[i].Cells[0].Value.ToString();
string username = dataGridView1.Rows[i].Cells[1].Value.ToString();
string password = dataGridView1.Rows[i].Cells[2].Value.ToString();
//判断ID 是否为空,如果不为空这执行Update操作,否则执行Insert操作
if (id != null && id != string.Empty && id != "")
{
UpdateValue(id, username, password, false);
}
else
{
UpdateValue("0", username, password, true);
}
}
Form1_Load(sender, e);
}
public void UpdateValue(string id, string UserName, string PassWord, bool isInsert)
{
StringBuilder s = new StringBuilder(250);
if (!isInsert)
{
s.Append("update admin");
s.Append(" set username=@user_name,password=@pwd ");
s.AppendFormat(" where id={0}", id);
}
else
{
s.Append("insert into admin(username,password) ");
s.Append("values(@user_name,@pwd)");
}
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(s.ToString(), conn);
cmd.Parameters.Add(new SqlParameter("@user_name", SqlDbType.NVarChar));
cmd.Parameters.Add(new SqlParameter("@pwd", SqlDbType.NVarChar));
cmd.Parameters[0].Value = UserName;
cmd.Parameters[1].Value = PassWord;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
private void dataGridView1_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
{
//用户确认是否删除
if (MessageBox.Show("Are you sure delete this row?", "Delete confirm", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
DeleteValue(e.Row.Cells[0].Value.ToString());
}
else e.Cancel = true;
}
public void DeleteValue(string id)
{
StringBuilder s = new StringBuilder(100);
s.AppendFormat("delete admin where id={0}", id);
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(s.ToString(), conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
少年啊。你自己去测试下。。
DEMO 的表结构是 ID USERNAME PASSWORD
1 XXX XXX