学习ADO.NET和封装数据库数据成c#对象 - bergy

公告

ADO.NET

这一节所涉及的数据库下面给出了其创建的SQL语句：

(blog数据库及employee表的创建语句)

View Code

 1 create database blog
 2 
 3 use blog
 4 
 5 create table employee
 6 (
 7     eid int primary key identity,
 8     ename varchar(20) not null,
 9     esex bit default 1 not null,
10     eage int not null,
11     esalary int not null,
12     eaddress varchar(100)
13 )
14 go
15 insert into employee values ('孙悟空',default,23,5000,'花果山')
16 insert into employee values ('猪八戒',default,40,3500,'高老庄')
17 insert into employee values ('沙悟净',default,45,4000,'流沙河')
18 insert into employee values ('唐僧',default,18,5000,'大唐')
19 insert into employee values ('林冲',default,18,6000,'东京')
20 insert into employee values ('孙二娘',0,33,4500,'黑店')
21 insert into employee values ('武松',default,35,4600,'景阳冈')
22 insert into employee values ('宋江',default,50,5000,'梁山')
23 insert into employee values ('诸葛亮',default,23,5000,'卧龙岗')
24 insert into employee values ('刘备',default,45,5500,'巴蜀')
25 insert into employee values ('关羽',default,43,4000,'巴蜀')
26 insert into employee values ('张飞',default,42,3500,'巴蜀')
27 insert into employee values ('曹操',default,28,8000,'魏国')
28 insert into employee values ('孙权',default,86,6000,'吴国')
29 insert into employee values ('迎春',0,18,1200,'贾府')
30 insert into employee values ('元春',0,19,1300,'贾府')
31 insert into employee values ('探春',0,20,1400,'贾府')
32 insert into employee values ('惜春',0,21,1500,'贾府')
33 insert into employee values ('秋香',0,22,1600,'贾府')
34 insert into employee values ('冬香',0,23,1700,'贾府')
35 insert into employee values ('夏香',0,24,1800,'贾府')
36 insert into employee values ('春香',0,25,1900,'贾府')
37 insert into employee values ('贾宝玉',1,26,3000,'贾府')
38 insert into employee values ('王熙凤',0,35,5000,'贾府')
39 insert into employee values ('葫芦僧',default,5,800,'葫芦秒')
40 insert into employee values ('林黛玉',0,18,3000,'贾府')
41 insert into employee values ('聂小倩',0,23,4300,'坟墓')
42 insert into employee values ('宁采臣',default,23,1500,'荒野')
43 insert into employee values ('燕赤霞',default,56,5000,'道观')
44 insert into employee values ('萧峰',default,45,8000,'大辽')
45 insert into employee values ('段誉',default,26,8500,'大理')
46 insert into employee values ('虚竹',default,34,4000,'缥缈峰')
47 insert into employee values ('慕容复',default,35,5500,'燕子坞')

一.SqlConnection对象: SqlConnection表示SQL Server 数据库的一个打开的连接，无法继承。它是连接字符串(搜集连接数据的信息)正如下面示例中的string strcon = "server=.;database=blog;uid=sa;pwd=1";语句。SqlConnection构造函数：1.SqlConnection() 需要显式的设置ConectionString属性 2.SqlConnection(string connectionString);参数是给定包含连接字符串的字符串后，初始化SqlConnection新实例，在c#中定义为：public SqlConnection(string connectionString)。常用属性： ConnectionString:设置或获取用于打开SQL Server 数据库的字符串。 State:指示SqlConnection状态，c#中定义为：public ConnectionState State{get;} 而ConnectionState在c#中定义为：public enum ConnectionState 它是枚举类型，成员名有：Closed Open Connecting Executing Fetching Broken。常用方法：Open 和 Close 用来打开或关闭数据库连接
体会下面两段代码的不同点：

View Code

 1 using System;
 2 using System.Data.SqlClient;
 3 
 4 public class StudyADO01
 5 {
 6     public static void Main()
 7     {
 8         string strcon = "server=.;database=blog;uid=sa;pwd=1";
 9         SqlConnection con = new SqlConnection(strcon);
10         using(con)
11         {
12             con.Open();
13             Console.WriteLine(con.State);
14         }
15         Console.WriteLine(con.State);
16     }
17 }
18 
19 //所用的构造函数不同，关闭方式也不一样
20 using System;
21 using System.Data.SqlClient;
22 
23 public class StudyADO01
24 {
25     public static void Main()
26     {
27         SqlConnection con = new SqlConnection(strcon);
28         //设置连接字符串
29         con.ConnectionString = "server=.;database=blog;uid=sa;pwd=1";
30         con.Open();
31         Console.WriteLine(con.State);
32         con.Close();
33         Console.WriteLine(con.State);
34         Console.WriteLine(con.State);
35     }
36 }

