.NET数据库编程求索之路--3.使用ADO.NET实现(SQL语句篇)(2)

简介: 3.使用ADO.NET实现(SQL语句篇)(2)   3.3  FormEdit新增/修改窗体代码   代码3-3 FormEdit 窗体代码 1 using System; 2 using System.

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

目录
相关文章
|
3天前
|
SQL 关系型数据库 MySQL
数据库管理新星:Navicat、HeidiSQL和SQL Workbench的竞争格局
随着数据管理需求的不断增长,数据库管理工具的选择变得至关重要。在众多选择中,Navicat、HeidiSQL和SQL Workbench作为备受关注的数据库管理工具,各自展现出独特的特性和功能,竞争格局日益激烈。
17 0
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据查询
【数据库SQL server】关系数据库标准语言SQL之数据查询
57 0
|
13天前
|
SQL 存储 关系型数据库
sql数据库的相关概念与底层介绍
sql数据库的相关概念与底层介绍
31 0
|
1天前
|
SQL 关系型数据库 MySQL
【MySQL 数据库】7、SQL 优化
【MySQL 数据库】7、SQL 优化
5 0
|
2天前
|
SQL Oracle Java
怎样写出可在各种数据库间移植的SQL
国际标准没有对这些函数做规定,这种 SQL 语句就会和数据库相关而无法移植了,而这些函数在应用开发中还特别常见。 Hibernate 能将包括这些函数的标准 HQL 语句转换成不同数据库的 SQL,但 HQL 支持的函数太少,碰到不认识的函数还是要分别注册,这就丧失了可移植性;而且 HQL 能生成的 SQL 本身也比较简单,覆盖面太窄了。
|
25天前
|
SQL 开发框架 .NET
ASP.NET Web——GridView完整增删改查示例(全篇幅包含sql脚本)大二结业考试必备技能
ASP.NET Web——GridView完整增删改查示例(全篇幅包含sql脚本)大二结业考试必备技能
28 0
|
1月前
|
SQL 人工智能 运维
数据库基础入门 — SQL排序与分页
数据库基础入门 — SQL排序与分页
16 0
|
1月前
|
SQL 人工智能 运维
数据库基础入门 — SQL运算符
数据库基础入门 — SQL运算符
15 0
|
1月前
|
SQL 人工智能 运维
数据库基础入门 — SQL
数据库基础入门 — SQL
26 0
|
1月前
|
SQL 存储 数据库
【数据库SQL server】自学终极笔记
【数据库SQL server】自学终极笔记
75 0