DataRowState、AcceptChanges、RejectChanges综合使用示例:实现DataGridView数据的增、删、改
下面的示例中,通过一个程序,演示使用DataRowState、AcceptChanges、RejectChanges,实现DataGridView数据的增、删、改。
一、界面设计
二、代码实现
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Threading.Tasks; 9 using System.Windows.Forms; 10 using System.Data; 11 using System.Data.SqlClient; 12 using System.Configuration; 13 14 namespace DataRowStateDemo 15 { 16 public partial class FrmMain : Form 17 { 18 public FrmMain() 19 { 20 InitializeComponent(); 21 } 22 23 //连接字符串 24 string strConn = ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString; 25 26 /// <summary> 27 /// 加载 28 /// </summary> 29 /// <param name="sender"></param> 30 /// <param name="e"></param> 31 private void btn_LoadData_Click(object sender, EventArgs e) 32 { 33 Initdgv(); 34 this.btn_Add.Visible = false; 35 } 36 37 /// <summary> 38 /// 初始化DataGridView 39 /// </summary> 40 private void Initdgv() 41 { 42 SqlConnection conn = new SqlConnection(strConn); 43 string strSQL = "select * from Users"; 44 SqlCommand cmd = new SqlCommand(strSQL, conn); 45 SqlDataAdapter adapter = new SqlDataAdapter(cmd); 46 DataSet dsDgv = new System.Data.DataSet(); 47 try 48 { 49 conn.Open(); 50 //填充数据 51 adapter.Fill(dsDgv); 52 53 this.dgv_Demo.DataSource = dsDgv.Tables[0]; 54 //不显示最后的空行 55 this.dgv_Demo.AllowUserToAddRows = false; 56 // 设置第一列只读 57 this.dgv_Demo.Columns[0].ReadOnly = true; 58 } 59 catch (Exception ex) 60 { } 61 finally 62 { 63 conn.Close(); 64 } 65 } 66 67 /// <summary> 68 /// 编辑 69 /// </summary> 70 /// <param name="sender"></param> 71 /// <param name="e"></param> 72 private void btn_Edit_Click(object sender, EventArgs e) 73 { 74 this.dgv_Demo.AllowUserToAddRows = true; 75 } 76 77 /// <summary> 78 /// 保存 79 /// </summary> 80 /// <param name="sender"></param> 81 /// <param name="e"></param> 82 private void btn_Save_Click(object sender, EventArgs e) 83 { 84 DataTable dtCopy = this.dgv_Demo.DataSource as DataTable; 85 DataSet dsUsers = new DataSet(); 86 87 //产生与表Users结构相同的空表 88 DataTable dtAdd = GetEmptyTable(); 89 DataTable dtEdit = GetEmptyTable(); 90 DataTable dtDel = GetEmptyTable(); 91 92 //根据DataRowState的状态获取新增、修改、删除的表数据 93 dtAdd = dtCopy.GetChanges(DataRowState.Added); 94 dtEdit = dtCopy.GetChanges(DataRowState.Modified); 95 dtDel = dtCopy.GetChanges(DataRowState.Deleted); 96 97 //新增 98 if (dtAdd != null) 99 { 100 dtAdd.TableName = "Added"; 101 dsUsers.Tables.Add(dtAdd); 102 } 103 //修改 104 if (dtEdit != null) 105 { 106 dtEdit.TableName = "Edit"; 107 dsUsers.Tables.Add(dtEdit); 108 } 109 //删除 110 if (dtDel != null) 111 { 112 dtDel.TableName = "Del"; 113 dtDel.RejectChanges(); 114 dsUsers.Tables.Add(dtDel); 115 } 116 //保存数据 117 if (SaveUser(dsUsers)) 118 { 119 MessageBox.Show("保存成功!"); 120 //重新加载数据 121 Initdgv(); 122 } 123 else 124 { 125 MessageBox.Show("保存失败!"); 126 } 127 } 128 129 /// <summary> 130 /// 根据表结构产生空表 131 /// </summary> 132 /// <returns></returns> 133 private DataTable GetEmptyTable() 134 { 135 DataTable dtTable = new DataTable("Users"); 136 //使用集合初始化器添加列 137 dtTable.Columns.AddRange(new DataColumn[]{ 138 new DataColumn("UserID",typeof(Int32)), 139 new DataColumn("UserName",typeof(string)), 140 new DataColumn("Password",typeof(string)), 141 new DataColumn("Sex",typeof(Char)), 142 new DataColumn("Birthday",typeof(DateTime)) 143 }); 144 return dtTable; 145 } 146 147 /// <summary> 148 /// 保存数据 149 /// </summary> 150 /// <param name="ds"></param> 151 /// <returns></returns> 152 private bool SaveUser(DataSet ds) 153 { 154 bool tf = false; 155 //新增 156 if (ds.Tables["Added"] != null) 157 { 158 foreach (DataRow dr in ds.Tables["Added"].Rows) 159 { 160 tf= InsertUser(dr); 161 } 162 } 163 //修改 164 if (ds.Tables["Edit"] != null) 165 { 166 foreach (DataRow dr in ds.Tables["Edit"].Rows) 167 { 168 tf = UpdateUser(dr); 169 } 170 } 171 //删除 172 if (ds.Tables["Del"] != null) 173 { 174 foreach (DataRow dr in ds.Tables["Del"].Rows) 175 { 176 tf = DeleteUser(dr); 177 } 178 } 179 return tf; 180 } 181 182 /// <summary> 183 /// 数据库增加 184 /// </summary> 185 /// <param name="drDataRow"></param> 186 /// <returns></returns> 187 private bool InsertUser(DataRow drDataRow) 188 { 189 string strSQL = string.Format(@"insert into users values('{0}','{1}','{2}','{3}')", drDataRow["UserName"].ToString(), drDataRow["Password"].ToString(),
drDataRow["Sex"].ToString(), drDataRow["Birthday"].ToString()); 190 return ExecuteSQL(strSQL); 191 } 192 193 /// <summary> 194 /// 数据库删除 195 /// </summary> 196 /// <param name="drDataRow"></param> 197 /// <returns></returns> 198 private bool DeleteUser(DataRow drDataRow) 199 { 200 string strSQL = string.Format("delete from users where UserID='{0}'", Convert.ToInt32(drDataRow["UserID"].ToString())); 201 return ExecuteSQL(strSQL); 202 } 203 204 /// <summary> 205 /// 数据库修改 206 /// </summary> 207 /// <param name="drDataRow"></param> 208 /// <returns></returns> 209 private bool UpdateUser(DataRow drDataRow) 210 { 211 string strSQL = string.Format("update users set UserName='{0}',Password='{1}',Sex='{2}',Birthday='{3}' where UserID='{4}'", 212 drDataRow["UserName"].ToString(), drDataRow["Password"].ToString(), drDataRow["Sex"].ToString(), 213 drDataRow["Birthday"].ToString(), Convert.ToInt32(drDataRow["UserID"].ToString())); 214 return ExecuteSQL(strSQL); 215 } 216 217 /// <summary> 218 /// 数据库执行SQL语句 219 /// </summary> 220 /// <param name="strSQL"></param> 221 /// <returns></returns> 222 private bool ExecuteSQL(string strSQL) 223 { 224 bool tfResult = false; 225 SqlConnection conn = new SqlConnection(strConn); 226 SqlCommand cmd = new SqlCommand(strSQL, conn); 227 try 228 { 229 conn.Open(); 230 tfResult= cmd.ExecuteNonQuery().Equals(1); 231 } 232 catch (Exception ex) 233 { } 234 finally 235 { 236 conn.Close(); 237 } 238 239 return tfResult; 240 } 241 242 /// <summary> 243 /// 删除 244 /// </summary> 245 /// <param name="sender"></param> 246 /// <param name="e"></param> 247 private void btn_Del_Click(object sender, EventArgs e) 248 { 249 if (this.dgv_Demo.SelectedRows.Count <= 0) 250 { 251 MessageBox.Show("请先选择要删除的行"); 252 } 253 else 254 { 255 foreach(DataGridViewRow dr in this.dgv_Demo.SelectedRows) 256 { 257 //只是删除DataGridView中显示的数据,并没有删除数据库中的数据 258 this.dgv_Demo.Rows.Remove(dr); 259 } 260 261 } 262 } 263 264 /// <summary> 265 /// 增加空行 266 /// </summary> 267 /// <param name="sender"></param> 268 /// <param name="e"></param> 269 private void btn_Add_Click(object sender, EventArgs e) 270 { 271 //DataGridView没有绑定数据时才可以使用Add()方法增加空行 272 this.dgv_Demo.Rows.Add(); 273 } 274 275 private void FrmMain_Load(object sender, EventArgs e) 276 { 277 this.dgv_Demo.AllowUserToAddRows = false; 278 } 279 } 280 }