练习：Ado.Net 数据库增删改查--面向对象操作

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 
 6 namespace ConsoleApplication1
 7 {
 8     //实体类 (封装字段、属性)
 9     public class Nation
10     {
11         private string code;
12 
13         public string Code
14         {
15             get { return code; }
16             set { code = value; }
17         }
18 
19         private string name;
20 
21         public string Name
22         {
23             get { return name; }
24             set { name = value; }
25         }
26     }
27 }

实体类

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.Data.SqlClient;
 6 
 7 namespace ConsoleApplication1
 8 {
 9     //数据连接类：提供数据连接对象
10     public class DataConnection
11     {
12         private static string connstr = "server=.; database=mydb; user=sa; pwd=ray;";
13               
14         public static SqlConnection Conn
15         {
16             get { return new SqlConnection(connstr);}
17         }
18     }
19 }

数据连接类

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Data.SqlClient;
  6 
  7 namespace ConsoleApplication1
  8 {
  9     //数据访问类（实现增删改查操作）
 10     class NationData
 11     {
 12         private SqlConnection _conn; //连接对象
 13         private SqlCommand _cmd;    //命令对象
 14         private SqlDataReader _dr;  //读取器对象
 15 
 16         //构造方法，初始化成员（连接对象、命令对象）
 17         public NationData()
 18         {
 19             _conn = DataConnection.Conn; //对连接对象进行初始化
 20             _cmd = _conn.CreateCommand(); //对命令对象进行初始化 
 21         }
 22 
 23         /// <summary>
 24         /// 添加数据方法
 25         /// </summary>
 26         /// <param name="code">编号</param>
 27         /// <param name="name">名称</param>
 28         /// <returns>bool</returns>
 29         public bool Add(string code, string name)
 30         {
 31             _cmd.CommandText = "insert into Nation values(@code,@name)";
 32             _cmd.Parameters.Clear();
 33             _cmd.Parameters.AddWithValue("@code", code);
 34             _cmd.Parameters.AddWithValue("@name", name);
 35 
 36             _conn.Open();
 37             int n = _cmd.ExecuteNonQuery();
 38             _conn.Close();
 39 
 40             if (n > 0)
 41             {
 42                 return true;
 43             }
 44             else
 45             {
 46                 return false;
 47             }
 48         }
 49 
 50         /// <summary>
 51         /// 查询所有数据方法
 52         /// </summary>
 53         /// <returns>List<T></returns>
 54         public List<Nation> Select()
 55         {
 56             _cmd.CommandText = "select *from Nation";
 57             _conn.Open();
 58             _dr = _cmd.ExecuteReader();
 59 
 60             //定义一个空的集合
 61             List<Nation> list=new List<Nation>();
 62 
 63             if (_dr.HasRows)
 64             {
 65                 while (_dr.Read())
 66                 {
 67                     //造一个Nation对象
 68                     Nation data = new Nation();
 69                     data.Code = _dr["Code"].ToString();
 70                     data.Name = _dr["Name"].ToString();
 71 
 72                     //扔到集合中
 73                     list.Add(data);
 74                 }
 75             }
 76             _conn.Close();
 77             return list;
 78         }
 79 
 80         /// <summary>
 81         /// 根据条件查询方法
 82         /// </summary>
 83         /// <param name="code">编号</param>
 84         /// <returns>List<T></returns>
 85         public List<Nation> Select(string code)
 86         {
 87             _cmd.CommandText = "select *from Nation where Code=@code";
 88             _cmd.Parameters.Clear();
 89             _cmd.Parameters.AddWithValue("@code",code);
 90             _conn.Open();
 91             _dr = _cmd.ExecuteReader();
 92 
 93 
 94             //定义一个空的集合
 95             List<Nation> list = new List<Nation>();
 96 
 97             if (_dr.HasRows)
 98             {
 99                 while (_dr.Read())
100                 {
101                     //造一个Nation对象
102                     Nation data = new Nation();
103                     data.Code = _dr["Code"].ToString();
104                     data.Name = _dr["Name"].ToString();
105 
106                     //扔到集合中
107                     list.Add(data);
108                 }
109             }
110             _conn.Close();
111             return list;
112         }
113 
114         /// <summary>
115         /// 删除数据方法
116         /// </summary>
117         /// <param name="code">编号</param>
118         /// <returns>bool</returns>
119         public bool Delete(string code)
120         {
121             _cmd.CommandText = "delete from Nation where Code=@code";
122             _cmd.Parameters.Clear();
123             _cmd.Parameters.AddWithValue("@code",code);
124 
125             _conn.Open();
126             int n=_cmd.ExecuteNonQuery();
127             _conn.Close();
128 
129             if ( n> 0)
130             {
131                 return true;
132             }
133             else { return false; }
134         }
135 
136         /// <summary>
137         /// 修改数据方法
138         /// </summary>
139         /// <param name="code">编号</param>
140         /// <param name="name">名称</param>
141         /// <returns>bool</returns>
142         public bool Update(string code,string name)
143         {
144             _cmd.CommandText = "update Nation set Name=@name where Code=@code";
145             _cmd.Parameters.Clear();
146             _cmd.Parameters.AddWithValue("@code",code);
147             _cmd.Parameters.AddWithValue("@name",name);
148 
149             _conn.Open();
150             int n = _cmd.ExecuteNonQuery();
151             _conn.Close();
152 
153             if (n > 0)
154             {
155                 return true;
156             }
157             else { return false; }
158       
159         }
160     }
161 }