二.SqlCommand 对象:用来执行SQL语句，它表示对SQL Server数据库执行的一个Transact-SQL语句或存储过程。无法继承。构造函数：1.SqlCommand()用来实例SqlCommand初始属性值：CommandText(cmdText)设置要执行SQL语句;CommandType(CommandType.Text)获取或设置一个值，该值指示如何解释CommandText属性（设置sql语句执行的方式），它在c#中定义为：public override CommandType CommandType{set;get;},作为返回值的CommandType的类型是枚举：public enum CommandType,有三个成员：1.Text 2.StoredProcedure 3.TableDirect(表的名称); Connection( connection)设置SqlCommand对象的数据库连接。. 2。SqlCommand(string) 定义：public SqlCommand(string cmdText)。 3.SqlCommand(string,SqlConnection) 定义：public SqlCommand(string cmdText,SqlConnection connection)。ExecuteNonQuery方法执行的是非查询语句，其定义为：public override int ExecuteNonQuery()返回int类型数值。非查询语句：ExecuteNonQuery()只能用来执行非查询语句，返回影响的行数。
下面给出俩示例予以解释：

插入数据的非查询语句：

View Code

 1 using System;
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 
 5 public class StudyADO02
 6 {
 7     public static void Main()
 8     {
 9         string strcon = "server=.;database=blog;uid=sa;pwd=1";
10         SqlConnection con = new SqlConnection(strcon);
11         SqlCommand cmd = new SqlCommand();
12         string name = "陈一冰";
13         int sex = 1;
14         int age = 39;
15         int salary = 7000;
16         string address = "China";
17         using(con)
18         {
19             con.Open();
20             using(cmd)
21             {
22                 //把已经打开数据库的连接赋值给cmd,让cmd对象知道SQL语句要在那个数据库上执行
23                 cmd.Connection = con;
24                 //设置要执行的sql语句
25                 cmd.CommandText = "insert into employee values ('"+name+"',"+sex+","+age+","+salary+",'"+address+"')";
26                 //设置执行SQL语句的方式
27                 cmd.CommandType = CommandType.Text;
28                 //开始执行
29                 int i=cmd.ExecuteNonQuery();
30                 if(i!=0)
31                 {
32                     Console.WriteLine("执行成功");
33                 }
34             }
35         }
36         
37     }
38 }

删除记录的非查询语句：

View Code

 1 using System;
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 
 5 public class StudyADO03
 6 {
 7     public static void Main()
 8     {
 9         //删除eid =56的记录
10         Delete("56","employee","eid");
11         
12     }
13     public static void Delete(string eid,string table,string key)
14     {
15         string strcon = "server=.;database=blog;uid=sa;pwd=1";
16         SqlConnection con = new SqlConnection(strcon);
17         SqlCommand cmd = new SqlCommand();
18     
19         using(con)
20         {
21             con.Open();
22             using(cmd)
23             {
24                 //把已经打开数据库的连接赋值给cmd,让cmd对象知道SQL语句要在那个数据库上执行
25                 cmd.Connection = con;
26                 //设置要执行的sql语句
27                 cmd.CommandText = "delete from "+table+" where "+key+" ="+eid;
28                 //Console.WriteLine(cmd.CommandText);
29                 //设置执行SQL语句的方式
30                 cmd.CommandType = CommandType.Text;
31                 //开始执行
32                 int i=cmd.ExecuteNonQuery();
33                 if(i!=0)
34                 {
35                     Console.WriteLine("执行成功");
36                 }
37             }
38         }
39     }
40 }

Parameters和SqlParameter：Parameters获取SqlParametersCollection集合，是SqlCommand对象的属性。SqlParametersCollection代表了CommandText中出现的参数的集合，集合中的元素是SqlParameter类型。c#中定义为：public SqlParameterCollection Parameters{get;} SqlParametersCollection表示与SqlCommand相关联的参数的集合以及各个参数到DataSet中的映射。常用SqlParametersCollection的Add方法向集合中添加SqlParameter 它的定义：public SqlParameter Add(string ParameterName,SqlDbType sqlDbType,int size)返回值SqlParameter指的是新的SqlParameter对象。 Item能获取具有指定属性的SqlParameter常用索引有： Item[int index] Item[String name](public SqlParameter Item[string parameterName] {get;set;}) 这两个索引将会返回指定位置或名字的SqlParameter对象。SqlParameter：代表了数据库中的参数。构造函数：SqlParameter()创建一个空的数据库变量对象，需要设置ParameterName：参数对象对应的数据库的变量名字SqlDbType:参数对象在数据库中的数据类型Size:数据类型的大小Value：变量的值。如果参数不需要制定长度则使用构造函数SqlPatameter(String,SqlDbType)。。 SqlParameter(String,SqlDbType,Size) 设置了参数的名称，类型，大小。

