首页 > 代码库 > 2016年11月28日--ADO.Net 查、插、删、改 小练习
2016年11月28日--ADO.Net 查、插、删、改 小练习
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data.SqlClient;namespace ConsoleApplication1{ class Program { /// <summary> /// 执行TSQL语句 /// </summary> /// <param name="str"></param> /// <returns></returns> public int zhixing(string str) { conn.Open(); try { SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = str; int i = cmd.ExecuteNonQuery(); conn.Close(); return i; } catch (Exception ex) { Console.WriteLine(ex.Message); conn.Close(); return 0; } } static SqlConnection conn = new SqlConnection("server=.;database=Data0928;user=sa;pwd=123"); /// <summary> /// 判断数据输入是否有误 /// </summary> /// <param name="id">1：密码 2：昵称 3：性别 4：生日 5：民族 6：班级</param> /// <returns></returns> public string panduan(int id) { switch (id) { case 1: //密码 for (; ; ) { Console.ForegroundColor = ConsoleColor.Yellow; Console.Write("请输入密码："); string napw = Console.ReadLine(); if (napw != "") return napw; else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("该字段不能为空"); } } case 2: //昵称 for (; ; ) { Console.ForegroundColor = ConsoleColor.Yellow; Console.Write("请输入昵称："); string napw = Console.ReadLine(); if (napw != "") return napw; else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("该字段不能为空"); } } case 3: //性别 for (; ; ) { Console.ForegroundColor = ConsoleColor.Yellow; Console.Write("请输入性别："); string sex = Console.ReadLine(); if (sex != "") { if (sex == "True" || sex == "true" || sex == "男" || sex == "1" || sex == "雄") return "True"; else if (sex == "False" || sex == "false" || sex == "女" || sex == "0" || sex == "雌") return "False"; else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("性别输入有误"); } } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("该字段不能为空"); } } case 4: //生日 for (; ; ) { Console.ForegroundColor = ConsoleColor.Yellow; Console.Write("请输入生日："); try //尝试去做，如果有错，从错误的句直接跳转到catch { DateTime zhi = DateTime.Parse(Console.ReadLine()); return zhi.ToShortDateString(); } catch //只有错误的情况下 { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("输入日期格式错误"); } } case 5: //民族 for (; ; ) { selectNation("Nation"); Console.ForegroundColor = ConsoleColor.Yellow; Console.Write("请输入民族：N"); string nation = ""; int i = Convert.ToInt32(Console.ReadLine()); if (i < 0) i = Math.Abs(i); if (i < 999) { nation = "N" + i.ToString("000"); if (boselect("Nation", "NationCode", nation) || boselect("Nation", "NationName", nation)) { return nation; } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("没有此民族编号或名称"); } } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("请输入数字1~999之间"); } } case 6: //班级 for (; ; ) { selectNation("Class"); Console.ForegroundColor = ConsoleColor.Yellow; Console.Write("请输入班级：C"); string nation = ""; int i = Convert.ToInt32(Console.ReadLine()); if (i < 0) i = Math.Abs(i); if (i < 999) { nation = "C" + i.ToString("000"); if (boselect("Class", "ClassCode", nation) || boselect("Class", "ClassName", nation)) { return nation; } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("没有此班级编号或名称"); } } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("请输入数字1~999之间"); } } default: break; } return ""; } /// <summary> /// 判断要执行的操作 /// </summary> /// <param name="id">操作编号</param> public void caozuo(int id) { switch (id) { case 1: //查询select while (true) { Console.ForegroundColor = ConsoleColor.Yellow; Console.Write("请输入要查看的表名："); string tabname = Console.ReadLine(); if (tabname != "") { Console.Write("请输入条件列名："); string cluame = Console.ReadLine(); if (tabname != "") { Console.Write("请输入条件："); string UserName = Console.ReadLine(); if (tabname != "") { if (select(tabname, cluame, UserName)) { break; } } } } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("该字段不能为空"); } } break; case 2: //插入 while (true) { Console.ForegroundColor = ConsoleColor.Yellow; Console.Write("请输入要插入的用户名："); string UserName = Console.ReadLine(); if (!boselect("Users", "UserName", UserName)) { string pwd = panduan(1); string nick = panduan(2); string sex = panduan(3); string bir = panduan(4); string nation = panduan(5); string cla = panduan(6); if (zhixing("insert into Users values(‘" + UserName + "‘,‘" + pwd + "‘,‘" + nick + "‘,‘" + sex + "‘,‘" + bir + "‘,‘" + nation + "‘,‘" + cla + "‘)") > 0) { Console.WriteLine("成了"); break; } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("没成"); } } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("此用户名以存在请更改。"); } } break; case 3: //删除 while (true) { Console.ForegroundColor = ConsoleColor.Yellow; Console.Write("请输入要删除的用户名："); string UserName = Console.ReadLine(); if (boselect("Users", "UserName", UserName)) { if (zhixing("DELETE FROM Users WHERE UserName=‘" + UserName + "‘") > 0) { Console.WriteLine("成了"); break; } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("没成"); } } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("此用户名不存在请更改。"); } } break; case 4: //修改 while (true) { Console.ForegroundColor = ConsoleColor.Yellow; Console.Write("请输入要修改的用户名："); string UserName = Console.ReadLine(); if (boselect("Users", "UserName", UserName)) { string pwd = panduan(1); string nick = panduan(2); string sex = panduan(3); string bir = panduan(4); string nation = panduan(5); string cla = panduan(6); if (zhixing("update Users set PassWord = ‘" + pwd + "‘,NickName = ‘" + nick + "‘,Sex=‘" + sex + "‘,Birthday=‘" + bir + "‘,Nation=‘" + nation + "‘,Class=‘" + cla + "‘ where UserName = ‘" + UserName + "‘") > 0) { Console.WriteLine("成了"); break; } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("没成"); } } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("此用户名不存在请更改。"); } } break; default: break; } } /// <summary> /// 查询语句 /// </summary> /// <param name="tabname">要查询的表名</param> /// <param name="cluame">要查询的条件列</param> /// <param name="UserName">条件</param> /// <returns></returns> public bool select(string tabname, string cluame, string UserName) { try { SqlCommand cmd = conn.CreateCommand(); Console.ForegroundColor = ConsoleColor.Blue; if (tabname == "Users" || tabname == "users") { conn.Open(); cmd.CommandText = "SELECT ids,UserName,[PassWord],NickName,Sex,Birthday,NationName as Nation,ClassName AS Class FROM Users,Class,Nation WHERE Nation=NationCode AND Class=ClassCode AND " + cluame + "=‘" + UserName + "‘"; SqlDataReader dr = cmd.ExecuteReader(); Console.WriteLine("┌──────────────────────────────────┐"); Console.WriteLine("│ ID\t用户名\t密码\t昵称\t性别\t生日\t\t民族\t班级 │"); if (dr.HasRows) { while (dr.Read()) { Console.WriteLine("│ {0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\t{7} │", dr["ids"], dr["username"], dr["PassWord"], dr["NickName"], dr["Sex"], Convert.ToDateTime(dr["Birthday"]).ToShortDateString(), dr["Nation"], dr["class"]); } Console.WriteLine("└──────────────────────────────────┘"); conn.Close(); return true; } else { Console.WriteLine("│──────────────────────────────────│"); Console.WriteLine("│─────────────未查到任何数据──────────────│"); Console.WriteLine("│──────────────────────────────────│"); Console.WriteLine("└──────────────────────────────────┘"); conn.Close(); return false; } } else { cmd.CommandText = "SELECT * FROM " + tabname + " where " + cluame + "=‘" + UserName + "‘"; SqlDataReader dr = cmd.ExecuteReader(); Console.WriteLine("┌───────────┐"); Console.WriteLine("│\t编号\t名称\t│"); conn.Open(); if (dr.HasRows) { while (dr.Read()) { Console.WriteLine("│\t{0}\t{1}\t│", dr[0], dr[1]); } Console.WriteLine("└───────────┘"); conn.Close(); return true; } else { Console.WriteLine("│───────────│"); Console.WriteLine("│──未查到任何数据──│"); Console.WriteLine("│───────────│"); Console.WriteLine("└───────────┘"); conn.Close(); return false; } } } catch { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("表名、列名、数据输入有误"); return false; } } /// <summary> /// 判断UserName是否存在 /// </summary> /// <param name="tabname">表名</param> /// <param name="UserName">用户名</param> /// <returns></returns> public bool boselect(string tabname, string cluame, string UserName) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT * FROM " + tabname + " where "+cluame+"=‘" + UserName + "‘"; SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { conn.Close(); return true; } else { conn.Close(); return false; } } /// <summary> /// 打印所有用户数据 /// </summary> public void selectall() { SqlCommand cmd = conn.CreateCommand(); Console.ForegroundColor = ConsoleColor.Blue; Console.WriteLine("┌──────────────────────────────────┐"); Console.WriteLine("│ ID\t用户名\t密码\t昵称\t性别\t生日\t\t民族\t班级 │"); conn.Open(); cmd.CommandText = "SELECT ids,UserName,[PassWord],NickName,Sex,Birthday,NationName as Nation,ClassName AS Class FROM Users,Class,Nation WHERE Nation=NationCode AND Class=ClassCode"; SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { Console.WriteLine("│ {0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\t{7} │", dr["ids"], dr["username"], dr["PassWord"], dr["NickName"], dr["Sex"], Convert.ToDateTime(dr["Birthday"]).ToShortDateString(), dr["Nation"], dr["class"]); } } else { Console.WriteLine("│──────────────────────────────────│"); Console.WriteLine("│─────────────未查到任何数据──────────────│"); Console.WriteLine("│──────────────────────────────────│"); } conn.Close(); Console.WriteLine("└──────────────────────────────────┘"); } /// <summary> /// 打印所有民族与代码 /// </summary> public void selectNation(string tabname) { SqlCommand cmd = conn.CreateCommand(); Console.ForegroundColor = ConsoleColor.Blue; Console.WriteLine("┌───────────┐"); Console.WriteLine("│\t编号\t名称\t│"); conn.Open(); cmd.CommandText = "SELECT * FROM " + tabname; SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { Console.WriteLine("│\t{0}\t{1}\t│", dr[0], dr[1]); } } else { Console.WriteLine("│───────────│"); Console.WriteLine("│──未查到任何数据──│"); Console.WriteLine("│───────────│"); } conn.Close(); Console.WriteLine("└───────────┘"); } static void Main(string[] args) { Program pr = new Program(); SqlCommand cmd = conn.CreateCommand(); //打印所有人的数据 pr.selectall(); while (true) { Console.ForegroundColor = ConsoleColor.Yellow; Console.WriteLine("1.查询"); Console.WriteLine("2.插入"); Console.WriteLine("3.删除"); Console.WriteLine("4.修改"); Console.Write("请选择要执行的操作："); try { int biao = Convert.ToInt32(Console.ReadLine()); if (biao > 0 && biao < 5) { pr.caozuo(biao); Console.ForegroundColor = ConsoleColor.Yellow; Console.Write("请输入任意键继续_"); Console.ReadKey(); Console.Clear(); pr.selectall(); } else { Console.Clear(); pr.selectall(); Console.ForegroundColor = ConsoleColor.Yellow; Console.WriteLine("请输入1~4进行选择"); } } catch { Console.Clear(); pr.selectall(); Console.WriteLine("请输入数字1~4进行选择"); } } } }}
干净到爆，可以点啊，点点试试
2016年11月28日--ADO.Net 查、插、删、改 小练习
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。