数据访问----封装类实例

数据库

 1 create database mydb
 2 go
 3 use mydb
 4 go
 5 create table login
 6 (
 7     username varchar(50) primary key,
 8     password varchar(50)
 9 )
10 go
11 insert into login values('aaa','aaa')
12 insert into login values('bbb','aaa')
13 insert into login values('ccc','aaa')
14 insert into login values('ddd','aaa')
15 insert into login values('eee','aaa')
16 select * from login
17 
18 create table stock
19 (
20     ids varchar(10) primary key, --股票代号
21     name varchar(50) ,--股票名称
22     start decimal(18,2),--开盘价
23     stop decimal(18,2),--收盘价
24     trans bigint--成交量
25 )
26 go 
27 insert into stock values('s001','济南轻骑',8.5,8.7,59874)
28 insert into stock values('s002','长江股份',7.2,7.0,35659)
29 insert into stock values('s003','浦发银行',6.5,7.1,32120)
30 insert into stock values('s004','山东铝业',3.5,3.1,36520)
31 insert into stock values('s005','铁龙股份',5.6,5.9,55460)
32 insert into stock values('s006','金牛股份',6.3,5.4,98630)
33 insert into stock values('s007','辽渔集团',12.1,11.4,36541)
34 insert into stock values('s008','中华电力',9.6,8.7,25698)
35 select * from stock

View Code

链接类

1 class DBCon
2     {
3         public const string CONSTR = "server=.;database=mydb;uid=sa;pwd=111111";
4     }

View Code

login实体类

 1  class Login
 2     {
 3         private string _username;
 4 
 5         public string Username
 6         {
 7             get { return _username; }
 8             set { _username = value; }
 9         }
10         private string _password;
11 
12         public string Password
13         {
14             get { return _password; }
15             set { _password = value; }
16         }
17     }

View Code

loginDA数据访问类

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Threading.Tasks;
  6 using System.Data;
  7 using System.Data.SqlClient;
  8 
  9 namespace uu
 10 {
 11     class LoginDA
 12     {
 13         private SqlConnection _con;
 14         private SqlCommand _cmd;
 15         private SqlDataReader _dr;
 16         public LoginDA()
 17         {
 18             _con = new SqlConnection(DBCon.CONSTR);
 19             _cmd = _con.CreateCommand();
 20         }
 21         public void Insert(string username,string password)
 22         {
 23             _cmd.CommandText = "insert into login values(@username,@password)";
 24             _cmd.Parameters.Clear();
 25             _cmd.Parameters.AddWithValue("@username",username);
 26             _cmd.Parameters.AddWithValue("@password",password);
 27             try
 28             {
 29                 _con.Open();
 30                 _cmd.ExecuteNonQuery();
 31             }
 32             finally
 33             {
 34                 _con.Close();
 35             }
 36         }
 37         public void Delete(string username)
 38         {
 39             _cmd.CommandText = "delete from login where username=@username";
 40             _cmd.Parameters.Clear();
 41             _cmd.Parameters.AddWithValue("@username",username);
 42             try
 43             {
 44                 _con.Open();
 45                 _cmd.ExecuteNonQuery();
 46             }
 47             finally
 48             {
 49                 _con.Close();
 50             }
 51         }
 52         public void Update(string username, string password)
 53         {
 54             _cmd.CommandText = "update login set username= @username where password=@password";
 55             _cmd.Parameters.Clear();
 56             _cmd.Parameters.AddWithValue("@username", username);
 57             _cmd.Parameters.AddWithValue("@password", password);
 58             try
 59             {
 60                 _con.Open();
 61                 _cmd.ExecuteNonQuery();
 62             }
 63             finally
 64             {
 65                 _con.Close();
 66             }
 67         }
 68         public List<Login> Select()
 69         {
 70             _cmd.CommandText = "select * from login";
 71             try
 72             {
 73                 _con.Open();
 74                 _dr = _cmd.ExecuteReader();
 75                 if (_dr.Read())
 76                 {
 77                     List<Login> list = new List<Login>();
 78                     Login data = new Login();
 79                     data.Username = _dr["username"].ToString();
 80                     data.Password = _dr["password"].ToString();
 81                     list.Add(data);
 82                     return list;
 83                 }
 84                 else
 85                 {
 86                     return null;
 87                 }
 88             }
 89             finally
 90             {
 91                 _con.Close();
 92             }
 93         }
 94         public Login Select(string username)
 95         {
 96             _cmd.CommandText = "select * from login where username=@username";
 97             _cmd.Parameters.Clear();
 98             _cmd.Parameters.AddWithValue("@username", username);
 99             try
100             {
101                 _con.Open();
102                 _dr = _cmd.ExecuteReader();
103                 if (_dr.Read())
104                 {
105                     Login data = new Login();
106                     data.Username = _dr["username"].ToString();
107                     data.Password = _dr["password"].ToString();
108                     return data;
109                 }
110                 else
111                 {
112                     return null;
113                 }
114             }
115             finally
116             {
117                 _con.Close();
118             }
119         }
120 
121     }
122 }