常见的传参的形式：

View Code

 1 using System;
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 
 5 public class StudyADO02
 6 {
 7     public static void Main()
 8     {
 9         string strcon = "server=.;database=blog;uid=sa;pwd=1";
10         SqlConnection con = new SqlConnection(strcon);
11         SqlCommand cmd = new SqlCommand();
12         
13         using(con)
14         {
15             
16             using(cmd)
17             {
18                 //把已经打开数据库的连接赋值给cmd,让cmd对象知道SQL语句要在那个数据库上执行
19                 cmd.Connection = con;
20                 SqlParameter pname = new SqlParameter("@name",SqlDbType.VarChar,20);
21                 SqlParameter psex = new SqlParameter("@sex",SqlDbType.Bit);
22                 SqlParameter page = new SqlParameter("@age",SqlDbType.Int,4);
23                 SqlParameter psalary = new SqlParameter("@salary",SqlDbType.Int,4);
24                 SqlParameter paddrss = new SqlParameter();
25                 
26                 pname.Value = "陈一冰";
27                 psex.Value = 1;
28                 page.Value = 39;
29                 psalary.Value = 7000;
30                 paddrss.Value = "china";
31                 cmd.Parameters.Add(pname);
32                 cmd.Parameters.Add(psex);
33                 cmd.Parameters.Add(page);
34                 cmd.Parameters.Add(psalary);
35                 cmd.Parameters.Add(paddrss);
36 
37                 //设置要执行的sql语句                
38                 cmd.CommandText = "insert into employee values (@name,@sex,@age,@salary,@addrss)";
39                 //设置执行SQL语句的方式
40                 cmd.CommandType = CommandType.Text;
41                 //开始执行
42                 con.Open();
43                 int i=cmd.ExecuteNonQuery();
44                 if(i!=0)
45                 {
46                     Console.WriteLine("执行成功");
47                 }
48             }
49         }
50         
51     }
52 }

SqlParameter参数不同演变中的三个形态：

View Code

 1 using System;
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 
 5 public class StudyADO02
 6 {
 7     public static void Main()
 8     {
 9         string strcon = "server=.;database=blog;uid=sa;pwd=1";
10         SqlConnection con = new SqlConnection(strcon);
11         SqlCommand cmd = new SqlCommand();
12         
13         using(con)
14         {
15             
16             using(cmd)
17             {
18                 //把已经打开数据库的连接赋值给cmd,让cmd对象知道SQL语句要在那个数据库上执行
19                 cmd.Connection = con;
20                 
21                 
22             /*
23                 //最初的形式：
24                 SqlParametersCollection collection =cmd.Parameters;
25                 collection.Add("@name",SqlDbType.VarChar,20);
26                 SqlParameters p = collection["@name"];
27                 p.Value = "sss";
28 
29                 //上面四句化简为两句
30                 cmd.Parameters.Add("@name",SqlDbType.VarChar,20);
31                 cmd.Parameters["@name"].Value = "ss";
32 
33                 //整体代换，最总可化简为一句
34                 cmd.Parameters.Add("@name",SqlDbType.VarChar,20).Value = "ss"
35             */
36 
37                 //常用形式
38                 cmd.Parameters.Add("@name",SqlDbType.VarChar,20);
39                 cmd.Parameters["@name"].Value = "ss";
40 
41                 //设置要执行的sql语句                
42                 cmd.CommandText = "insert into employee values (@name,@sex,@age,@salary,@addrss)";
43                 //设置执行SQL语句的方式
44                 cmd.CommandType = CommandType.Text;
45                 //开始执行
46                 con.Open();
47                 int i=cmd.ExecuteNonQuery();
48                 if(i!=0)
49                 {
50                     Console.WriteLine("执行成功");
51                 }
52             }
53         }
54         
55     }
56 }

