数据访问----实例4
方法1：
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 vv 10 { 11 class Program 12 { 13 static void Main(string[] args) 14 { 15 bool t=true; 16 do{ 17 Console.Write("赈灾物资发放登记系统\n1：物质库存维护\n2：物质库存列表\n3：物质发放\n4：物质发放统计\n5：退出系统\n"); 18 string tr = Console.ReadLine(); 19 switch (tr) 20 { 21 case "1": 22 kcinsert.insert(); 23 break; 24 case "2": 25 kccha.cha(); 26 break; 27 case "3": 28 fainsert.insert(); 29 break; 30 case "4": 31 facha.cha(); 32 break; 33 case "5": 34 t=false; 35 break; 36 37 default: 38 break; 39 } 40 }while(t); 41 } 42 class kcinsert 43 { 44 public const string CONSTR = "server=.;database=yy;uid=sa;pwd=111111"; 45 public static void addkc(string name,string unit,string num,string price) 46 { 47 SqlConnection scon = new SqlConnection(CONSTR); 48 try 49 { 50 scon.Open(); 51 SqlCommand scmd = scon.CreateCommand(); 52 scmd.CommandText = "insert into kc values('" + name + "','" + num + "','" + unit + "','" + price + "')"; 53 scmd.ExecuteNonQuery(); 54 Console.WriteLine("添加成功！"); 55 56 } 57 finally 58 { 59 scon.Close(); 60 } 61 62 63 } 64 public static void insert() 65 { 66 string name, unit, num, price; 67 Console.WriteLine("****************************物质库存维护****************************"); 68 69 Console.Write("请输入物资名称："); 70 name = Console.ReadLine(); 71 if(hasName(name)) 72 { 73 Console.Write("请输入计量单位："); 74 unit = Console.ReadLine(); 75 Console.Write("请输入库存数量："); 76 num = Console.ReadLine(); 77 Console.Write("请输入物资单价："); 78 price = Console.ReadLine(); 79 addkc(name,unit,num,price); 80 } 81 82 83 84 85 } 86 public static bool hasName(string name) 87 { 88 bool t = true; 89 SqlConnection scon = new SqlConnection(CONSTR); 90 try 91 { 92 scon.Open(); 93 SqlCommand scmd = scon.CreateCommand(); 94 scmd.CommandText = "select * from kc where WzName='" + name + "'"; 95 SqlDataReader re = scmd.ExecuteReader(); 96 if (re.HasRows) 97 { 98 Console.Write("已有货物数据，请输入增加数量:"); 99 string num = Console.ReadLine(); 100 101 updatekc(name, num); 102 t = false; 103 } 104 return t; 105 } 106 finally 107 { 108 scon.Close(); 109 } 110 111 112 113 } 114 public static void updatekc(string name, string num) 115 { 116 SqlConnection scon = new SqlConnection(CONSTR); 117 try 118 { 119 scon.Open(); 120 SqlCommand scmd = scon.CreateCommand(); 121 scmd.CommandText = "update kc set Num=Num+'"+ num + "' where WzName='" + name + "'"; 122 scmd.ExecuteNonQuery(); 123 Console.WriteLine("添加成功！"); 124 125 } 126 finally 127 { 128 scon.Close(); 129 130 } 131 132 } 133 134 } 135 class kccha 136 { 137 public const string CONSTR = "server=.;database=yy;uid=sa;pwd=111111"; 138 public static void cha() 139 { 140 SqlConnection scon = new SqlConnection(CONSTR); 141 try 142 { 143 scon.Open(); 144 SqlCommand scmd = scon.CreateCommand(); 145 scmd.CommandText = "select * from kc"; 146 SqlDataReader re=scmd.ExecuteReader(); 147 Console.WriteLine("物质库存列表如下\n****************************************************"); 148 Console.WriteLine("物资名称\t库存数量\t单价（元）\n"); 149 while (re.Read()) 150 { 151 Console.WriteLine(re[0].ToString()+"\t\t"+re[1].ToString()+re[2].ToString()+"\t\t"+re[3].ToString()+"\n"); 152 } 153 Console.WriteLine("****************************************************"); 154 155 156 } 157 finally 158 { 159 scon.Close(); 160 161 } 162 163 } 164 165 } 166 class fainsert 167 { 168 public const string CONSTR = "server=.;database=yy;uid=sa;pwd=111111"; 169 public static void addkc(string name, string unit, string num, string price) 170 { 171 SqlConnection scon = new SqlConnection(CONSTR); 172 try 173 { 174 scon.Open(); 175 SqlCommand scmd = scon.CreateCommand(); 176 scmd.CommandText = "insert into kc values('" + name + "','" + num + "','" + unit + "','" + price + "')"; 177 scmd.ExecuteNonQuery(); 178 Console.WriteLine("添加成功！"); 179 180 } 181 finally 182 { 183 scon.Close(); 184 185 } 186 187 } 188 public static void insert() 189 { 190 string name,num, receiver; 191 Console.WriteLine("物资发放\n****************************物质库存维护****************************"); 192 do{ 193 Console.Write("请输入发放物资名称："); 194 name = Console.ReadLine(); 195 }while(!hasWuzi(name)); 196 do{ 197 Console.Write("请输入发放数量："); 198 num = Console.ReadLine(); 199 }while(!has(num,name)); 200 Console.Write("接收人："); 201 receiver = Console.ReadLine(); 202 addfa(name,num,receiver); 203 Console.WriteLine("成功发放！"); 204 } 205 public static void addfa(string name,string num,string receiver) 206 { 207 SqlConnection scon = new SqlConnection(CONSTR); 208 try 209 { 210 scon.Open(); 211 SqlCommand scmd = scon.CreateCommand(); 212 scmd.CommandText = "select * from kc where WzName='" + name + "'"; 213 SqlDataReader re = scmd.ExecuteReader(); 214 while (re.Read()) 215 { 216 string price=re["Price"].ToString(); 217 string unit = re["Unit"].ToString(); 218 int n=Convert.ToInt32(num); 219 insertfa(name,n,unit,price,receiver); 220 } 221 222 } 223 finally 224 { 225 scon.Close(); 226 227 } 228 } 229 public static void insertfa(string name,int num,string unit,string price,string receiver) 230 { 231 SqlConnection scon = new SqlConnection(CONSTR); 232 try 233 { 234 scon.Open(); 235 SqlCommand scmd = scon.CreateCommand(); 236 scmd.CommandText = "insert into fafang values('"+name+"','"+num+"','"+unit+"','"+price+"','"+DateTime.Now+"','"+receiver+"')"; 237 scmd.ExecuteNonQuery(); 238 239 } 240 finally 241 { 242 scon.Close(); 243 244 } 245 246 } 247 public static bool has(string num,string name) 248 { 249 bool t = false; 250 int n = Convert.ToInt32(num); 251 SqlConnection scon = new SqlConnection(CONSTR); 252 253 try 254 { 255 scon.Open(); 256 SqlCommand scmd = scon.CreateCommand(); 257 scmd.CommandText = "select * from kc where WzName='" + name + "'"; 258 SqlDataReader re = scmd.ExecuteReader(); 259 re.Read(); 260 int m = (int)re["Num"]; 261 if (m >= n) 262 { 263 t = true; 264 updateKc(m, n, name); 265 266 267 } 268 else 269 { 270 Console.WriteLine("库存不足！！！"); 271 } 272 return t; 273 } 274 finally 275 { 276 scon.Close(); 277 278 } 279 280 281 282 } 283 public static void updateKc(int m,int num,string name) 284 { 285 SqlConnection scon = new SqlConnection(CONSTR); 286 try 287 { 288 scon.Open(); 289 SqlCommand scmd = scon.CreateCommand(); 290 scmd.CommandText = "update kc set Num='"+(m-num)+"' where WzName='"+name+"'"; 291 scmd.ExecuteNonQuery(); 292 Console.WriteLine("添加成功！"); 293 294 } 295 finally 296 { 297 scon.Close(); 298 299 } 300 301 } 302 public static bool hasWuzi(string name) 303 { 304 bool t = false; 305 306 SqlConnection scon = new SqlConnection(CONSTR); 307 try 308 { 309 scon.Open(); 310 SqlCommand scmd = scon.CreateCommand(); 311 scmd.CommandText = "select * from kc where WzName='"+name+"'"; 312 SqlDataReader re=scmd.ExecuteReader(); 313 t = re.HasRows; 314 while (re.Read()) 315 { 316 Console.WriteLine("当前单价："+re["Price"].ToString()+"元\n当前库存："+re["Num"].ToString()+re["Unit"].ToString()+"\n"); 317 } 318 319 } 320 finally 321 { 322 scon.Close(); 323 324 } 325 326 327 328 return t; 329 330 } 331 } 332 class facha 333 { 334 public const string CONSTR = "server=.;database=yy;uid=sa;pwd=111111"; 335 public static void cha() 336 { 337 SqlConnection scon = new SqlConnection(CONSTR); 338 try 339 { 340 scon.Open(); 341 SqlCommand scmd = scon.CreateCommand(); 342 scmd.CommandText = "select * from fafang"; 343 SqlDataReader re = scmd.ExecuteReader(); 344 Console.WriteLine("物资发放统计\n****************************************************"); 345 Console.WriteLine("物资名称\t发放数量\t单价（元）\t接收人\n"); 346 while (re.Read()) 347 { 348 Console.WriteLine(re[1].ToString() + "\t\t" + re[2].ToString() + re[3].ToString() + "\t\t" + re[4].ToString() + "\t\t" + re[6].ToString() + "\n"); 349 } 350 Console.WriteLine("****************************************************"); 351 352 } 353 finally 354 { 355 scon.Close(); 356 357 } 358 359 360 } 361 362 } 363 } 364 }
方法2：
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 namespace ConsoleApplication1 9 { 10 class Program 11 { 12 const string CONNECTIONSTRING = "server=.;database=yy;uid=sa;pwd=111111"; 13 static SqlDataReader GetKcDataReader(string name) 14 { 15 SqlConnection conn = new SqlConnection(CONNECTIONSTRING); 16 17 conn.Open(); 18 SqlCommand cmd = conn.CreateCommand(); 19 cmd.CommandText = "select * from kc where WzName='" + name + "'"; 20 SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); 21 return dr; 22 23 } 24 static decimal GetPrice(string name) 25 { 26 decimal price = 0; 27 SqlDataReader dr = GetKcDataReader(name); 28 dr.Read(); 29 price = Convert.ToDecimal(dr["price"]); 30 dr.Close(); 31 return price; 32 } 33 static int GetNum(string name) 34 { 35 int num = 0; 36 SqlDataReader dr = GetKcDataReader(name); 37 dr.Read(); 38 num = Convert.ToInt32(dr["num"]); 39 dr.Close(); 40 return num; 41 } 42 static string GetUnit(string name) 43 { 44 string unit = ""; 45 SqlDataReader dr = GetKcDataReader(name); 46 dr.Read(); 47 unit = dr["unit"].ToString(); 48 dr.Close(); 49 return unit; 50 } 51 static void AddFaFang(string name, int num, string unit, decimal price, DateTime date, string receiver) 52 { 53 SqlConnection conn = new SqlConnection(CONNECTIONSTRING); 54 try 55 { 56 conn.Open(); 57 SqlCommand cmd = conn.CreateCommand(); 58 cmd.CommandText = "update kc set num = num-" + num + " where WzName='" + name + "' "; 59 cmd.ExecuteNonQuery(); 60 cmd.CommandText = "insert into FaFang values('" + name + "','" + num + "','" + unit + "','" + price + "','" + date + "','" + receiver + "')"; 61 cmd.ExecuteNonQuery(); 62 } 63 finally 64 { 65 conn.Close(); 66 } 67 } 68 static void FaFangTongJi() 69 { 70 Console.WriteLine("物资发放统计如下："); 71 Console.WriteLine("**********************************************************"); 72 SqlConnection conn = new SqlConnection(CONNECTIONSTRING); 73 try 74 { 75 conn.Open(); 76 SqlCommand cmd = conn.CreateCommand(); 77 cmd.CommandText = "select * from fafang"; 78 SqlDataReader dr = cmd.ExecuteReader(); 79 Console.WriteLine("物资名称\t发放数量\t单价（元）\t接收人\n"); 80 while (dr.Read()) 81 { 82 Console.WriteLine(dr[1].ToString() + "\t\t" + dr[2].ToString() + dr[3].ToString() + "\t\t" + dr[4].ToString() + "\t\t" + dr[6].ToString() + "\n"); 83 } 84 85 } 86 finally 87 { 88 conn.Close(); 89 90 } 91 Console.WriteLine("**********************************************************"); 92 93 } 94 static void FaFang() 95 { 96 Console.WriteLine("物资发放"); 97 Console.Write("请输入发放物资："); 98 string name = Console.ReadLine(); 99 if (hasName(name)) 100 { 101 Console.WriteLine("当前单价：" + GetPrice(name) + "元"); 102 Console.WriteLine("当前库存：" + GetNum(name) + GetUnit(name)); 103 Console.Write("请输入发放数量："); 104 int num = Convert.ToInt32(Console.ReadLine()); 105 Console.Write("请输入接收人："); 106 string receiver = Console.ReadLine(); 107 //向发放表插入数据 108 AddFaFang(name, num, GetUnit(name), GetPrice(name), DateTime.Now, receiver); 109 Console.WriteLine("物资发放成功"); 110 111 } 112 else 113 { 114 Console.WriteLine("物资名称输入不正确！"); 115 Console.ReadKey(); 116 } 117 } 118 public static bool hasName(string name) 119 { 120 bool t = false; 121 SqlDataReader dr = GetKcDataReader(name); 122 t = dr.HasRows; 123 dr.Close(); 124 return t; 125 126 } 127 static void ShowKC() 128 { 129 Console.WriteLine("物资库存列表如下："); 130 Console.WriteLine("**********************************************************"); 131 Console.WriteLine("物资名称\t库存数量\t单价(元)"); 132 SqlConnection conn = new SqlConnection(CONNECTIONSTRING); 133 try 134 { 135 conn.Open(); 136 SqlCommand cmd = conn.CreateCommand(); 137 cmd.CommandText = "select * from kc"; 138 SqlDataReader dr = cmd.ExecuteReader(); 139 while (dr.Read()) 140 { 141 Console.WriteLine(dr["WzName"].ToString() + "\t\t" + dr["Num"].ToString() + dr["Unit"].ToString() + "\t\t" + dr["Price"].ToString()); 142 } 143 } 144 finally 145 { 146 conn.Close(); 147 } 148 Console.WriteLine("**********************************************************"); 149 } 150 static void Add() 151 { 152 Console.WriteLine("物资库存维护"); 153 Console.Write("请输入物资名称："); 154 string name = Console.ReadLine(); 155 Console.Write("请输入计量单位："); 156 string unit = Console.ReadLine(); 157 Console.Write("请输入库存数量："); 158 int num = Convert.ToInt32 (Console.ReadLine()); 159 Console.Write("请输入物资单价："); 160 decimal price = Convert.ToDecimal(Console.ReadLine()); 161 162 //向数据库中插入 163 SqlConnection conn = new SqlConnection(CONNECTIONSTRING); 164 try 165 { 166 conn.Open(); 167 SqlCommand cmd = conn.CreateCommand(); 168 cmd.CommandText = "insert into KC values('" + name + "','" + num + "','" + unit + "','" + price + "')"; 169 cmd.ExecuteNonQuery(); 170 Console.WriteLine("添加成功"); 171 } 172 catch 173 { 174 Console.WriteLine("添加出错"); 175 } 176 finally 177 { 178 conn.Close(); 179 } 180 181 } 182 static void Main(string[] args) 183 { 184 while (true) 185 { 186 Console.Clear(); 187 Console.WriteLine("赈灾物资发放统计系统"); 188 Console.WriteLine("1.物资库存维护"); 189 Console.WriteLine("2.物资库存列表"); 190 Console.WriteLine("3.物资发放"); 191 Console.WriteLine("4.物资发放统计"); 192 Console.WriteLine("5.退出"); 193 string menu = Console.ReadLine(); 194 if (menu == "5") 195 { 196 break; 197 } 198 else if (menu == "1") 199 { 200 Add(); 201 } 202 else if (menu == "2") 203 { 204 ShowKC(); 205 Console.ReadKey(); 206 } 207 else if (menu == "3") 208 { 209 FaFang(); 210 } 211 else if (menu == "4") 212 { 213 FaFangTongJi(); 214 Console.ReadKey(); 215 } 216 217 } 218 } 219 } 220 }