View Code

stock实体类

 1     class Stock
 2     {
 3         private string _ids;
 4 
 5         public string Ids
 6         {
 7             get { return _ids; }
 8             set { _ids = value; }
 9         }
10         private string _name;
11 
12         public string Name
13         {
14             get { return _name; }
15             set { _name = value; }
16         }
17         private decimal _start;
18 
19         public decimal Start
20         {
21             get { return _start; }
22             set { _start = value; }
23         }
24         private decimal _stop;
25 
26         public decimal Stop
27         {
28             get { return _stop; }
29             set { _stop = value; }
30         }
31         private long _trans;
32 
33         public long Trans
34         {
35             get { return _trans; }
36             set { _trans = value; }
37         }
38     }

View Code

stockDA数据访问类

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Threading.Tasks;
  6 using System.Data;
  7 using System.Data.SqlClient;
  8 
  9 namespace uu
 10 {
 11     class StockDA
 12     {
 13         private SqlConnection _Conn;
 14         private SqlCommand _Cmd;
 15         private SqlDataReader _DR;
 16         public StockDA()
 17         {
 18             _Conn = new SqlConnection(DBCon.CONSTR);
 19             _Cmd = _Conn.CreateCommand();
 20         }
 21         public void Insert(string ids,string name,decimal start,decimal stop,long trans)
 22         {
 23             _Cmd.CommandText = "insert into stock values(@ids,@name,@start,@stop,@trans)";
 24             _Cmd.Parameters.Clear();
 25             _Cmd.Parameters.AddWithValue("@ids", ids);
 26             _Cmd.Parameters.AddWithValue("@name", name);
 27             _Cmd.Parameters.AddWithValue("@start", start);
 28             _Cmd.Parameters.AddWithValue("@stop", stop);
 29             _Cmd.Parameters.AddWithValue("@trans", trans);
 30             try
 31             {
 32                 _Conn.Open();
 33                 _Cmd.ExecuteNonQuery();
 34             }
 35             finally
 36             {
 37                 _Conn.Close();
 38             }
 39         }
 40         public void Delete(string ids)
 41         {
 42             _Cmd.CommandText = "delete from stock where ids=@ids";
 43             _Cmd.Parameters.Clear();
 44             _Cmd.Parameters.AddWithValue("@ids", ids);
 45             try
 46             {
 47                 _Conn.Open();
 48                 _Cmd.ExecuteNonQuery();
 49             }
 50             finally
 51             {
 52                 _Conn.Close();
 53             }
 54         }
 55 
 56         public void Update(string ids, string name, decimal start, decimal stop, long trans)
 57         {
 58             _Cmd.CommandText = "update stock set name=@name,start=@start,stop=@stop,trans=@trans where ids=@ids";
 59             _Cmd.Parameters.Clear();
 60             _Cmd.Parameters.AddWithValue("@ids", ids);
 61             _Cmd.Parameters.AddWithValue("@name", name);
 62             _Cmd.Parameters.AddWithValue("@start", start);
 63             _Cmd.Parameters.AddWithValue("@stop", stop);
 64             _Cmd.Parameters.AddWithValue("@trans", trans);
 65             try
 66             {
 67                 _Conn.Open();
 68                 _Cmd.ExecuteNonQuery();
 69             }
 70             finally
 71             {
 72                 _Conn.Close();
 73             }
 74         }
 75         public void Update(string ids, string name, decimal start, decimal stop)
 76         {
 77             _Cmd.CommandText = "update stock set name=@name,start=@start,stop=@stop where ids=@ids";
 78             _Cmd.Parameters.Clear();
 79             _Cmd.Parameters.AddWithValue("@ids", ids);
 80             _Cmd.Parameters.AddWithValue("@name", name);
 81             _Cmd.Parameters.AddWithValue("@start", start);
 82             _Cmd.Parameters.AddWithValue("@stop", stop);
 83             try
 84             {
 85                 _Conn.Open();
 86                 _Cmd.ExecuteNonQuery();
 87             }
 88             finally
 89             {
 90                 _Conn.Close();
 91             }
 92         }
 93         public void Update(string ids, decimal start, decimal stop, long trans)
 94         {
 95             _Cmd.CommandText = "update stock set start=@start,stop=@stop,trans=@trans where ids=@ids";
 96             _Cmd.Parameters.Clear();
 97             _Cmd.Parameters.AddWithValue("@ids", ids);
 98             _Cmd.Parameters.AddWithValue("@start", start);
 99             _Cmd.Parameters.AddWithValue("@stop", stop);
100             _Cmd.Parameters.AddWithValue("@trans", trans);
101             try
102             {
103                 _Conn.Open();
104                 _Cmd.ExecuteNonQuery();
105             }
106             finally
107             {
108                 _Conn.Close();
109             }
110         }
111         public void Update(string ids, decimal start, decimal stop)
112         {
113             _Cmd.CommandText = "update stock setstart=@start,stop=@stop where ids=@ids";
114             _Cmd.Parameters.Clear();
115             _Cmd.Parameters.AddWithValue("@ids", ids);
116             _Cmd.Parameters.AddWithValue("@start", start);
117             _Cmd.Parameters.AddWithValue("@stop", stop);
118             try
119             {
120                 _Conn.Open();
121                 _Cmd.ExecuteNonQuery();
122             }
123             finally
124             {
125                 _Conn.Close();
126             }
127         }
128         public void Update(string ids, long trans)
129         {
130             _Cmd.CommandText = "update stock set trans=@trans where ids=@ids";
131             _Cmd.Parameters.Clear();
132             _Cmd.Parameters.AddWithValue("@ids", ids);
133             _Cmd.Parameters.AddWithValue("@trans", trans);
134             try
135             {
136                 _Conn.Open();
137                 _Cmd.ExecuteNonQuery();
138             }
139             finally
140             {
141                 _Conn.Close();
142             }
143         }
144         public void Update(string ids, string name)
145         {
146             _Cmd.CommandText = "update stock set name=@name where ids=@ids";
147             _Cmd.Parameters.Clear();
148             _Cmd.Parameters.AddWithValue("@ids", ids);
149             _Cmd.Parameters.AddWithValue("@name", name);
150             try
151             {
152                 _Conn.Open();
153                 _Cmd.ExecuteNonQuery();
154             }
155             finally
156             {
157                 _Conn.Close();
158             }
159         }
160 
161         public List<Stock> Select()
162         {
163             _Cmd.CommandText = "select * from stock";
164             List<Stock> list = new List<Stock>();
165             try
166             {
167                 _Conn.Open();
168                 _DR = _Cmd.ExecuteReader();
169                 while (_DR.Read())
170                 {
171                     Stock data = new Stock();
172                     data.Ids = _DR["ids"].ToString();
173                     data.Name = _DR["name"].ToString();
174                     data.Start = Convert.ToDecimal(_DR["start"]);
175                     data.Stop = Convert.ToDecimal(_DR["stop"]);
176                     data.Trans = Convert.ToInt64(_DR["trans"]);
177                     list.Add(data);
178                 }
179             }
180             finally
181             {
182                 _Conn.Close();
183             }
184             return list;
185         }
186 
187         public Stock Select(string ids)
188         {
189             
190             _Cmd.CommandText = "select * from stock where ids=@ids";
191             _Cmd.Parameters.Clear();
192             _Cmd.Parameters.AddWithValue("@ids", ids);
193             try
194             {
195                 _Conn.Open();
196                 _DR = _Cmd.ExecuteReader();
197                 if (_DR.Read())
198                 {
199                     Stock data = new Stock();
200                     data.Ids = _DR["ids"].ToString();
201                     data.Name = _DR["name"].ToString();
202                     data.Start = Convert.ToDecimal(_DR["start"]);
203                     data.Stop = Convert.ToDecimal(_DR["stop"]);
204                     data.Trans = Convert.ToInt64(_DR["trans"]); return data;
205                 }
206                 else
207                 {
208                     return null;
209                 }
210             }
211             finally
212             {
213                 _Conn.Close();
214             }
215            
216         }
217 
218 
219     }
220 }