数据访问类

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 
  6 namespace ConsoleApplication1
  7 {
  8     class Program
  9     {
 10         static void Main(string[] args)
 11         {
 12             NationData data = new NationData();
 13             while (true)
 14             {
 15                 Console.WriteLine("请输入要对数据进行的操作（1：添加、2：修改、3：删除、4：查询）：");
 16                 string Operation = Convert.ToString(Console.ReadLine());
 17                 if (Operation == "1")
 18                 {
 19                     #region 添加数据
 20                     Console.Write("请输入要添加的编号：");
 21                     string code = Console.ReadLine();
 22                     Console.Write("请输入要添加的名称：");
 23                     string name = Console.ReadLine();
 24 
 25                     if (data.Add(code, name))
 26                     {
 27                         Console.WriteLine("添加成功，请按任意键刷新数据");
 28                         Console.ReadKey();
 29                         Console.Clear();
 30 
 31                         Console.WriteLine("=============刷新数据==============");
 32                         List<Nation> listSelect = data.Select();
 33                         foreach (Nation dataSelect in listSelect)
 34                         {
 35                             Console.WriteLine(dataSelect.Code + "	" + dataSelect.Name);
 36                         }
 37                     }
 38                     else
 39                     {
 40                         Console.WriteLine("添加失败");
 41                     }
 42                     Console.ReadKey();
 43                     #endregion
 44 
 45                     break;
 46                 }
 47                 if (Operation == "2")
 48                 {
 49                     #region 修改数据
 50                     Console.Write("请输入要修改的编号：");
 51                     String codeUpdate = Console.ReadLine();
 52                     Console.Write("请输入修改后的名称：");
 53                     string nameUpdate = Console.ReadLine();
 54 
 55                     if (data.Update(codeUpdate, nameUpdate))
 56                     {
 57                         Console.WriteLine("修改成功，请按任意键刷新数据");
 58                         Console.ReadKey();
 59                         Console.Clear();
 60 
 61                         Console.WriteLine("==============刷新数据=============");
 62                         List<Nation> listSelect = data.Select();
 63                         foreach (Nation dataSelect in listSelect)
 64                         {
 65                             Console.WriteLine(dataSelect.Code + "	" + dataSelect.Name);
 66                         }
 67                     }
 68                     else { Console.WriteLine("修改失败"); }
 69                     Console.ReadKey();
 70                     #endregion
 71 
 72                     break;
 73                 }
 74                 if (Operation == "3")
 75                 {
 76                     #region 删除数据
 77                     Console.WriteLine("请输入要删除数据的编号：");
 78                     string codeDelete = Console.ReadLine();
 79 
 80                     List<Nation> list = data.Select(codeDelete);
 81                     Console.WriteLine("这是你要删除的数据：");
 82                     foreach (Nation data1 in list)
 83                     {
 84                         Console.WriteLine(data1.Code + "	" + data1.Name);
 85                     }
 86                     Console.WriteLine("确认删除这条数据么？（Y/N）");
 87                     string s = Console.ReadLine();
 88                     if (s == "Y")
 89                     {
 90                         if (data.Delete(codeDelete))
 91                         {
 92                             Console.WriteLine("删除成功，按任意键刷新数据");
 93                             Console.ReadKey();
 94                             Console.Clear();
 95 
 96                             Console.WriteLine("=============刷新数据==============");
 97                             List<Nation> listSelect = data.Select();
 98                             foreach (Nation dataSelect in listSelect)
 99                             {
100                                 Console.WriteLine(dataSelect.Code + "	" + dataSelect.Name);
101                             }
102                         }
103                         else { Console.WriteLine("删除失败"); }
104                     }
105                     else { Console.WriteLine("数据保留"); }
106                     Console.ReadKey();
107                     #endregion
108 
109                     break;
110                 }
111 
112                 if (Operation == "4")
113                 {
114                     #region 条件查询数据
115                     Console.Write("请输入要查询的编号：");
116                     string code = Console.ReadLine();
117                     List<Nation> list = data.Select(code);
118                     foreach (Nation datass in list)
119                     {
120                         Console.WriteLine(datass.Code+"	"+datass.Name);
121                     }
122                     Console.ReadKey();
123                     #endregion
124 
125                     break;
126                 }
127                 else { Console.WriteLine("输入有误"); }
128             }
129         }
130     }
131 }