20150506 数据访问（3）

输入向数据库中添加或删除数据、、检查主外键

一、向数据库中添加数据

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication8
{
class Class1
{
public const string CONNECTIONSTRING = "server=MA-PC;database=mydb;uid=sa;pwd=111111";
public static bool Check(string columnName, string value)
{
if (columnName =="sex")
{
if (value =="0"||value =="1")
{
return true;
}
else
{
Console.WriteLine("输入的性别错误");
return false;
}
}
else if (columnName =="birthday")
{
try
{
Convert.ToDateTime(value);
return true;
}
catch
{
Console.WriteLine("生日格式不正确！");
return false;
}
}
else
{
return false;
}
} //判断格式
public static bool CheckPK(string pk) //检查主键
{
bool notHasPK = true;
SqlConnection conn = new SqlConnection(CONNECTIONSTRING);
conn.Open();

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select*from info where code='"+pk+"'";
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows )
{
notHasPK = false;
Console.WriteLine("主键存在");
}
else
{
notHasPK = true;
}

conn.Close();
return notHasPK;
}
public static bool HasNation(string nationCode) //检查民族代号是否输入正确
{
bool hasNation = true;
SqlConnection conn = new SqlConnection(CONNECTIONSTRING);
conn.Open();

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from nation where code='" + nationCode + "'";
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
hasNation = true;
}
else
{
hasNation = false;
Console.WriteLine("民族代号输入不正确");
}
conn.Close();
return hasNation;
}
public static void AddInfo(string code,string name, string sex, string nation,string birthday) //添加数据
{
SqlConnection conn = new SqlConnection(CONNECTIONSTRING);
conn.Open();

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "insert into info value('"+code +"','"+name +"','"+sex+"','"+nation +"','"+birthday+"')";
cmd.ExecuteNonQuery();

conn.Close();
}
public static void Main01(string[] args)
{
//输入
Console.Write("代号：");
string code= Console.ReadLine();
Console.Write("姓名：");
string name = Console.ReadLine();
Console.Write("性别（0，1）：");
string sex = Console.ReadLine();
Console.Write("民族：");
string nation = Console.ReadLine();
Console.Write("生日：");
string birthday = Console.ReadLine();
Console.ReadLine();

//检查
bool isok = CheckInput(code,sex,nation,birthday);
//插入
if (isok==true )
{
AddInfo(code,name,sex ,nation ,birthday);
}
Console.ReadLine();
}
private static bool CheckInput(string code,string sex,string nation,string birthday)
{
//1.对输入的数据格式进行检查
bool isOK = true;
bool isOK1 = Check("sex", sex);
bool isOK2 = Check("birthday", birthday);

//2.对主外键进行检查
bool isOK3 = CheckPK(code);
bool isOK4 = HasNation(nation);

isOK = isOK && isOK1 && isOK2 && isOK3 && isOK4;
return isOK;
} ///检查
}
}

二、从数据库中删除数据  并显示

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication1
{
class Class2
{
public const string CONNECTIONSTRING = "server=.;database=mydb;uid=sa;pwd=123";
static string GetNationName(string code)
{
string str = "";

//根据民族代号查询民族名称
SqlConnection conn = new SqlConnection(CONNECTIONSTRING);
conn.Open();

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from nation where code='" + code + "' ";
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
str = dr["Name"].ToString();
}
else
{
str = "未知";
}

conn.Close();

return str;
}
//显示所有人员信息
public static void Show()
{
//显示
SqlConnection conn = new SqlConnection(CONNECTIONSTRING);
conn.Open();

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from info";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string code = dr["code"].ToString();
string name = dr["name"].ToString();
string sex = ((bool)dr["Sex"]) ? "男" : "女";
string nation = GetNationName(dr["nation"].ToString());
string birthday = ((DateTime)dr["birthday"]).ToString("yyyy年MM月dd日");

Console.WriteLine(code + " " + name + " " + sex + " " + nation + " " + birthday);
}

conn.Close();
}
//检查主键是否存在
public static bool CheckPK(string pk)
{
bool hasPK = true;
SqlConnection conn = new SqlConnection(CONNECTIONSTRING);
conn.Open();

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from info where code='" + pk + "'";
SqlDataReader dr = cmd.ExecuteReader();
hasPK = dr.HasRows;
conn.Close();

return hasPK;
}
public static void DeleteInfo(string pk)
{
SqlConnection conn = new SqlConnection(CONNECTIONSTRING);
conn.Open();

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "delete from family where InfoCode='" + pk + "'";
cmd.ExecuteNonQuery();
cmd.CommandText = "delete from work where InfoCode='" + pk + "'";
cmd.ExecuteNonQuery();
cmd.CommandText = "delete from info where code='" + pk + "'";
cmd.ExecuteNonQuery();

conn.Close();
}
public static void Main00(string[] args)
{
//显示
Show();
//输入要删除的人员代号
Console.Write("输入要删除的人员：");
string code = Console.ReadLine();
//删除
//看看有没有这个代号的人员，如果没有就提示代号输入错误。
bool pkIsOK = CheckPK(code);
if (pkIsOK == true)
{
//执行删除
DeleteInfo(code);
Console.WriteLine("删除成功");
}
else
{
Console.WriteLine("找不到要删除的人员代号，删除失败。请重新检查要删除的人员代号。");
}

//显示
Show();
}
}
}