以上示例全部为非查询语句，下面所讲述的为查询语句：ExecuteReader():用来执行查询语句，返回SqlDataReader对象SqlDataReader数据读取器，只能向下读，而起是只读数据，并且在读取数据的时候不能和数据库断开连接。当使用完毕后，记着释放资源。常用属性 HasRows：判断是否有记录。 FieldCount:实际查询出得结果中有多少列。 Item[index]:根据列的索引拿出结果。 Item[name]:根据列的名字拿出结果。常用方法： Read：首先判断有没有记录，如果有记录的话，则下移一行。 Close:关闭
查询语句且有参数的例子：

View Code

 1 using System;
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 public class StudyReader
 5 {
 6     public static void Main()
 7     {
 8         string strcon  = "server=.;database=blog;uid=sa;pwd=1";
 9         SqlConnection  con = new SqlConnection(strcon);
10         SqlCommand cmd = new SqlCommand();
11         cmd.Connection = con;
12         cmd.CommandText = "select *     from employee where eid=@eid";
13         cmd.Parameters.Add("@eid",SqlDbType.Int,4);
14         cmd.Parameters["@eid"].Value = 23;
15         con.Open();
16         //把查询结果返回出来，并赋值给SqlDataReader
17         SqlDataReader reader = cmd.ExecuteReader();
18         while(reader.Read())
19         {
20             Console.WriteLine(reader["ename"].ToString()+"   "+reader["eaddress"].ToString());
21         }
22         reader.Close();
23         cmd.Dispose();
24         con.Close();
25         
26     }
27 }

把上面代码换为using来释放资源：

View Code

 1 using System;
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 public class StudyReader
 5 {
 6     public static void Main()
 7     {
 8         string strcon  = "server=.;database=blog;uid=sa;pwd=1";
 9         SqlConnection  con = new SqlConnection(strcon);
10         SqlCommand cmd = new SqlCommand();
11         using(con)
12         {
13             using(cmd)
14             {
15                 cmd.Connection = con;
16                 cmd.CommandText = "select *     from employee where eid=@eid";
17                 cmd.Parameters.Add("@eid",SqlDbType.Int,4);
18                 cmd.Parameters["@eid"].Value = 23;
19                 con.Open();
20                 //把查询结果返回出来，并赋值给SqlDataReader
21                 SqlDataReader reader = cmd.ExecuteReader();
22                 using(reader)
23                 {
24                     while(reader.Read())
25                     {
26                         Console.WriteLine(reader["ename"].ToString()+"   "+reader["eaddress"].ToString());
27                     }
28                 }
29                 
30                     
31             }
32         }
33         
34         
35         
36     }
37 }

有了上面的基础我们就可以做一个自己的数据库助手类，就是我们日常生活中在搭建三成构架的时候要创建的SqlHelper:

创建数据库助手类的步骤：

/* 1,创建ADO.NEt对象 2,初始化sql和参数 3,执行 4,关闭 */

