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(); ; }
}
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)
{
:
//密码
for (; ; )
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.Write("请输入密码：");
string napw = Console.ReadLine();
if (napw != "")
return napw;
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("该字段不能为空");
}
}
:
//昵称
for (; ; )
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.Write("请输入昵称：");
string napw = Console.ReadLine();
if (napw != "")
return napw;
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("该字段不能为空");
}
}
:
//性别
for (; ; )
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.Write("请输入性别：");
string sex = Console.ReadLine();
if (sex != "")
{
" || sex == "雄")
return "True";
" || sex == "雌")
return "False";
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("性别输入有误");
}
}
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("该字段不能为空");
}
}
:
//生日
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("输入日期格式错误");
}
}
:
//民族
for (; ; )
{
selectNation("Nation");
Console.ForegroundColor = ConsoleColor.Yellow;
Console.Write("请输入民族：N");
string nation = "";
int i = Convert.ToInt32(Console.ReadLine());
)
i = Math.Abs(i);
)
{
nation = ");
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之间");
}
}
:
//班级
for (; ; )
{
selectNation("Class");
Console.ForegroundColor = ConsoleColor.Yellow;
Console.Write("请输入班级：C");
string nation = "";
int i = Convert.ToInt32(Console.ReadLine());
)
i = Math.Abs(i);
)
{
nation = ");
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)
{
:
//查询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;
:
//插入
while (true)
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.Write("请输入要插入的用户名：");
string UserName = Console.ReadLine();
if (!boselect("Users", "UserName", UserName))
{
);
);
);
);
);
);
)
{
Console.WriteLine("成了"); break;
}
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("没成");
}
}
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("此用户名以存在请更改。");
}
}
break;
:
//删除
while (true)
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.Write("请输入要删除的用户名：");
string UserName = Console.ReadLine();
if (boselect("Users", "UserName", UserName))
{
)
{
Console.WriteLine("成了"); break;
}
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("没成");
}
}
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("此用户名不存在请更改。");
}
}
break;
:
//修改
while (true)
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.Write("请输入要修改的用户名：");
string UserName = Console.ReadLine();
if (boselect("Users", "UserName", UserName))
{
);
);
);
);
);
);
)
{
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(], dr[]);
}
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(], dr[]);
}
}
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());
&& biao < )
{
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进行选择");
}
}
}
}
}
代码