public static Customer getCustomerByAccount(string account) { SqlConnection conn = DBConnection.getConnection(); try { conn.Open(); } catch (SqlException e) { throw e; } Customer user = null; try { Console.WriteLine("初始化查询参数"+account); string sql = "SELECT [account],[password],[gender],[starttime],[availableday],[name] FROM available_user WHERE account=@account"; Console.WriteLine("连接开启"); using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue("@account", account); using (SqlDataReader dr = cmd.ExecuteReader()) { Console.WriteLine("结果:" + dr.HasRows); if (dr.Read()) { string name = Convert.ToString(dr["name"]); string password = Convert.ToString(dr["password"]); string gender = Convert.ToString(dr["gender"]); DateTime starttime = Convert.ToDateTime(dr["starttime"]); int availableday = Convert.ToInt32(dr["availableday"]); user = new Customer(); user.Account = account; user.Name = name; user.Password = password; user.Gender = gender; user.StartTime = starttime; user.AvailableDay = availableday; } dr.Close(); } } Console.WriteLine(user); } catch (Exception ex) { throw ex; } finally { DBConnection.closeConnection(conn); } return user; }
private void showCustomer(Customer c) { if (c != null) { this.hotel_search_res_account.BeginInvoke(new System.EventHandler(UpdateAccount), c.Account); this.hotel_search_res_name.BeginInvoke(new System.EventHandler(UpdateTime), c.Name); this.hotel_search_res_sex.BeginInvoke(new System.EventHandler(UpdateSex), c.Gender); this.hotel_search_res_status.BeginInvoke(new System.EventHandler(UpdateStaute), "注册时间为:" + c.StartTime+"\r\n有效时间是"+c.AvailableDay+"天"); ((HotelForm)this.MdiParent).UpdateState("查询成功!"); } else { ((HotelForm)this.MdiParent).ShowSearchRes("没有该用户注册信息!"); } }
internal static void RemoveCustomerDao(Customer u) { try { InsertUser("unavailable_user", u); }catch(Exception e) { Console.WriteLine(e.Message); } if (u == null) { return; } string sql = "DELETE FROM available_user WHERE account=@account"; SqlConnection con = DBConnection.getConnection(); try { Console.WriteLine("初始化删除参数"); con.Open(); Console.WriteLine("连接开启"); using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.Parameters.AddWithValue("@account", u.Account); if (cmd.ExecuteNonQuery() <= 0) { throw new Exception("移除失效用户失败!"); } } } catch (Exception ex) { throw new Exception(ex.Message); } finally { DBConnection.closeConnection(con); } }
private static void InsertUser(string TABLE_NAME, Customer user) { if (TABLE_NAME == null || user == null) { return; } SqlConnection con = DBConnection.getConnection(); try { Console.WriteLine("初始化" + TABLE_NAME + "插入参数"); con.Open(); string sql = "INSERT INTO " + TABLE_NAME + "(account,password, gender, starttime, availableday, name)" + " VALUES(@account,@password,@gender,@starttime,@availableday,@name)"; using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.Parameters.AddWithValue("@account", user.Account); cmd.Parameters.AddWithValue("@passowrd", user.Password); cmd.Parameters.AddWithValue("@gender", user.Gender); cmd.Parameters.AddWithValue("@starttime", user.StartTime); cmd.Parameters.AddWithValue("@availableday", user.AvailableDay); cmd.Parameters.AddWithValue("@name", user.Name); if (cmd.ExecuteNonQuery() <= 0) { throw new Exception("插入失败!"); } } } catch (Exception ex) { Console.WriteLine(ex.GetBaseException()); throw ex; } finally { DBConnection.closeConnection(con); } throw new Exception("插入成功!"); }
internal static void InsertCustomer(Customer c) { // 登记新用户 if (c == null) { return; } Console.WriteLine("插入新成员"); try { InsertUser("available_user", c); }catch(Exception e) { throw e; } }
internal static List<Customer> GetAvailableUser() { SqlConnection con = DBConnection.getConnection(); List<Customer> users = new List<Customer>(); try { Console.WriteLine("初始化查询参数"); con.Open(); string sql = "SELECT [account],[password],[gender],[starttime],[availableday],[name] FROM available_user"; Console.WriteLine("连接开启"); using (SqlCommand cmd = new SqlCommand(sql, con)) { using (SqlDataReader dr = cmd.ExecuteReader()) { Console.WriteLine("结果:" + dr.HasRows); while (dr.Read()) { string name = Convert.ToString(dr["name"]); string account = Convert.ToString(dr["account"]); string password = Convert.ToString(dr["password"]); string gender = Convert.ToString(dr["gender"]); DateTime starttime = Convert.ToDateTime(dr["starttime"]); int availableday = Convert.ToInt32(dr["availableday"]); Customer user = new Customer(); user.Account = account; user.Name = name; user.Password = password; user.Gender = gender; user.StartTime = starttime; user.AvailableDay = availableday; users.Add(user); } dr.Close(); } } Console.WriteLine(users); } catch (Exception ex) { throw new Exception(ex.Message); } finally { DBConnection.closeConnection(con); } return users; }