View Code

  1 using System;
  2 using System.Data;
  3 using System.Data.SqlClient;
  4 public class TestHelper
  5 {
  6     public static void Main()
  7     {
  8         string strcon = "server = .;database = blog;uid = sa;pwd = 1";
  9         SqlHelper helper = new SqlHelper(strcon);
 10         /*
 11         //查找员工表中员工姓名和住址字段（无参查询语句）
 12         string sql = "select * from employee";
 13         SqlDataReader reader = helper.ExecuteQuery(sql);
 14         using(reader)
 15         {
 16             while(reader.Read())
 17             {
 18                 Console.WriteLine(reader["eid"].ToString() + " " + reader["ename"].ToString());
 19             }
 20         }
 21         helper.Close();*/
 22         //含参的非查询语句
 23         string sql = "insert into employee values(@name,@sex,@age,@salary,@address)";
 24         SqlParameter[] p = new SqlParameter[]{
 25             new SqlParameter("@name",SqlDbType.VarChar,20),
 26             new SqlParameter("@sex",SqlDbType.Bit),
 27             new SqlParameter("@age",SqlDbType.Int),
 28             new SqlParameter("@salary",SqlDbType.Int),
 29             new SqlParameter("@address",SqlDbType.VarChar,20)
 30         };
 31         p[0].Value = "qqq";
 32         p[1].Value = 0;
 33         p[2].Value = 23;
 34         p[3].Value = 5000;
 35         p[4].Value = "China";
 36         helper.ExecuteNonQuery(sql,p);
 37         helper.Close();
 38     }
 39 }
 40 public class SqlHelper
 41 {
 42     private SqlConnection con = null;
 43     private SqlCommand cmd = null;
 44     private string connectionString;
 45     public string ConnectionString
 46     {
 47         get{return this.connectionString;}
 48         set{this.connectionString = value;}
 49     }
 50 
 51     public SqlHelper (string strcon)
 52     {
 53         this.connectionString = strcon;
 54         con = new SqlConnection(strcon);
 55         cmd = new SqlCommand();
 56         cmd.Connection = con;
 57     }
 58     /*
 59         执行非查询语句
 60     */
 61     //执行含有参数的SQL语句
 62     public int ExecuteNonQuery(string sql,params SqlParameter[] param)
 63     {
 64         PreparedCommand(sql,param);
 65         int result = cmd.ExecuteNonQuery();
 66         con.Close();
 67         return result;
 68     }
 69     //重载上述方法，查询语句中没有参数。
 70     public int ExecuteNonQuery(string sql)
 71     {
 72         PreparedCommand(sql,null);
 73         int result = cmd.ExecuteNonQuery();
 74         con.Close();
 75         return result;
 76     }
 77     /*
 78         执行查询语句
 79     */
 80     //执行含有参数的SQL语句
 81     public SqlDataReader ExecuteQuery(string sql,params SqlParameter[] param)
 82     {
 83         PreparedCommand(sql,param);
 84         return cmd.ExecuteReader();
 85         
 86     }
 87     //重载上述方法，查询语句中没有参数。
 88     public SqlDataReader ExecuteQuery(string sql)
 89     {
 90         PreparedCommand(sql,null);
 91         return cmd.ExecuteReader();
 92     }
 93     private void PreparedCommand(string sql,params SqlParameter[] param)
 94     {
 95         /*
 96         SqlCommand cmd1 = null;
 97         if(cmd != null)
 98         {
 99             cmd1 = cmd;
100         }
101         else
102         {
103             cmd1 = new SqlCommand();
104         }
105         */
106         cmd.CommandText = sql;
107         //清空Parameters中的参数
108         cmd.Parameters.Clear();
109         if(param != null)
110         {
111             foreach(SqlParameter p in param)
112             {
113                 cmd.Parameters.Add(p);
114             }
115         }
116         con.Open();;
117     }
118     /*
119         打开数据库连接
120     */
121     private void Open()
122     {
123         con.Open();
124     }
125     /*
126         关闭数据库连接
127     */
128     public void Close()
129     {
130         cmd.Dispose();
131         con.Close();
132     }
133 }

最后要写一个把数据库数据转换为对象的程序：

View Code

 1 using System;
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 using System.Collections.Generic;
 5 public class StudyModel
 6 {
 7     public static void Main()
 8     {
 9         string strcon = "server = .;database = blog;uid = sa;pwd = 1";
10         SqlConnection con = new SqlConnection(strcon);
11         SqlCommand cmd = new SqlCommand();
12         List<Employee> list = new List<Employee>();
13         using(con)
14         {
15             using(cmd)
16             {
17                 cmd.Connection = con;
18                 cmd.CommandText = "select * from employee";
19                 con.Open();
20                 SqlDataReader reader = cmd.ExecuteReader();
21                 using(reader)
22                 {
23                     Employee p = null;
24                     while(reader.Read())
25                     {
26                         p = new Employee();
27                         p.Eid = Convert.ToInt32(reader["eid"]);
28                         p.Ename = reader["ename"].ToString();
29                         p.Esex = Convert.ToInt32(reader["esex"]);
30                         p.Eage = Convert.ToInt32(reader["eage"]);
31                         p.Esalary = Convert.ToInt32(reader["esalary"]);
32                         p.Eaddress = reader["eaddress"].ToString();
33                         list.Add(p);
34                     }
35                 }
36             }
37         }
38         foreach(Employee pp in list)
39         {
40             Console.WriteLine(pp.Ename + "   " + pp.Esex);
41         }
42         //Linq语句
43         /*
44             var result = from ps in list
45                      where ps.Eage >30
46                      select ps;
47             foreach(Employee ep in result)
48             {
49                 Console.WriteLine(ep.Ename);
50             }
51         */
52     }
53 }
54 /*
55     实体类，模型类
56 */
57 public class Employee
58 {
59     public int Eid
60     {
61         get;set;
62     }
63     public string Ename
64     {
65         get;set;
66     }
67     public int Esex
68     {
69         get;set;
70     }
71     public int Eage
72     {
73         get;set;
74     }
75     public int Esalary
76     {
77         get;set;
78     }
79     public string Eaddress
80     {
81         get;set;
82     }
83 }

posted on 2012-08-08 21:10 bergy 阅读(2585) 评论(8) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部