数据库第十七章
来源：互联网 发布：违规删除恢复权重淘宝 编辑：程序博客网 时间：2024/06/08 19:13
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data.SqlClient;namespace Program{ class UserManager { private const string strConn = @"Data Source=.;Initial Catalog=QQ用户信息管理系统;Integrated Security=True"; private DBHandle _dbHandle = new DBHandle(); const String ERRMSG = "数据操作失败！"; const String EXCEPT = "出现异常"; public void Login() { int count = 0; do { string strUserName = string.Empty; string strPwd = string.Empty; count++; Console.WriteLine("请输入用户名："); strUserName = Console.ReadLine(); Console.WriteLine("请输入密码："); strPwd = Console.ReadLine(); if (strUserName.Equals(string.Empty) || strPwd.Equals(string.Empty)) { Console.WriteLine("输入错误，请重新输入！\n"); continue; } else { string strMsg = string.Empty; bool bRet = _dbHandle.CheckAdminInfo(strUserName, strPwd, ref strMsg); if (bRet) { Console.WriteLine("登录成功！"); ShowMenu(); break; } else { Console.WriteLine("登录失败：" + strMsg + "\n"); continue; } } } while (count < 3); if (count == 3) Console.WriteLine("\n连续三次登录失败，退出本系统！\n"); } private void ShowMenu() { string option = ""; do { Console.WriteLine(""); Console.WriteLine("=======欢迎登录QQ用户信息管理系统======"); Console.WriteLine("----------------请选择菜单项----------"); Console.WriteLine("1、显示用户清单"); Console.WriteLine("2、更新在线天数"); Console.WriteLine("3、添加用户新记录"); Console.WriteLine("4、更新用户等级"); Console.WriteLine("5、删除用户记录"); Console.WriteLine("0、退出"); Console.WriteLine("======================================="); option = Console.ReadLine(); switch (option) { case "1"://显示用户信息 ShowUserInfo(); continue; case "2"://更新在线天数 UpdateOnLineDay(); continue; case "3"://添加用户 InsertUserInfo(); continue; case "4"://更新用户等级 UpdateUserLevel(); continue; case "5"://删除用户 DeleteUserInfo(); continue; case "0": break;//退出 default: continue; } break; } while (true); } private void ShowUserInfo() { SqlDataReader reader = _dbHandle.GetUserList(); if (reader == null) { Console.WriteLine(EXCEPT); return; } DisplayUserInfo(reader); } private void DisplayUserInfo(SqlDataReader reader) { Console.WriteLine("--------------------------------------------------------------------------------"); Console.WriteLine("编号\t昵称\t\t等级\t\t邮箱\t\t在线天数"); Console.WriteLine("--------------------------------------------------------------------------------"); while (reader.Read()) { Console.Write(reader["UserId"] + "\t"); Console.Write(reader["UserName"] + "\t"); Console.Write(ShowDesign((String)reader["LevelName"]) + "\t\t"); Console.Write(reader["Email"] + "\t"); Console.WriteLine(reader["OnLineDay"]); } Console.WriteLine("--------------------------------------------------------------------------------"); } private string ShowDesign(string strLevel) { string strDesign = string.Empty; switch (strLevel) { case "无": strDesign = "―"; break; case "星星": strDesign = "☆"; break; case "月亮": strDesign = "€"; break; case "太阳": strDesign = "◎"; break; default: break; } return strDesign; } private void UpdateOnLineDay() { try { Console.WriteLine("请输入用户编号"); string strUserId = Console.ReadLine(); int iUserId = Convert.ToInt32(strUserId); Console.WriteLine("请输入新的在线天数"); string strNewOnlineDay = Console.ReadLine(); double iNewOnlineDay = Convert.ToDouble(strNewOnlineDay); int iret = _dbHandle.UpdateOnlineDay(iUserId, iNewOnlineDay); if (iret == -1) Console.WriteLine(ERRMSG); else if (iret == 0) { Console.WriteLine("用户记录不存在"); } else { Console.WriteLine("修改成功"); } } catch (Exception) { Console.WriteLine(EXCEPT); } } private void InsertUserInfo() { Console.WriteLine("请输入用户昵称:"); string strUserName = Console.ReadLine(); Console.WriteLine("请输入用户密码:"); string strUserPwd = Console.ReadLine(); Console.WriteLine("请输入用户邮箱地址："); string strUserEmail = Console.ReadLine(); int iRet = Convert.ToInt32(_dbHandle.InsertUserInfo(strUserName, strUserPwd, strUserEmail)); if (iRet == -1) { Console.WriteLine(EXCEPT); } else if (iRet == 0) { Console.WriteLine("用户记录不存在"); } else { Console.WriteLine("插入成功！用户编号是：" + iRet); } } private int JudgeLevelByOnLineDay(double iOnlineDay) { const int LEVEL1 = 5; const int LEVEL2 = 32; const int LEVEL3 = 320; int iNewLevel = 0; if (iOnlineDay >= LEVEL1 && iOnlineDay < LEVEL2) { iNewLevel = 2; } else if (iOnlineDay >= LEVEL2 && iOnlineDay < LEVEL3) { iNewLevel = 3; } else if (iOnlineDay >= LEVEL3) { iNewLevel = 4; } else { iNewLevel = 1; } return iNewLevel; } private void UpdateUserLevel() { SqlDataReader reader = _dbHandle.GetUserIdAndOnlineDay(); if (reader == null) { Console.WriteLine(EXCEPT); return; } int iUserId = 0; //用户编号 double iLineDay = 0; //用户在线天数 int iLevelId = 0; //用户等级 int count = 0; //更新记录数 while (reader.Read()) { iUserId = Convert.ToInt32(reader["UserId"]);//用户编号的类型转换 iLineDay = Convert.ToDouble(reader["OnLineDay"]);//用户在线天数的类型转换 iLevelId = JudgeLevelByOnLineDay(iLineDay);//根据在线天数判定用户等级 _dbHandle.UpdateUserLevel(iUserId, iLevelId); count++; } Console.WriteLine("本次共更新用户记录数：{0}", count); Console.WriteLine("更新成功！"); } public void DeleteUserInfo() { try { Console.WriteLine("请输入删除的用户编号："); string strUserId = Console.ReadLine(); int iUserId = Convert.ToInt32(strUserId); Console.WriteLine("将要删除的用户信息是："); SqlDataReader reader = _dbHandle.GetUserByID(iUserId); if (reader == null) { Console.WriteLine(EXCEPT); return; } DisplayUserInfo(reader); Console.WriteLine("要删除该用户记录吗？（Y/N）"); if (Console.ReadLine().Trim().ToUpper() != "Y") { Console.WriteLine("退出删除操作！"); return; } int iRet = _dbHandle.DeleteUserInfo(iUserId); if (iRet == -1) { Console.WriteLine(ERRMSG); } else { Console.WriteLine("删除成功！"); } } catch (Exception ex) { Console.WriteLine("删除失败：" + ex.Message); } } private bool IsExit() { Console.WriteLine("是否退出？（Y/N）"); string strRet = Console.ReadLine(); strRet = strRet.Trim().ToUpper(); if (strRet.Equals("Y")) { return true; } else { return false; } } }}using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data.SqlClient;namespace Program{ class DBHandle { private const string strConn = @"Data Source=.;Initial Catalog=QQ用户信息管理系统;Integrated Security=True"; public bool CheckAdminInfo(string strUserName, string strPwd, ref string strMsg) { SqlConnection conn = new SqlConnection(strConn); try { string strSql = "select count(*) from Admin where LoginId='" + strUserName + "' and LoginPwd='" + strPwd + "'"; conn.Open(); SqlCommand comm = new SqlCommand(strSql, conn); int iRet = (int)comm.ExecuteScalar(); if (iRet != 1) { strMsg = "输入无效！"; return false; } else { return true; } } catch (Exception e) { Console.WriteLine(e.Message); strMsg = "发生异常！"; return false; } finally { conn.Close(); } } public SqlDataReader GetUserList() { try { SqlConnection conn = new SqlConnection(strConn); conn.Open(); StringBuilder sb = new StringBuilder(); sb.AppendLine(" SELECT"); sb.AppendLine(" a.[UserId]"); sb.AppendLine(" ,a.[UserName]"); sb.AppendLine(" ,b.[LevelName]"); sb.AppendLine(" ,a.[Email]"); sb.AppendLine(" ,a.[OnLineDay]"); sb.AppendLine(" FROM"); sb.AppendLine(" [UserInfo] a, [Level] b "); sb.AppendLine(" WHERE"); sb.AppendLine(" a.[LevelId] = b.[LevelId]"); SqlCommand comm = new SqlCommand(sb.ToString(), conn); return comm.ExecuteReader(); } catch (Exception ex) { Console.WriteLine(ex.Message); return null; } } public SqlDataReader GetUserIdAndOnlineDay() { try { SqlConnection conn = new SqlConnection(strConn); conn.Open(); StringBuilder sb = new StringBuilder(); sb.AppendLine(" SELECT"); sb.AppendLine(" [UserId]"); sb.AppendLine(" ,[OnLineDay]"); sb.AppendLine(" FROM"); sb.AppendLine(" [UserInfo]"); SqlCommand comm = new SqlCommand(sb.ToString(), conn); return comm.ExecuteReader(); } catch (Exception ex) { Console.WriteLine(ex.Message); return null; } } public int UpdateOnlineDay(int userId, double newOnlineDay) { try { SqlConnection conn = new SqlConnection(strConn); conn.Open(); StringBuilder sb = new StringBuilder(); sb.AppendLine("UPDATE"); sb.AppendLine(" [UserInfo]"); sb.AppendLine("SET"); sb.AppendLine(" [OnLineDay]=" + newOnlineDay); sb.AppendLine("WHERE"); sb.AppendLine(" [UserId]=" + userId); SqlCommand comm = new SqlCommand(sb.ToString(), conn); return comm.ExecuteNonQuery(); } catch (Exception) { return -1; } } public int UpdateUserLevel(int userId, int iLevel) { try { SqlConnection conn = new SqlConnection(strConn); conn.Open(); StringBuilder sb = new StringBuilder(); sb.AppendLine(" UPDATE"); sb.AppendLine(" [UserInfo]"); sb.AppendLine(" SET"); sb.AppendLine(" [LevelId]=" + iLevel); sb.AppendLine(" WHERE"); sb.AppendLine(" [UserId]=" + userId); SqlCommand comm = new SqlCommand(sb.ToString(), conn); return comm.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); return -1; } } public object InsertUserInfo(string userName, string userPwd, string email) { SqlConnection conn = new SqlConnection(strConn); try { conn.Open(); StringBuilder sb = new StringBuilder(); sb.AppendLine(" INSERT INTO"); sb.AppendLine(" [UserInfo]"); sb.AppendLine(" VALUES"); sb.AppendLine(" ('" + userName + "','" + userPwd + "',1,'" + email + "',0);"); sb.AppendLine(" SELECT @@Identity;"); SqlCommand comm = new SqlCommand(sb.ToString(), conn); return comm.ExecuteScalar(); } catch (Exception) { return -1; } } public SqlDataReader GetUserByID(int UserID) { try { SqlConnection conn = new SqlConnection(strConn); conn.Open(); StringBuilder sb = new StringBuilder(); sb.AppendLine(" SELECT"); sb.AppendLine(" a.[UserId]"); sb.AppendLine(" ,a.[UserName]"); sb.AppendLine(" ,b.[LevelName]"); sb.AppendLine(" ,a.[Email]"); sb.AppendLine(" ,a.[OnLineDay]"); sb.AppendLine("FROM"); sb.AppendLine(" [UserInfo] a, [Level] b"); sb.AppendLine(" WHERE"); sb.AppendLine(" a.[UserId] = " + UserID); sb.AppendLine(" AND"); sb.AppendLine(" a.[LevelId] = b.[LevelId]"); SqlCommand comm = new SqlCommand(sb.ToString(), conn); return comm.ExecuteReader(); } catch (Exception) { return null; } } public int DeleteUserInfo(int strUserId) { try { SqlConnection conn = new SqlConnection(strConn); conn.Open(); StringBuilder sb = new StringBuilder(); sb.AppendLine(" DELETE FROM"); sb.AppendLine(" [UserInfo]"); sb.AppendLine( "WHERE"); sb.AppendLine(" [UserId]=" + strUserId); SqlCommand comm = new SqlCommand(sb.ToString(), conn); return comm.ExecuteNonQuery(); } catch (Exception) { return -1; } } }}using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace Program{ class Program { static void Main(string[] args) { UserManager manager = new UserManager(); manager.Login(); } }} 0 0
- 数据库第十七章
- 第十七章
- 第十七章
- 【细说PHP学习】第十七章 MySQL数据库概述
- 数据库第十七周学习攻略
- 第十七章 树
- 第十七章 现在时态
- 梦里人第十七章
- 第十七章 工具
- 第十七章 适配器模式
- 第十七章 FTP服务器
- 第十七章： 中间件
- 第十七章_Web注解
- 第十七章：jQuery类库
- 第二章第十七题
- 第二章第十七题
- 第二章第十七题
- 第三章第十七题
- 关于轮播
- react webpack 按需加载初试
- Android开发小工具以及小错误总结
- mysql删除重复记录，保存Id最小的一条
- keepalived+nginx+tomcat高可用（主从切换）以及灾难恢复
- 数据库第十七章
- Android多个Activity之间的页面跳转带参传递与返回
- jinkins在windows上的安装 配置C#编译
- poj1860 Currency Exchange bfs剪枝 / bellman-ford 单源最短路径 初学
- 在Ubuntu终端彻底删除软件的方法
- class文件转变为java源文件方法
- 远程访问Ubuntu 上resin 管理页面
- stm32通信接口
- YV12，I420，YUV420P的区别
