StringBuilder类： 用来定义可变字符串
StringBuilder sb = new StringBuilder("");
//追加字符串
sb.Append("World");
sb.Append("!");
//W2orld
sb.Insert(2, "2");
//原字符串：Wo2rld! 截取之后：W2rld!
sb.Remove(1, 2);
//ToString()
Console.WriteLine(sb.ToString());
查询学生记录数
//打开数据库连接
con.Open();
//使用StringBuilder追加SQL语句
StringBuilder sb = new StringBuilder();
sb.Append("select ");
sb.Append(" Count(*) ");
sb.Append(" from ");
sb.Append("[Student]");
Console.WriteLine(sb.ToString());
//创建一个SqlCommand对象
SqlCommand com = new SqlCommand(sb.ToString(),con);
Console.WriteLine((int)com.ExecuteScalar());
DataReader：从数据源中检索只读、只进的数据流，每次读取一行数据
StringBuilder sb = new StringBuilder();
sb.AppendLine("select");
sb.AppendLine("[StudentNo]");
sb.AppendLine(",[StudentName]");
sb.AppendLine("from");
sb.AppendLine("[Student]");
SqlCommand com = new SqlCommand(sb.ToString(), con);
//从数据源中检索只读、只进的数据流
return com.ExecuteReader();
SqlDataReader reader=GetStudentInfo();
while (reader.Read())
{
Console.WriteLine("{0} {1}",reader["StudentNo"],reader["StudentName"]);
}
reader.Close();
ExecuteNonQuery():
StringBuilder sb = new StringBuilder();
sb.AppendLine("Insert into");
sb.AppendLine("[Grade]([GradeName])");
sb.AppendLine("Values('" + gradeName + "')");
//3.创建一个SqlCommand
SqlCommand com = new SqlCommand(sb.ToString(),con);
//4.返回执行结果
return com.ExecuteNonQuery();
Student stu = new Student();
Console.WriteLine("请输入年级名称：");
string gradename = Console.ReadLine();
int count = stu.AddGrade(gradename);
if (count > 0)
{
Console.WriteLine("success！");
}
else
{
Console.WriteLine("success mother!");
}
class Test
{
string connString = "Data Source = .;Initial Catalog= Library;User Id = Sa;Pwd = asiga0.";
//判断用户
public bool CheckUser(string UserId, string Password) {
String s = "select count(*) from user1 where loginid='" + UserId + "'and loginpwd = '" + Password + "'";//空格错误!
SqlConnection conn = new SqlConnection(connString);
SqlCommand comm = new SqlCommand(s, conn);
try
{
conn.Open();
if ((int)comm.ExecuteScalar()>0
)
{
Console.WriteLine("登陆成功");
return true;
}
else
{
Console.WriteLine("登录失败");
return false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}
finally
{
conn.Close();
}
}
//主菜单
public void List() {
do
{
Console.WriteLine("====请选择操作键====");
Console.WriteLine("1.查看全部图书");
Console.WriteLine("2.插入图书信息");
Console.WriteLine("3.修改图书信息");
Console.WriteLine("4.删除图书信息");
Console.WriteLine("5.退出");
Console.WriteLine("===============");
int i = int.Parse(Console.ReadLine());
switch (i)
{
case 1:
One();
break;
case 2:
Two();
break;
case 3:
Three();
break;
case 4:
Four();
break;
case 5:
Console.WriteLine("谢谢使用,再见!");
return;
default:
break;
}
Console.WriteLine("继续吗?(Y/N)");
if (!Console.ReadLine().ToLower().Trim().Equals("y"))
{
break;
}
} while (true);
}
public void One() {
SqlConnection conn = new SqlConnection(connString);
try
{
String s = "select book.id,book.name,booktype.typename,book.number,book.price from book,booktype where book.typle=booktype.typle";
Console.WriteLine("------------------------------------------------");
Console.WriteLine("编号 名称 类别 数量 价格");
Console.WriteLine("------------------------------------------------");
SqlCommand comm = new SqlCommand(s, conn);
conn.Open();
SqlDataReader rd = comm.ExecuteReader();
while (rd.Read())
{
Console.WriteLine("{0} {1} {2} {3} {4}", rd["id"], rd["name"], rd["typename"], rd["number"], rd["price"]);//前面不能加" . "
}
rd.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conn.Close();
}
}
public void Two() {
SqlConnection conn = new SqlConnection(connString);
try
{
Console.WriteLine("请输入图书编号");
int Id = int.Parse(Console.ReadLine());
if (GetBookByID(Id))
{
Console.WriteLine("图书记录已存在,请重新输入!");
return;
}
Console.WriteLine("请输入图书名称");
string name = Console.ReadLine();
Console.WriteLine("请插入图书类型编号");
int type = int.Parse(Console.ReadLine());
if (GetBookTypeByID(type)) { }
else { Console.WriteLine("不存在,请重新输入"); return; }
Console.WriteLine("请插入图书数量");
int number = int.Parse(Console.ReadLine());
Console.WriteLine("请输入图书单价");
float price = float.Parse(Console.ReadLine());
String s = string.Format("insert book(id,name,typle,number,price) values({0},'{1}',{2},{3},{4})", Id, name, type, number, price);
SqlCommand comm = new SqlCommand(s, conn);
conn.Open();//忘记开启
if (comm.ExecuteNonQuery() > 0)
{
Console.WriteLine("插入成功!");
}
else
{
Console.WriteLine("插入失败");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conn.Close();
}
}
public bool GetBookByID(int bookId) {
SqlConnection conn = new SqlConnection(connString);
string s = "select id from book where id=" + bookId;
SqlCommand comm = new SqlCommand(s, conn);
conn.Open();
SqlDataReader rd = comm.ExecuteReader();
try
{
if (rd.Read())
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return true;
}
finally
{
rd.Close();
conn.Close();
}
}
public bool GetBookTypeByID(int typeId) {
SqlConnection conn = new SqlConnection(connString);
string s = "select typle,typename from booktype where typle=" + typeId;
SqlCommand comm = new SqlCommand(s, conn);
conn.Open();
SqlDataReader rd = comm.ExecuteReader();
try
{
if (rd.Read())
{
Console.WriteLine("{0} {1}", rd["typle"], rd["typename"]);
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}
finally
{
rd.Close();
conn.Close();
}
}
public void Three() {
Console.WriteLine("请输入图书编号");
int id = int.Parse(Console.ReadLine());
if (GetBookByID(id))
{
Console.WriteLine("请输入修改后的价格($.00):");
float f = float.Parse(Console.ReadLine());
if (ChangePrice(id, f) > 0)
{
Console.WriteLine("修改成功");
}
else
{
Console.WriteLine("修改失败");
}
}
else
{
Console.WriteLine("不存在该编号,请重新输入");
return;
}
}
public int ChangePrice(int id,float price) {
SqlConnection conn = new SqlConnection(connString);
try
{
String s = string.Format("update book set price ={0} where id={1}", price, id);
SqlCommand comm = new SqlCommand(s, conn);
conn.Open();
return comm.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return -1;
}
finally
{
conn.Close();
}
}
public void Four() {
Console.WriteLine("请输入图书编号");
int id = int.Parse(Console.ReadLine());
if (GetBookByID(id))
{
show(id);
Console.WriteLine("将删除该图书记录,确认吗?(Y/N)");
if (Console.ReadLine().ToLower().Trim().Equals("y")) {
if (DeleteBookByID(id) > 0)
{
Console.WriteLine("修改成功");
}
else
{
Console.WriteLine("修改失败");
}
}
else
{
Console.WriteLine("未删除,返回");
}
}
else
{
Console.WriteLine("编号不存在,重新输入");
return;
}
}
public void show(int id) {
SqlConnection conn = new SqlConnection(connString);
try
{
String s = "select book.id,book.name,booktype.typename,book.number,book.price from book,booktype where book.typle=booktype.typle and book.id="+id;
Console.WriteLine("------------------------------------------------");
Console.WriteLine("编号 名称 类别 数量 价格");
Console.WriteLine("------------------------------------------------");
SqlCommand comm = new SqlCommand(s, conn);
conn.Open();
SqlDataReader rd = comm.ExecuteReader();
while (rd.Read())
{
Console.WriteLine("{0} {1} {2} {3} {4}", rd["id"], rd["name"], rd["typename"], rd["number"], rd["price"]);//前面不能加" . "
}
rd.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conn.Close();
}
}
public int DeleteBookByID(int id) {
SqlConnection conn = new SqlConnection(connString);
String s = "Delete from book where id=" + id;
SqlCommand comm = new SqlCommand(s, conn);
try
{
conn.Open();
return comm.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return -1;
}
finally
{
conn.Close();
}
}
}