3.使用ADO.NET实现（SQL语句篇）（2）
3.3 FormEdit新增/修改窗体代码
代码3-3 FormEdit 窗体代码
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.Windows.Forms; 9 using System.Data.SqlClient;//新添命名空间 10 using System.Configuration;//新添命名空间 11 12 namespace HomeShop.WinForm 13 { 14 //功能：订单管理系统-新增/修改订单窗体； 15 //作者：夏春涛； 16 //日期：2011-11-30； 17 public partial class FormEdit : Form 18 { 19 //数据库连接字符串 20 private string connectionString = ConfigurationManager.ConnectionStrings["HomeShop"].ConnectionString; 21 22 //当前操作状态，默认为"ADD" 23 private string operationState = "ADD";//ADD：新增；UPDATE：修改； 24 25 //当前订单的ID，默认为0 26 private int currentOrderID = 0; 27 28 //当前订单关联的订购商品信息数据 29 private DataTable orderItems = new DataTable(); 30 31 //临时ID，起始值为0，自动递减（避免与正常的正整数ID重复） 32 private int tempID = 0; 33 34 //【窗体构造函数】 35 public FormEdit() 36 { 37 InitializeComponent(); 38 } 39 40 //【窗体构造函数】-重载，入参：要修改的订单的ID 41 public FormEdit(int orderID) 42 { 43 InitializeComponent(); 44 this.currentOrderID = orderID; 45 this.operationState = "UPDATE"; 46 } 47 48 //【窗体加载事件】-初始化控件的内容 49 private void FormEdit_Load(object sender, EventArgs e) 50 { 51 this.Text = "新增订单";//窗体标题 52 gridView.AutoGenerateColumns = false;//禁止DataGridView控件自动生成列 53 OrderID.Text = this.currentOrderID.ToString(); 54 InitOrderState();//初始化订单状态下拉框 55 InitOrderItems();//初始化订购商品列表 56 57 if ("UPDATE" == this.operationState)//在订单修改状态下 58 { 59 this.Text = "修改订单";//窗体标题 60 InitOrderBaseInfo();//初始化订单基本信息 61 GetOrderTotal();//获取订单总金额 62 } 63 } 64 65 //初始化订单状态下拉框 66 private void InitOrderState() 67 { 68 //从数据库获取订单状态数据 69 SqlConnection connection = new SqlConnection(connectionString); 70 connection.Open(); 71 string sql = @"SELECT * FROM [OrderState] 72 ORDER BY [Code]"; 73 SqlCommand command = new SqlCommand(sql, connection); 74 SqlDataAdapter adapter = new SqlDataAdapter(command); 75 DataTable dataTable = new DataTable(); 76 adapter.Fill(dataTable); 77 connection.Close(); 78 //将数据绑定到订单状态下拉框 79 OrderState.ValueMember = "Code"; 80 OrderState.DisplayMember = "Name"; 81 OrderState.DataSource = dataTable; 82 } 83 84 //初始化订单基本信息 85 private void InitOrderBaseInfo() 86 { 87 //从数据库中获取当前订单基本信息 88 SqlConnection connection = new SqlConnection(connectionString); 89 connection.Open(); 90 string sql = @"SELECT * FROM [Order] 91 WHERE [OrderID] = @OrderID"; 92 SqlCommand command = new SqlCommand(sql, connection); 93 command.Parameters.Add(new SqlParameter("@OrderID", currentOrderID)); 94 SqlDataAdapter adapter = new SqlDataAdapter(command); 95 DataTable dataTable = new DataTable(); 96 adapter.Fill(dataTable); 97 connection.Close(); 98 //将当前订单基本信息显示在编辑区 99 OrderID.Text = dataTable.Rows[0]["OrderID"].ToString(); 100 CustomerName.Text = dataTable.Rows[0]["CustomerName"].ToString(); 101 CustomerPhoneNo.Text = dataTable.Rows[0]["CustomerPhoneNo"].ToString(); 102 CustomerAddress.Text = dataTable.Rows[0]["CustomerAddress"].ToString(); 103 OrderTime.Value = Convert.ToDateTime(dataTable.Rows[0]["OrderTime"]); 104 OrderState.SelectedValue = dataTable.Rows[0]["OrderStateCode"].ToString(); 105 } 106 107 //初始化当前订单关联的商品列表 108 private void InitOrderItems() 109 { 110 SqlConnection connection = new SqlConnection(connectionString); 111 connection.Open(); 112 string sql = @"SELECT [OrderItemID], 113 [Product], 114 [UnitPrice], 115 [Quantity], 116 [UnitPrice]*[Quantity] AS SubTotal 117 FROM [OrderItem] 118 WHERE [OrderID] = @OrderID 119 ORDER BY [OrderItemID]"; 120 SqlCommand command = new SqlCommand(sql, connection); 121 command.Parameters.Add(new SqlParameter("@OrderID", currentOrderID)); 122 SqlDataAdapter adapter = new SqlDataAdapter(command); 123 adapter.Fill(this.orderItems); 124 connection.Close(); 125 126 this.orderItems.PrimaryKey = new DataColumn[] { this.orderItems.Columns["OrderItemID"] }; 127 gridView.DataSource = this.orderItems; 128 } 129 130 //计算当前订单的总金额 131 private void GetOrderTotal() 132 { 133 decimal orderTotal = 0m; 134 //通过累加数据列表中的“小计”来计算当前订单的总金额 135 for (int i = 0; i < gridView.RowCount; i++) 136 { 137 orderTotal += (decimal)gridView.Rows[i].Cells["Col_SubTotal"].Value; 138 } 139 OrderTotal.Text = orderTotal.ToString(); 140 } 141 142 //【新增】商品 143 private void btnProductAdd_Click(object sender, EventArgs e) 144 { 145 if (!ValidateInput_Product()) return;//验证用户输入 146 147 string product = Product.Text.Trim(); 148 decimal unitPrice = Convert.ToDecimal(UnitPrice.Text.Replace(" ","")); 149 int quantity = Convert.ToInt32(Quantity.Text.Replace(" ","")); 150 decimal subtotal = (decimal)unitPrice * quantity; 151 152 DataRow row = this.orderItems.NewRow(); 153 row["OrderItemID"] = tempID--; 154 row["Product"] = product; 155 row["UnitPrice"] = unitPrice; 156 row["Quantity"] = quantity; 157 row["Subtotal"] = subtotal; 158 this.orderItems.Rows.Add(row); 159 160 gridView.DataSource = this.orderItems; 161 GetOrderTotal(); 162 gridView.Rows[gridView.Rows.Count - 1].Selected = true; 163 } 164 165 //验证用户输入-新增/修改商品信息时 166 private bool ValidateInput_Product() 167 { 168 //验证商品名称 169 if ("" == Product.Text.Trim()) 170 { 171 MessageBox.Show(this, "请输入商品名称！", "提示", 172 MessageBoxButtons.OK, MessageBoxIcon.Information); 173 Product.Focus(); 174 return false; 175 } 176 //验证商品单价 177 try 178 { 179 string regexString = @"^[0-9]*[0-9]+[\.]*[0-9]*$";//正则表达式-非负数 180 RegexStringValidator validator = new RegexStringValidator(regexString); 181 validator.Validate(UnitPrice.Text.Replace(" ", "")); 182 } 183 catch 184 { 185 MessageBox.Show(this, "请输入正确的商品单价（非负数）！", "提示", 186 MessageBoxButtons.OK, MessageBoxIcon.Information); 187 UnitPrice.Focus(); 188 return false; 189 } 190 //验证商品数量 191 try 192 { 193 string regexString = @"^[0-9]*[1-9][0-9]*$";//正则表达式-正整数 194 RegexStringValidator validator = new RegexStringValidator(regexString); 195 validator.Validate(Quantity.Text.Replace(" ", "")); 196 } 197 catch 198 { 199 MessageBox.Show(this, "请输入正确的商品数量（正整数）！", "提示", 200 MessageBoxButtons.OK, MessageBoxIcon.Information); 201 Quantity.Focus(); 202 return false; 203 } 204 205 return true; 206 } 207 208 //【修改】选中的商品 209 private void btnProductUpdate_Click(object sender, EventArgs e) 210 { 211 if (gridView.RowCount == 0) return; 212 if (!ValidateInput_Product()) return; 213 214 int selectedRowIndex = gridView.SelectedRows[0].Index; 215 216 int orderItemID = (int)gridView.Rows[selectedRowIndex].Cells["Col_OrderItemID"].Value; 217 string product = Product.Text.Trim(); 218 decimal unitPrice = Convert.ToDecimal(UnitPrice.Text.Replace(" ", "")); 219 int quantity = Convert.ToInt32(Quantity.Text.Replace(" ", "")); 220 decimal subtotal = (decimal)unitPrice * quantity; 221 222 DataRow row = this.orderItems.Rows.Find(orderItemID); 223 row["Product"] = product; 224 row["UnitPrice"] = unitPrice; 225 row["Quantity"] = quantity; 226 row["Subtotal"] = subtotal; 227 228 gridView.DataSource = this.orderItems; 229 GetOrderTotal(); 230 gridView.Rows[selectedRowIndex].Selected = true; 231 } 232 233 //【删除】选中的商品 234 private void btnProductDelete_Click(object sender, EventArgs e) 235 { 236 if (gridView.RowCount == 0) return; 237 238 DialogResult dlgResult = MessageBox.Show(this, "确认要删除选中的商品吗？", "提示", 239 MessageBoxButtons.YesNo, MessageBoxIcon.Question); 240 if (DialogResult.Yes == dlgResult) 241 { 242 int selectedRowIndex = gridView.SelectedRows[0].Index; 243 244 int orderItemID = (int)gridView.SelectedRows[0].Cells["Col_OrderItemID"].Value; 245 this.orderItems.Rows.Find(orderItemID).Delete(); 246 247 gridView.DataSource = this.orderItems; 248 GetOrderTotal(); 249 //选中下一条记录 250 if (selectedRowIndex > gridView.Rows.Count - 1) 251 selectedRowIndex = gridView.Rows.Count - 1; 252 if (selectedRowIndex >= 0) 253 { 254 gridView.Rows[selectedRowIndex].Selected = true; 255 ShowSelectedRowInfo();//将选中的商品信息显示在编辑区 256 } 257 } 258 } 259 260 //【选择更改事件】-将选中的商品信息显示在编辑区 261 private void gridView_SelectionChanged(object sender, EventArgs e) 262 { 263 ShowSelectedRowInfo(); 264 } 265 266 //将选中的商品信息显示在编辑区 267 private void ShowSelectedRowInfo() 268 { 269 if (gridView.SelectedRows.Count == 0) return; 270 Product.Text = gridView.SelectedRows[0].Cells["Col_Product"].Value.ToString(); 271 UnitPrice.Text = gridView.SelectedRows[0].Cells["Col_UnitPrice"].Value.ToString(); 272 Quantity.Text = gridView.SelectedRows[0].Cells["Col_Quantity"].Value.ToString(); 273 } 274 275 //【确定】-保存新增/修改的订单 276 private void btnOK_Click(object sender, EventArgs e) 277 { 278 if (!ValidateInput_Order()) return;//验证用户输入 279 280 if ("ADD"==this.operationState)//新增订单 281 { 282 AddOrder(); 283 MessageBox.Show(this, "新增订单成功！", "提示", 284 MessageBoxButtons.OK, MessageBoxIcon.Information); 285 } 286 if ("UPDATE" == this.operationState)//修改订单 287 { 288 UpdateOrder(); 289 MessageBox.Show(this, "修改订单成功！", "提示", 290 MessageBoxButtons.OK, MessageBoxIcon.Information); 291 } 292 293 this.DialogResult = DialogResult.OK;//设置对话框结果 294 this.Close();//关闭窗体 295 } 296 297 //验证用户输入-保存新增/修改的订单时 298 private bool ValidateInput_Order() 299 { 300 //验证顾客姓名 301 if ("" == CustomerName.Text.Trim()) 302 { 303 MessageBox.Show(this, "请输入顾客姓名！", "提示", 304 MessageBoxButtons.OK, MessageBoxIcon.Information); 305 CustomerName.Focus(); 306 return false; 307 } 308 //验证联系电话 309 if ("" == CustomerPhoneNo.Text.Trim()) 310 { 311 MessageBox.Show(this, "请输入联系电话！", "提示", 312 MessageBoxButtons.OK, MessageBoxIcon.Information); 313 CustomerPhoneNo.Focus(); 314 return false; 315 } 316 //订购商品信息 317 if (0 == gridView.Rows.Count) 318 { 319 MessageBox.Show(this, "请输入订购商品信息！", "提示", 320 MessageBoxButtons.OK, MessageBoxIcon.Information); 321 Product.Focus(); 322 return false; 323 } 324 325 return true; 326 } 327 328 //添加订单到数据库 329 private int AddOrder() 330 { 331 int rowsCountAffected = 0; 332 SqlConnection connection = new SqlConnection(connectionString); 333 connection.Open(); 334 //开始数据库事务 335 SqlTransaction trans = connection.BeginTransaction(); 336 SqlCommand command = new SqlCommand(); 337 command.Connection = connection; 338 command.Transaction = trans; 339 try 340 { 341 //新增订单基本信息，并使用SQL的系统函数@@IDENTITY获取新增订单的ID 342 string sql = @"INSERT INTO [Order]([OrderTime], 343 [OrderStateCode], 344 [CustomerName], 345 [CustomerPhoneNo], 346 [CustomerAddress]) 347 VALUES(@OrderTime, 348 @OrderStateCode, 349 @CustomerName, 350 @CustomerPhoneNo, 351 @CustomerAddress) 352 SET @OrderID = @@IDENTITY "; 353 command.CommandText = sql; 354 command.Parameters.Add(new SqlParameter("@OrderTime", OrderTime.Value)); 355 command.Parameters.Add(new SqlParameter("@OrderStateCode", OrderState.SelectedValue.ToString())); 356 command.Parameters.Add(new SqlParameter("@CustomerName", CustomerName.Text.Trim())); 357 command.Parameters.Add(new SqlParameter("@CustomerPhoneNo", CustomerPhoneNo.Text.Trim())); 358 command.Parameters.Add(new SqlParameter("@CustomerAddress", CustomerAddress.Text.Trim())); 359 //@OrderID作为传出参数，用于获取新增订单的ID 360 SqlParameter paramOrderID = new SqlParameter("@OrderID", SqlDbType.Int); 361 paramOrderID.Direction = ParameterDirection.Output; 362 command.Parameters.Add(paramOrderID); 363 rowsCountAffected = command.ExecuteNonQuery(); 364 this.currentOrderID = (int)paramOrderID.Value; 365 command.Parameters.Clear();//清空参数列表，避免参数重名问题 366 //----------------------------------------------------------- 367 //循环添加订购商品信息 368 for (int i = 0; i < this.orderItems.Rows.Count; i++) 369 { 370 sql = @"INSERT INTO [OrderItem]([OrderID], 371 [Product], 372 [UnitPrice], 373 [Quantity]) 374 VALUES( @OrderID, 375 @Product, 376 @UnitPrice, 377 @Quantity)"; 378 command.CommandText = sql; 379 380 DataRow row = this.orderItems.Rows[i]; 381 command.Parameters.Add(new SqlParameter("@OrderID", this.currentOrderID)); 382 command.Parameters.Add(new SqlParameter("@Product", row["Product"].ToString())); 383 command.Parameters.Add(new SqlParameter("@UnitPrice", (decimal)row["UnitPrice"])); 384 command.Parameters.Add(new SqlParameter("@Quantity", (int)row["Quantity"])); 385 rowsCountAffected += command.ExecuteNonQuery(); 386 command.Parameters.Clear();//清空参数列表，避免参数重名问题 387 } 388 trans.Commit();//提交数据库事务 389 } 390 catch 391 { 392 trans.Rollback();//回滚数据库事务 393 throw; 394 } 395 connection.Close(); 396 397 return rowsCountAffected; 398 } 399 400 //修改订单到数据库 401 private int UpdateOrder() 402 { 403 int rowsCountAffected = 0; 404 SqlConnection connection = new SqlConnection(connectionString); 405 connection.Open(); 406 //开始数据库事务 407 SqlTransaction trans = connection.BeginTransaction(); 408 SqlCommand command = new SqlCommand(); 409 command.Connection = connection; 410 command.Transaction = trans; 411 try 412 { 413 //修改订单基本信息 414 string sql = @"UPDATE [Order] 415 SET [OrderTime] = @OrderTime, 416 [OrderStateCode] = @OrderStateCode, 417 [CustomerName] = @CustomerName, 418 [CustomerPhoneNo] = @CustomerPhoneNo, 419 [CustomerAddress] = @CustomerAddress 420 WHERE [OrderID] = @OrderID"; 421 command.CommandText = sql; 422 command.Parameters.Add(new SqlParameter("@OrderTime", OrderTime.Value)); 423 command.Parameters.Add(new SqlParameter("@OrderStateCode", OrderState.SelectedValue.ToString())); 424 command.Parameters.Add(new SqlParameter("@CustomerName", CustomerName.Text.Trim())); 425 command.Parameters.Add(new SqlParameter("@CustomerPhoneNo", CustomerPhoneNo.Text.Trim())); 426 command.Parameters.Add(new SqlParameter("@CustomerAddress", CustomerAddress.Text.Trim())); 427 command.Parameters.Add(new SqlParameter("@OrderID", currentOrderID)); 428 rowsCountAffected = command.ExecuteNonQuery(); 429 command.Parameters.Clear();//清空参数列表，避免参数重名问题 430 //----------------------------------------------------------- 431 //循环将订购商品信息列表同步更新到数据库中 432 for (int i = 0; i < this.orderItems.Rows.Count; i++) 433 { 434 DataRow row = this.orderItems.Rows[i]; 435 436 if (DataRowState.Added == row.RowState)//新增 437 { 438 sql = @"INSERT INTO [OrderItem]([OrderID], 439 [Product], 440 [UnitPrice], 441 [Quantity]) 442 VALUES( @OrderID, 443 @Product, 444 @UnitPrice, 445 @Quantity)"; 446 command.CommandText = sql; 447 command.Parameters.Add(new SqlParameter("@OrderID", this.currentOrderID)); 448 command.Parameters.Add(new SqlParameter("@Product", row["Product"].ToString())); 449 command.Parameters.Add(new SqlParameter("@UnitPrice", (decimal)row["UnitPrice"])); 450 command.Parameters.Add(new SqlParameter("@Quantity", (int)row["Quantity"])); 451 rowsCountAffected += command.ExecuteNonQuery(); 452 command.Parameters.Clear();//清空参数列表，避免参数重名问题 453 } 454 if (DataRowState.Modified == row.RowState)//修改 455 { 456 sql = @"UPDATE [OrderItem] 457 SET [OrderID] = @OrderID, 458 [Product] = @Product, 459 [UnitPrice] = @UnitPrice, 460 [Quantity] = @Quantity 461 WHERE [OrderItemID] = @OrderItemID"; 462 command.CommandText = sql; 463 command.Parameters.Add(new SqlParameter("@OrderID", this.currentOrderID)); 464 command.Parameters.Add(new SqlParameter("@Product", row["Product"].ToString())); 465 command.Parameters.Add(new SqlParameter("@UnitPrice", (decimal)row["UnitPrice"])); 466 command.Parameters.Add(new SqlParameter("@Quantity", (int)row["Quantity"])); 467 command.Parameters.Add(new SqlParameter("@OrderItemID", (int)row["OrderItemID"])); 468 rowsCountAffected += command.ExecuteNonQuery(); 469 command.Parameters.Clear();//清空参数列表，避免参数重名问题 470 } 471 if (DataRowState.Deleted == row.RowState)//删除 472 { 473 sql = @"DELETE FROM [OrderItem] 474 WHERE [OrderItemID] = @OrderItemID"; 475 command.CommandText = sql; 476 command.Parameters.Add(new SqlParameter("@OrderItemID", (int)row["OrderItemID",DataRowVersion.Original])); 477 rowsCountAffected += command.ExecuteNonQuery(); 478 command.Parameters.Clear();//清空参数列表，避免参数重名问题 479 } 480 } 481 482 trans.Commit();//提交数据库事务 483 } 484 catch 485 { 486 trans.Rollback();//回滚数据库事务 487 throw; 488 } 489 connection.Close(); 490 491 return rowsCountAffected; 492 } 493 494 //【取消】-关闭窗体 495 private void btnCancel_Click(object sender, EventArgs e) 496 { 497 this.Close();//关闭窗体 498 } 499 } 500 }
数据库文件：/Files/SummerRain/NetDbDevRoad/HomeShopDB.rar
完整源代码：/Files/SummerRain/NetDbDevRoad/3使用ADONET实现SQL语句篇.rar
转载请注明：【 夏春涛 email: xchuntao@163.com blog: http://www.cnblogs.com/SummerRain 】
