ADO.NET刪除和修改

標籤：ati   create   back   list   null   sda   使用者   key   編號   

//類

SqlConnection conn;
SqlCommand cmd;

public UsersData()
{
conn = new SqlConnection("server=.;database=Data0504;user=sa;pwd=123;");
cmd = conn.CreateCommand();
}

public List<Users> SelectAll()
{
List<Users> ulist = new List<Users>();
cmd.CommandText = "select *from Users";
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Users u = new Users();
u.Ids = dr["Ids"].ToString();
u.Ucode = dr["Ucode"].ToString();
u.UserName = dr["UserName"].ToString();
u.PassWord = dr["PassWord"].ToString();
u.NickName = dr["NickName"].ToString();
u.Sex = Convert.ToBoolean(dr["Sex"]);
u.Birthday = Convert.ToDateTime(dr["Birthday"]);
u.Nation = dr["Nation"].ToString();

ulist.Add(u);
}
conn.Close();
return ulist;
}

public bool SelectAll(string uc)
{
bool has = false;

cmd.CommandText = "select *from Users where Ucode = @a";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@a", uc);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
has = true;
}
conn.Close();
return has;
}

public bool UpdateUser(Users u)
{
bool ok = false;

cmd.CommandText = "update Users set PassWord = @a, [email protected],[email protected] ,[email protected] where Ucode = @d";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@a", u.PassWord);
cmd.Parameters.AddWithValue("@b", u.NickName);
cmd.Parameters.AddWithValue("@c", u.Sex);
cmd.Parameters.AddWithValue("@d", u.Ucode);
cmd.Parameters.AddWithValue("@e", u.Birthday );
conn.Open();

int count = cmd.ExecuteNonQuery();
if (count > 0)
{
ok = true;
}

conn.Close();
return ok;
}

public List<Users> insert1() {
List<Users> list = new List<Users> ();
Users a= new Users();
conn.Open();
cmd.CommandText = "insert into Users values(@a,@b,@c,@d,@e,@f,@g)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@a", a.Ucode );
cmd.Parameters.AddWithValue("@b",a.UserName );
cmd.Parameters.AddWithValue("@c",a.PassWord );
cmd.Parameters.AddWithValue("@d",a.NickName );
cmd.Parameters.AddWithValue("@e",a.Sex );
cmd.Parameters.AddWithValue("@f", a.Birthday );
cmd.Parameters.AddWithValue("@g", a.Nation );
list.Add(a);

int count = cmd.ExecuteNonQuery();
conn.Close();

if (count > 0)
{
Console.WriteLine("添加成功");

}
else
{
Console.WriteLine("失敗！");
}

return list;

}

 

  

Console.BackgroundColor = ConsoleColor.White;
Console.ForegroundColor = ConsoleColor.Black;
while (true)
{
Console.Clear();
new Test1().DataLook();

while (true)
{
Console.WriteLine();
Console.WriteLine();
Console.Write("請輸入你要執行的操作（添加：1，修改：2，刪除：3）：");
string u = Console.ReadLine();

if (u == "1")
{
Users uuu = new Users();
uuu.Ucode = new UsersData().NewUcode();
Console.WriteLine("編號為：" + uuu.Ucode);
while (true)
{
Console.Write("請輸入使用者名稱：");
uuu.UserName = Console.ReadLine();
if (uuu.UserName.Trim().Length > 0) { break; }
else { Console.WriteLine("使用者名稱不符合規定！"); }
}

while (true)
{
Console.Write("請輸入密碼：");
uuu.PassWord = Console.ReadLine();
Console.Write("請再次輸入新的密碼：");
string PassWord2 = Console.ReadLine();

if (uuu.PassWord.Trim().Length > 4 && uuu.PassWord == PassWord2)
{

break;
}
else
{ Console.WriteLine("密碼不符合規定！");
}
}

while (true)
{
Console.Write("請輸入暱稱：");
uuu.NickName = Console.ReadLine();
if (uuu.NickName.Trim().Length > 0) { break; }
else { Console.WriteLine("暱稱不符合規定！"); }
}

while (true)
{
Console.Write("請輸入性別（男/女）：");
string ssex = Console.ReadLine();
if (ssex != "男" && ssex != "女")
{
Console.WriteLine("性別輸入有誤！");
}
else
{
uuu.Sex = ssex == "男" ? true : false;
break;
}
}

while (true)
{
Console.Write("請輸入生日（2000-1-1）：");
string bir = Console.ReadLine();
try
{
uuu.Birthday = Convert.ToDateTime(bir);
break;
}
catch
{
Console.WriteLine("日期輸入錯誤！");
}
}

while (true)
{
Console.Write("請輸入你的民族（如：漢族/藏族）：");
string nat = Console.ReadLine();
string snn = new UserNationData().SelectNationCode(nat);
if (snn == "null")
{
Console.WriteLine("查無此民族！");
}
else
{
uuu.Nation = snn;
break;
}
}

 

UsersData udd = new UsersData();
bool ok = udd.InsertUser(uuu);
if (ok)
{
Console.WriteLine("添加成功！");
}
else
{
Console.WriteLine("添加失敗！");
}

Console.ReadKey();
break;
}

//修改

else if (u == "2")
{
Console.Write("請輸入要修改的使用者編號：");
string iii = Console.ReadLine();
bool h = new UsersData().SelectAll(iii);

if (h)
{
Users up = new Users();
up.Ucode = iii;

#region 修改密碼
Console.WriteLine("已查詢到此使用者資訊，可以進行修改");
while (true)
{
Console.Write("請輸入新的密碼：");
string pwd1 = Console.ReadLine();
Console.Write("請再次輸入新的密碼：");
string pwd2 = Console.ReadLine();
if (pwd1 == pwd2)
{
up.PassWord = pwd1;
break;
}
else
{
Console.WriteLine("兩次密碼不一致！請重新輸入！");
}
}
#endregion

#region 修改暱稱
while (true)
{
Console.Write("請輸入新的暱稱：");
string nn = Console.ReadLine();
if (nn.Trim().Length <= 0)
{
Console.WriteLine("輸入的暱稱不符合規定！請重新輸入！");
}
else
{
up.NickName = nn;
break;
}
}
#endregion

while (true)
{
Console.Write("請輸入新的性別（男/女）:");
string ss = Console.ReadLine();
if (ss != "男" && ss != "女")
{
Console.WriteLine("輸入錯誤！");
}
else
{
up.Sex = ss == "男" ? true : false;
break;
}
}
while (true) {
try
{
Console.Write("請輸入新的生日：");
DateTime birthday = new DateTime();
birthday = Convert.ToDateTime(Console.ReadLine());
up.Birthday = birthday;
break;
}
catch {

Console.Write("請輸入正確的日期格式：");
}

}
while (true)
{
Console.Write("請輸入你的民族（如：漢族/藏族）：");
string nat = Console.ReadLine();
string snn = new UserNationData().SelectNationCode(nat);
if (snn == "null")
{
Console.WriteLine("查無此民族！");
}
else
{
up.Nation = snn;
break;
}
}

bool b = new UsersData().UpdateUser(up);
if (b)
{
Console.WriteLine("修改成功！");
}
else
{
Console.WriteLine("修改失敗！！");
}

}
else
{
Console.WriteLine("無此資料");
}

Console.ReadKey();
break;
}

ADO.NET刪除和修改