WinForm之Adonet数据库快速编程

转自（http://blog.csdn.net/zyq5945/article/details/8491198）

 

ADO.Net数据库操作综合起来就是增删改查，而且这几个流程都是固定的。

查询：连接数据库-->打开记录集-->遍历记录集并获取记录内容

插入：连接数据库-->打开记录集-->插入数据库记录-->将记录保存到数据库

更新：连接数据库-->打开记录集-->遍历记录集并更新记录-->将记录保存到数据库

删除：连接数据库-->执行删除的SQL语句

 

将表的一行数据内容封装成一个数据记录类，该数据记录类实现如下接口：

1 public interface IDBOperability
2 {
3       void SetRecordData(IDBRecord dbRecord, Object oFlag);
4       void GetRecordData(IDBRecord dbRecord, Object oFlag);
5       Object PrimaryKey { get; set; }
6 }

需要做到快速，还需要一个小工具ADO.Net助手来帮忙根据SQL内容生成数据记录类，具体操作及使用请参考

《如何使用ADO.Net助手生成C#数据库代码》。

 

使用ADO.Net助手辅助生产数据库代码。

 

一. 用ADO.Net助手生成数据库代码

SQL Server2005的建表语句如下：

CREATE TABLE [dbo].[tbl_Demo](
    [ID] [int] IDENTITY(50,1) NOT NULL,
    [Guid] [nvarchar](80) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [Name] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [Birthday] [datetime] NULL,
    [Photo] [image] NULL,
 CONSTRAINT [PK_tbl_Demo] PRIMARY KEY CLUSTERED 
(
    [Guid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

用ADO.Net助手生成的C#代码如下：

  1 /*----------------------------------------------------------------------------------------------------------------
  2 Version:    CSAdonetCodeWriter 1.00
  3    Time:    2012/12/4 19:47:03
  4     SQL:    SELECT * FROM Tbl_Demo
  5 ----------------------------------------------------------------------------------------------------------------*/
  6 using System;
  7 using System.Collections.Generic;
  8 using System.Text;
  9 
 10 namespace Database
 11 {
 12     [DBTableAttribute("Tbl_Demo", "Guid")]
 13     public class Tbl_Demo : IDBOperability
 14     {
 15         #region AutoIncrement
 16         private static readonly DBTableAttribute m_AutoIncrement = new DBTableAttribute("Tbl_Demo", "Guid", "Id");
 17 
 18         public static DBTableAttribute AutoIncrement
 19         {
 20             get { return Tbl_Demo.m_AutoIncrement; }
 21         } 
 22         #endregion
 23 
 24         #region Field and Property
 25         
 26         /// <summary>
 27         /// 0 ID Int32
 28         /// </summary>
 29         protected Int32 m_ID;
 30 
 31         /// <summary>
 32         /// 0 ID Int32
 33         /// </summary>
 34         public Int32 ID
 35         {
 36             get
 37             {
 38                 return m_ID;
 39             }
 40             set
 41             {
 42                 m_ID = value;
 43             }
 44         }
 45 
 46         /// <summary>
 47         /// 1 Guid String
 48         /// </summary>
 49         protected String m_Guid;
 50 
 51         /// <summary>
 52         /// 1 Guid String
 53         /// </summary>
 54         public String Guid
 55         {
 56             get
 57             {
 58                 return m_Guid;
 59             }
 60             set
 61             {
 62                 m_Guid = value;
 63             }
 64         }
 65 
 66         /// <summary>
 67         /// 2 Name String
 68         /// </summary>
 69         protected String m_Name;
 70 
 71         /// <summary>
 72         /// 2 Name String
 73         /// </summary>
 74         public String Name
 75         {
 76             get
 77             {
 78                 return m_Name;
 79             }
 80             set
 81             {
 82                 m_Name = value;
 83             }
 84         }
 85 
 86         /// <summary>
 87         /// 3 Birthday DateTime
 88         /// </summary>
 89         protected DateTime? m_Birthday;
 90 
 91         /// <summary>
 92         /// 3 Birthday DateTime
 93         /// </summary>
 94         public DateTime? Birthday
 95         {
 96             get
 97             {
 98                 return m_Birthday;
 99             }
100             set
101             {
102                 m_Birthday = value;
103             }
104         }
105 
106         /// <summary>
107         /// 4 Photo Byte[]
108         /// </summary>
109         protected Byte[] m_Photo;
110 
111         /// <summary>
112         /// 4 Photo Byte[]
113         /// </summary>
114         public Byte[] Photo
115         {
116             get
117             {
118                 return m_Photo;
119             }
120             set
121             {
122                 m_Photo = value;
123             }
124         }
125 
126         
127         #endregion
128 
129         #region IDBOperability
130 
131         /// <summary>
132         /// 1 Guid String
133         /// </summary>
134         Object IDBOperability.PrimaryKey
135         {
136             get
137             {
138                 return this.Guid;
139             }
140             set
141             {
142                 this.Guid = (String)value;
143             }
144         }
145 
146         void IDBOperability.SetRecordData(IDBRecord dbRecord, Object oFlag)
147         {
148             IDBRecord x = dbRecord;
149 
150             //x["ID"] = ID; // 0 Int32
151             x["Guid"] = Guid; // 1 String
152             x["Name"] = Name; // 2 String
153             x["Birthday"] = Birthday; // 3 DateTime
154             x["Photo"] = Photo; // 4 Byte[]
155             /*
156             //x[0] = ID; // ID Int32
157             x[1] = Guid; // Guid String
158             x[2] = Name; // Name String
159             x[3] = Birthday; // Birthday DateTime
160             x[4] = Photo; // Photo Byte[]
161             */
162         }
163 
164         void IDBOperability.GetRecordData(IDBRecord dbRecord, Object oFlag)
165         {
166             IDBRecord x = dbRecord;
167 
168             ID = DBValue.Convert(x["ID"]); // 0 Int32
169             Guid = DBValue.Convert(x["Guid"]); // 1 String
170             Name = DBValue.Convert(x["Name"]); // 2 String
171             Birthday = DBValue.Convert(x["Birthday"]); // 3 DateTime
172             Photo = DBValue.Convert(x["Photo"]); // 4 Byte[]
173             /*
174             ID = DBValue.Convert(x[0]) // ID Int32
175             Guid = DBValue.Convert(x[1]) // Guid String
176             Name = DBValue.Convert(x[2]) // Name String
177             Birthday = DBValue.Convert(x[3]) // Birthday DateTime
178             Photo = DBValue.Convert(x[4]) // Photo Byte[]
179             */
180         }
181 
182         #endregion
183     }
184 }

 

二. 将生成的数据库代码和ADO.Net的封装类添加到工程引用中，然后编译整个项目解决方案（为后面的添加数据源做准备）

 

三. 拖主界面如下图

 

为一个DataGridView和五个按钮，DataGridView的DataSource属性进行添加项目数据源，选择数据源类型为对象，点击下一步，

在选择数据对象界面选择刚才用ADO.Net助手生成的数据库类对象，点击完成按钮完成对数据源的添加。DataGridView的Columns属性

各个列的HeadText修改为界面显示的列名。

 

四. 拖数据编辑对话框如下图

拖动一个BindingSource控件到界面中,对其DataSource属性用刚才和添加DataGridView数据源方式添加数据源。

设置TextBox中DataBindings的Text，DateTimePicker中DataBindings的Value和pictureBox中DataBindings的Image设置为添加的数据源各相对应的字段。

五. 编写相应的数据库代码

1. 连接数据库

定义数据库对象

1         IDBDatabase m_db;
2 
3         public IDBDatabase Db
4         {
5             get { return m_db; }
6             set { m_db = value; }
7         }

 

初始化并连接

 

 1         private void MainForm_Load(object sender, EventArgs e)
 2         {
 3             try
 4             {
 5                 Settings cfg = Settings.Default;
 6                 Db = DBFactory.CreateDatabase(cfg.Connection);
 7                 if (Db == null)
 8                 {
 9                     throw new Exception("配置的Connection不正确或者未加载到数据库提供程序");
10                 }
11                 Db.ConnectionString = cfg.ConnectionString;
12                 int nUpdateBatchSize = 1;
13                 int.TryParse(cfg.UpdateBatchSize, out nUpdateBatchSize);
14                 Db.DBSetCommandBuilder += delegate(Object s, DbCommandBuilder cmdBuilder)
15                 {
16                     cmdBuilder.QuotePrefix = cfg.QuotePrefix;
17                     cmdBuilder.QuoteSuffix = cfg.QuoteSuffix;
18                     cmdBuilder.DataAdapter.UpdateBatchSize = nUpdateBatchSize;
19                 };
20                 Db.Open();               
21             }
22             catch (System.Exception ex)
23             {
24                 MessageBox.Show(ex.Message);
25                 Environment.Exit(-1);
26             }
27 
28             button_Refresh.PerformClick();
29 
30         }

 

定义记录对象

 

1         List<Tbl_Demo> m_DelDemos = new List<Tbl_Demo>();
2 
3         public List<Tbl_Demo> DelDemos
4         {
5             get { return m_DelDemos; }
6             set { m_DelDemos = value; }
7         }

 

定义当前选中的记录

 

1         public Tbl_Demo CurDemo
2         {
3             get
4             {
5                 return tblDemoBindingSource.Current as Tbl_Demo;
6             }
7         }

 

 

2. 查询

 1         private void button_Refresh_Click(object sender, EventArgs e)
 2         {
 3             int nCount = Db.Count<Tbl_Demo>();
 4             if (Demos.Capacity < nCount)
 5             {
 6                 Demos.Capacity = nCount;               
 7             }
 8             Db.Select(Demos);
 9             tblDemoBindingSource.DataSource = Demos;
10             tblDemoBindingSource.ResetBindings(false);
11         }

 

3. 添加

 1 private void button_New_Click(object sender, EventArgs e)
 2         {
 3             tblDemoBindingSource.AddNew();
 4             using (TblDemoForm dlg = new TblDemoForm())
 5             {
 6                 CurDemo.Guid = Guid.NewGuid().ToString();
 7                 CurDemo.Birthday = DateTime.Today;
 8                 dlg.TblDemoBindingSource.DataSource = tblDemoBindingSource.DataSource;
 9                 dlg.TblDemoBindingSource.Position = tblDemoBindingSource.Position;
10                 if (DialogResult.OK == dlg.ShowDialog(this))
11                 {
12                     try
13                     {
14                         Db.Save(CurDemo);
15                         CurDemo.ID = Db.Scalar(CurDemo, Tbl_Demo.AutoIncrement);
16                     }
17                     catch (System.Exception ex)
18                     {
19                         MessageBox.Show(ex.Message);
20                     }
21                 }
22                 else
23                 {
24                     tblDemoBindingSource.RemoveCurrent();
25                 }
26                 tblDemoBindingSource.ResetCurrentItem();
27             }
28         }

 

4. 编辑

 1         private void button_Edit_Click(object sender, EventArgs e)
 2         {
 3             if (CurDemo == null)
 4             {
 5                 return;
 6             }
 7             using (TblDemoForm dlg = new TblDemoForm())
 8             {
 9                 dlg.TblDemoBindingSource.DataSource = tblDemoBindingSource.DataSource;
10                 dlg.TblDemoBindingSource.Position = tblDemoBindingSource.Position;
11                 if (DialogResult.OK == dlg.ShowDialog(this))
12                 {
13                     try
14                     {
15                         Db.Save(CurDemo);
16                     }
17                     catch (System.Exception ex)
18                     {
19                         MessageBox.Show(ex.Message);
20                     }
21                 }
22                 tblDemoBindingSource.ResetCurrentItem();
23             }
24         }

 

5. 删除

 1         private void button_Del_Click(object sender, EventArgs e)
 2         {
 3             if (CurDemo == null)
 4             {
 5                 return;
 6             }
 7             try
 8             {
 9                 Db.Delete(CurDemo);
10                 tblDemoBindingSource.RemoveCurrent();
11                 MessageBox.Show("成功将数据从数据库中删除");
12             }
13             catch (System.Exception ex)
14             {
15                 MessageBox.Show(ex.Message);
16             }
17         }

 

调用存储过程

 1 --SQL Server 2005 存储过程
 2 Create Procedure [dbo].[usp_ProcedureTest]  
 3 @firstParam int,  
 4 @secondParam int Output,  
 5 @thirdParam datetime Output  
 6 As 
 7 BEGIN   
 8     -- 返回记录集的话最好是用下面语句关闭一些不必要的返回记录集
 9     SET NOCOUNT ON; 
10 
11     DECLARE @Count int
12     SET @secondParam = @firstParam * 10
13     SELECT @thirdParam = getdate() 
14     SELECT @Count = Count(*) FROM tbl_Demo
15     SELECT * FROM tbl_Demo
16     return @Count 
17 END

 

调用代码：

 1         private void button_Procedure_Click(object sender, EventArgs e)
 2         {
 3             try
 4             {
 5 
 6                 DbCommand cmd = Db.CreateProcedureTCommand("usp_ProcedureTest");
 7                 int firstParam = 8;
 8                 cmd.Parameters["@firstParam"].Value = firstParam;
 9                 List<Tbl_Demo> lDemo = new List<Tbl_Demo>();
10 
11                 using (DbDataReader reader = cmd.ExecuteReader())
12                 {
13                     // 解析记录     
14                     DBHelper.GetRecordData(lDemo, reader, null);
15 
16                     // 用ExecuteReader执行存储过程的话要先关闭DataReader才能取各参数值
17                     reader.Close();
18                 }
19 
20                 int secondParam = (int)cmd.Parameters["@secondParam"].Value;
21                 DateTime tm = (DateTime)cmd.Parameters["@thirdParam"].Value;
22                 int nRet = (int)cmd.Parameters[0].Value;
23                 string msg = string.Format("{0}*10={1}\n数据库时间是{2}\n有{3}条记录",
24                     firstParam, secondParam, tm, nRet);
25                 MessageBox.Show(msg);
26             }
27             catch (System.Exception ex)
28             {
29                 MessageBox.Show(ex.Message);
30             }
31 
32         }

 

Demo中将数据信息配置在配置文件中，可以按配置文件的格式做相应修改即可以切换数据库，已经简单验证在ACCESS，SQLSERVER，MYSQL，ORACLE和SQLite均正常运行。

这里虽然说的winform的，但上述数据库增删查改流程在支持ADO.Net语言的程序中均适用。

 

posted @ 2014-06-12 17:07  仆人  阅读(382)  评论(0)  编辑  收藏  举报