View Code

显示界面

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Threading.Tasks;
  6 
  7 namespace uu
  8 {
  9     class Program
 10     {
 11         /// <summary>
 12         /// 界面
 13         /// </summary>
 14         /// <param name="args"></param>
 15         static void Main(string[] args)
 16         {
 17             while (true)
 18             {
 19 
 20                 Console.WriteLine("股票管理系统：");
 21                 Console.WriteLine("1:登录	0:退出");
 22                 string t = Console.ReadLine();
 23                 if (t.Equals("1"))
 24                 {
 25                     LoginDA user = new LoginDA();
 26                     string uid, pwd, ids, name; decimal start, stop; long trans;
 27                     do
 28                     {
 29                         Console.Write("请输入用户名/退出(0)：");
 30                         uid = Console.ReadLine();
 31                     } while (user.Select(uid) == null && uid != "0");
 32                     if (uid.Equals("0"))
 33                     {
 34                         break;
 35                     }
 36                     do
 37                     {
 38                         Console.Write("请输入密码/退出(0)：");
 39                         pwd = Console.ReadLine();
 40 
 41                     } while (user.Select(uid).Password != pwd && pwd != "0");
 42                     if (pwd.Equals("0"))
 43                     {
 44                         break;
 45                     }
 46                     while (true)
 47                     {
 48                         Console.WriteLine("股票管理：	1：增加股票	2：删除股票	3：修改股票	4：查询股票	0：退出管理
");
 49                         string tr = Console.ReadLine();
 50                         if (tr.Equals("0"))
 51                         {
 52                             break;
 53                         }
 54                         StockDA root = new StockDA();
 55                         switch (tr)
 56                         {
 57                             case "1":
 58                                 Console.Write("***********************增加股票****************************
");    try
 59                                 {
 60                                 do
 61                                 {
 62                                     Console.Write("请输入股票代号/退出(0)：");
 63                                     ids = Console.ReadLine();
 64                                 } while (root.Select(ids) != null && ids != "0");
 65                                 if (ids.Equals("0"))
 66                                 {
 67                                     break;
 68                                 }
 69 
 70                                 Console.Write("请输入股票名称/退出(0)：");
 71                                 name = Console.ReadLine();
 72                                 if (name.Equals("0"))
 73                                 {
 74                                     break;
 75                                 }
 76                             
 77                                     Console.Write("请输入股票开盘价/退出(0)：");
 78                                     start = Convert.ToDecimal(Console.ReadLine());
 79                                     if (start.Equals("0"))
 80                                     {
 81                                         break;
 82                                     }
 83 
 84                                     Console.Write("请输入股票收盘价/退出(0)：");
 85                                     stop = Convert.ToDecimal(Console.ReadLine());
 86                                     if (stop.Equals("0"))
 87                                     {
 88                                         break;
 89                                     }
 90 
 91                                     Console.Write("请输入股票成交量/退出(0)：");
 92                                     trans = Convert.ToInt64(Console.ReadLine());
 93                                     if (trans.Equals("0"))
 94                                     {
 95                                         break;
 96                                     }
 97                                     root.Insert(ids, name, start, stop, trans);
 98 
 99                                 }
100                                 catch
101                                 {
102 
103                                     Console.WriteLine("输入有误！请确认后重新输入……");
104                                 }
105 
106                                 break;
107                             case "2":
108                                 Console.Write("***********************删除股票****************************
");
109                                 do
110                                 {
111                                     Console.Write("请输入股票代号/退出(0)：");
112                                     ids = Console.ReadLine();
113                                 } while (root.Select(ids) == null && ids != "0");
114                                 if (ids.Equals("0"))
115                                 {
116                                     break;
117                                 }
118                                 root.Delete(ids);
119 
120                                 break;
121                             case "3":
122                                 Console.Write("***********************修改股票****************************
");try
123                                 {
124                                 do
125                                 {
126                                     Console.Write("请输入股票代号/退出(0)：");
127                                     ids = Console.ReadLine();
128                                 } while (root.Select(ids) == null && ids != "0");
129                                 if (ids.Equals("0"))
130                                 {
131                                     break;
132                                 }
133 
134                                 Console.Write("请输入股票名称/退出(0)：");
135                                 name = Console.ReadLine();
136                                 if (name.Equals("0"))
137                                 {
138                                     break;
139                                 }
140                                 
141                                     Console.Write("请输入股票开盘价/退出(0)：");
142                                     start = Convert.ToDecimal(Console.ReadLine());
143                                     if (start.Equals("0"))
144                                     {
145                                         break;
146                                     }
147 
148                                     Console.Write("请输入股票收盘价/退出(0)：");
149                                     stop = Convert.ToDecimal(Console.ReadLine());
150                                     if (stop.Equals("0"))
151                                     {
152                                         break;
153                                     }
154 
155                                     Console.Write("请输入股票成交量/退出(0)：");
156                                     trans = Convert.ToInt64(Console.ReadLine());
157                                     if (trans.Equals("0"))
158                                     {
159                                         break;
160                                     }
161 
162                                     root.Update(ids, name, start, stop, trans);
163                                 }
164                                 catch
165                                 {
166 
167                                     Console.WriteLine("输入有误！请确认后重新输入……");
168                                 }
169 
170 
171 
172                                 break;
173                             case "4":
174                                 Console.Write("***********************查询股票****************************
");
175 
176                                 List<Stock> list=root.Select();
177                                 Console.Write("股票代码	股票名称	开盘价	收盘价	成交量
");
178                                 foreach (Stock i in list)
179                                 {
180                                     Console.Write(i.Ids+"		"+i.Name+"	"+i.Start+"	"+i.Stop+"	"+i.Trans+"
");
181                                 }
182 
183 
184                                 break;
185                             default:
186                                 break;
187 
188                         }
189                     }
190 
191                 }
192                 else
193                 {
194                     break;
195                 }
196 
197 
198             }
199 
200         }
201     }
202 }

View Code