一、搭建环境
1、新建winform项目,命名build
2、在webconfig下面添加连接字符串
<connectionStrings>
<add name="HouseSystem" connectionString="data source=ADMIN-PC; database=HouseSystem; uid=sa; password=xxx"></add>
</connectionStrings>
3、解决方案下新建项目类,删掉默认生成的类,命名build.Common作为公共类库,新建类DbHelp,作为数据操作
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace Build.Common
{
public class DbHelper
{
private string connStr = ConfigurationManager.ConnectionStrings["HouseSystem"].ConnectionString;
// 连接函数
private SqlConnection conn = null;
private SqlConnection GetConnection()
{
return new SqlConnection(connStr);
}
// 重载查询scalar的封装(有参数),可变参数,
public object QueryScalar(string sql, params SqlParameter[] pars)
{
Open();
object result = null;
using(SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddRange(pars); // 添加参数
result = cmd.ExecuteScalar();
Close(); // 关闭数据库
return result;
}
}
// 查询语句的封装,可变参数
public DataTable QueryDataTable(string sql, params SqlParameter[] pars)
{
Open();
DataSet ds = new DataSet();
using(SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddRange(pars); // 先给cmd传参数
using(SqlDataAdapter adapter = new SqlDataAdapter(cmd)) // 在将cmd传给adapter
{
adapter.Fill(ds);
Close(); // 关闭数据库
return ds.Tables[0];
}
}
}
// 查询语句,以流的方式,不能关闭数据库,不然就没法read了
public SqlDataReader QueryReader(string sql, params SqlParameter[] pars)
{
Open();
SqlDataReader result = null;
using(SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddRange(pars);
result = cmd.ExecuteReader();
return result;
}
}
// 用于更新、修改、删除,返回影响行数
public int NonQuery(string sql, params SqlParameter[] pars)
{
conn.Open();
int result = 0;
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddRange(pars);
result = cmd.ExecuteNonQuery();
Close(); // 关闭数据库
return result;
}
}
// 打开数据库
private void Open()
{
if(conn == null)
{
conn = GetConnection();
}
if(conn.State == ConnectionState.Closed) // 如果连接状态是关闭的话,就打开
{
conn.Open();
}
}
// 关闭数据库
public void Close()
{
if (conn != null)
conn.Close();
}
}
}
二、登录操作
1、将Form1.cs改名为login.cs,登录的窗体
禁用最大化:MaximizeBox设为false
禁止调节窗口大小:FormBorderStyle设为FixedSingle
居中显示:StartPosition设为CenterScreen
2、加登录控件,密码的passwordChar改为*
3、美化皮肤:使用Irisskin
(1)网上下载IrisSkin,IrisSkin是控件,.ssk文件是皮肤
(2)将IrisSkin.dll和自己喜欢的皮肤.ssk放入../bin/debug下,为启动目录
(3)回到窗体设计页面,右键所有windows窗体->选择项->浏览->找到SkinEngine文件->确定->会在所有windows窗体下出现SkinEngine
(4)使用:将他拖到自己的窗体中,会出现在下面,修改他的name值为skin(随意)
(5)加载它:双击窗体,进入load方法
(6)运行查看效果,不满意换皮肤
4、登录的实现
(1)用户表
(2)角色表
有日常管理、内部管理、客户管理、房产管理、系统管理五个权限,true为有,false没有
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Build.Common;
namespace Build
{
public partial class login : Form
{
public login()
{
InitializeComponent();
}
private Boolean changeLogin = false;
// 重载构造,判断是不是切换登录;等于false是第一次登录,等于true是切换登录
public login(Boolean changeLogin)
{
this.changeLogin = changeLogin;
}
DbHelper db = new DbHelper();
public static Boolean _daily; // 日常管理的权限
public static Boolean _house; // 房源管理的权限
public static Boolean _guest; // 客户管理的权限
public static Boolean _inside; // 内部管理的权限
public static Boolean _system; // 系统管理的权限
private void login_Load(object sender, EventArgs e)
{
this.skin.SkinFile = "MacOS.ssk";
}
/// <summary>
/// 登录操作,登录成功,根据角色id,查找所拥有的权限,赋值给定义的权限,失败则提示失败
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
string username = this.username.Text;
string password = this.password.Text;
SqlParameter[] pers = {
new SqlParameter("@username", username),
new SqlParameter("@password", password)
};
string sql = "select * from UserInfo where username=@username and password=@password";
SqlDataReader user = db.QueryReader(sql, pers);
// 如果没有数据,返回false
if (user.Read()) // 只读取一条,多条要用while
{
int role_id = Convert.ToInt32(user["role_id"]);
db.Close(); // 关闭数据库,不然下面没法执行
// 获取权限
string sql1 = "select * from Role where id=@id";
DataTable role = db.QueryDataTable(sql1, new SqlParameter("@id", role_id));
_daily = Convert.ToBoolean(role.Rows[0]["daily"]);
_house = Convert.ToBoolean(role.Rows[0]["house"]);
_guest = Convert.ToBoolean(role.Rows[0]["guest"]);
_inside = Convert.ToBoolean(role.Rows[0]["inside"]);
_system = Convert.ToBoolean(role.Rows[0]["system"]);
// 判断是否是切换登录进来的
if(!changeLogin)
{
// 如果不是,就打开首页窗体,并隐藏登录窗体
Index index = new Build.Index();
index.Show();
this.Visible = false;
} else
{
// 只隐藏登录窗体就行了
this.Visible = false;
}
} else
{
// 登录失败
db.Close(); // 关闭数据库
MessageBox.Show("用户名或密码错误", "提示信息", MessageBoxButtons.OK);
this.password.Text = null;
}
}
// 点击取消,关闭窗体
private void button2_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
三、首页的实现(默认为日常管理)
1、建立index.cs
2、设计
3、DataGrIDView的配置
allUserToAddRows:false 禁止出现空行
MultiSelect:false 不允许选择多行
SelectionMode:点击某个单元格,全都选中
编辑列,dataPropertyName属性跟数据库中字段名相绑定,如id就是显示数据库中id的值
4、当进入首页时,中间的dataGridView要加载数据,和左边的导航要判断权限
(1)中间的房源信息数据表
(2)实现:进入index时初始化数据
private void Index_Load(object sender, EventArgs e)
{
this.skin.SkinFile = "MacOS.ssk";
// 判断权限,如果没有权限,左边的导航enable等于false
if(login._daily == false)
{
this.Daily.Enabled = false;
}
if(login._guest == false)
{
this.Guest.Enabled = false;
}
if(login._house == false)
{
this.House.Enabled = false;
}
if(login._inside == false)
{
this.Inside.Enabled = false;
}
if(login._system == false)
{
this.SysManage.Enabled = false;
}
// 初始化房源数据
string sql_houseMessage = "select * from HouseMessage";
this.HouseMessageView.AutoGenerateColumns = false; // 禁止生成列表头,我们已经自己生成了
DataTable HMView = db.QueryDataTable(sql_houseMessage);
HouseMessageView.DataSource = HMView;
}
效果:
5、当点击中间的dataGridView时,下方四个tab显示相应的内容,给dataGridView加cellClick时间
// 当点击中间的房源数据时,下面的四条信息随之出现
private void HouseMessageView_CellClick(object sender, DataGridViewCellEventArgs e)
{
try
{
// 获取当前点击列的id,并取出数据放进去
int house_id = Convert.ToInt32(this.HouseMessageView.Rows[e.RowIndex].Cells[0].Value);
// 房源详细信息:根据id将房源表中重要的信息展示
string sql_houseMessage = "select * from HouseMessage where id=@id";
this.HouseDetailView.AutoGenerateColumns = false;
DataTable HDView = db.QueryDataTable(sql_houseMessage, new SqlParameter("@id", house_id));
HouseDetailView.DataSource = HDView;
// 房屋保密信息:根据id找出房源中业主的信息
string sql_secret = "select * from HouseMessage where id = @id";
this.HouseSecretView.AutoGenerateColumns = false;
DataTable HSView = db.QueryDataTable(sql_secret, new SqlParameter("@id", house_id));
this.HouseSecretView.DataSource = HSView;
// 自动匹配:查找相同地区和地址的房源,但是id与被匹配的不相等
string area = HDView.Rows[0]["area"].ToString();
string address = HDView.Rows[0]["address"].ToString();
string sql_auto = "select * from HouseMessage where area=@area and address=@address and id != @id";
this.AutoSimilarView.AutoGenerateColumns = false;
SqlParameter[] pars = {
new SqlParameter("@area", area),
new SqlParameter("@address", address),
new SqlParameter("@id", house_id)
};
DataTable ASView = db.QueryDataTable(sql_auto, pars);
this.AutoSimilarView.DataSource = ASView;
// 房源跟进:根据id查找跟进信息
string sql_follow = "select * from Follow where h_id = @id";
this.HouseFollowView.AutoGenerateColumns = false;
DataTable HFView = db.QueryDataTable(sql_follow, new SqlParameter("@id", house_id));
this.HouseFollowView.DataSource = HFView;
} catch
{
}
}
需要的房源跟进表,Follow
效果:
7、输入房源信息查询
// 输入房源信息的查询
private void HouseMessageBtn_Click(object sender, EventArgs e)
{
if (this.HouseMessage.Text == "") // 为空直接return,不做操作
{
return;
}
string message = this.HouseMessage.Text;
// 根据时间,或物业,或区,或地址进行模糊查询
string sql_house = "select * from HouseMessage where created_time like @message or property like @message or area like @message or address like @message";
this.HouseMessageView.AutoGenerateColumns = false; // 禁止生成列表头,我们已经自己生成了
DataTable HMView = db.QueryDataTable(sql_house, new SqlParameter("@message", "%" + message + "%")); // 百分号要在这里拼接,不能直接写在sql中,不然无法识别
HouseMessageView.DataSource = HMView;
this.HouseMessageView.Refresh(); // 刷新
}
8、租房查询:点击租房查询,进入租房查询的窗体
(1)新建LendHouseSearch窗体
(2)点击显示
// 租房查询
private void LendHouseSearch_Click(object sender, EventArgs e)
{
LendHouseSearch lend = new Build.LendHouseSearch();
lend.ShowDialog();
}
(3)窗体的初始化和实现
public partial class LendHouseSearch : Form
{
public LendHouseSearch()
{
InitializeComponent();
}
DbHelper db = new DbHelper();
// 在load时赋初值
private void LendHouseSearch_Load(object sender, EventArgs e)
{
this.LendHouseSearchType.Text = "租房";
// 取出area中的数据
string sql = "select * from Area";
DataTable dt = db.QueryDataTable(sql);
this.LendHouseSearchArea.DataSource = dt;
this.LendHouseSearchArea.DisplayMember = "name"; // 显示name字段中的内容
this.LendHouseSearchArea.Text = "不限"; // 默认
this.LendHouseSearchHouse1.Text = "不限";
this.LendHouseSearchHouse2.Text = "不限";
this.LendHouseSearchHouse3.Text = "不限";
this.LendHouseSearchHouse4.Text = "不限";
}
// 点击确定时,查询
private void LendHouseSearchSubmit_Click(object sender, EventArgs e)
{
string sql = "select * from HouseMessage where ";
if(this.LendHouseSearchArea.Text != "不限") // 地区
{
sql = sql + "area = '" + this.LendHouseSearchArea.Text + "' and";
}
if(this.LengHouseSearchLowSum.Text != "") // 最小面积
{
sql = sql + "sum >= '" + this.LengHouseSearchLowSum.Text + "' and";
}
if(this.LendHouseSearchUpSum.Text != "") // 最大面积
{
sql = sql + "sum <= '" + this.LendHouseSearchUpSum.Text + "' and";
}
if(this.LendHouseSearchHouse1.Text != "不限") // 室
{
sql = sql + "house like '%" + this.LendHouseSearchHouse1.Text + "室%' and";
}
if(this.LendHouseSearchHouse2.Text != "不限") // 厅
{
sql = sql + "house like '%" + this.LendHouseSearchHouse2.Text + "厅%' and";
}
if(this.LendHouseSearchHouse3.Text != "不限") // 卫
{
sql = sql + "house like '% " + this.LendHouseSearchHouse3.Text + "卫%' and";
}
if(this.LendHouseSearchHouse4.Text != "不限") // 阳台
{
sql = sql + "house like '%" + this.LendHouseSearchHouse4.Text + "阳台%' and";
}
if(this.LendHouseSearchLowPrice.Text != "") // 最低价格
{
sql = sql + "price >= '" + this.LendHouseSearchLowPrice.Text + "' and";
}
if(this.LendHouseSearchUpPrice.Text != "") // 最高价格
{
sql = sql + "price <= '" + this.LendHouseSearchUpPrice.Text + "' and";
}
if(this.LendHouseSearchProperty.Text != "") // 物业名称
{
sql = sql + "property = '" + this.LendHouseSearchProperty.Text + "' and";
}
if(this.LendHouseSearchAddress.Text != "") // 地址
{
sql = sql + "address = '" + this.LendHouseSearchAddress.Text + "' and";
}
sql = sql + " lend = 1"; // 租房
this.LendHouseSearchResult.AutoGenerateColumns = false;
DataTable dt = db.QueryDataTable(sql);
this.LendHouseSearchResult.DataSource = dt;
}
// 点击取消关闭
private void LendHouseSearchCanel_Click(object sender, EventArgs e)
{
if (MessageBox.Show("你确定要关闭此窗口吗?", "提示信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
this.Close();
}
}
}
9、买房查询:点击买房查询,打开买房查询的窗体
(1)新建BuyHouseSearch窗体
(2)点击显示
// 买房查询
private void button2_Click(object sender, EventArgs e)
{
BuyHouseSearch buy = new BuyHouseSearch();
buy.ShowDialog();
}
(3)实现和租房查询类似
public partial class BuyHouseSearch : Form
{
public BuyHouseSearch()
{
InitializeComponent();
}
DbHelper db = new DbHelper();
private void BuyHouseSearch_Load(object sender, EventArgs e)
{
this.BuyHouseSearchType.Text = "出售";
// 取出area中的数据
string sql = "select * from Area";
DataTable dt = db.QueryDataTable(sql);
this.BuyHouseSearchArea.DataSource = dt;
this.BuyHouseSearchArea.DisplayMember = "name"; // 显示name字段中的内容
this.BuyHouseSearchArea.Text = "不限"; // 默认
this.BuyHouseSearchHouse1.Text = "不限";
this.BuyHouseSearchHouse2.Text = "不限";
this.BuyHouseSearchHouse3.Text = "不限";
this.BuyHouseSearchHouse4.Text = "不限";
}
private void BuyHouseSearchSubmit_Click(object sender, EventArgs e)
{
string sql = "select * from HouseMessage where ";
if (this.BuyHouseSearchArea.Text != "不限") // 地区
{
sql = sql + "area = '" + this.BuyHouseSearchArea.Text + "' and ";
}
if (this.BuyHouseSearchLowSum.Text != "") // 最小面积
{
sql = sql + "sum >= '" + this.BuyHouseSearchLowSum.Text + "' and ";
}
if (this.BuyHouseSearchUpSum.Text != "") // 最大面积
{
sql = sql + "sum <= '" + this.BuyHouseSearchUpSum.Text + "' and ";
}
if (this.BuyHouseSearchHouse1.Text != "不限") // 室
{
sql = sql + "house like '%" + this.BuyHouseSearchHouse1.Text + "室%' and ";
}
if (this.BuyHouseSearchHouse2.Text != "不限") // 厅
{
sql = sql + "house like '%" + this.BuyHouseSearchHouse2.Text + "厅%' and ";
}
if (this.BuyHouseSearchHouse3.Text != "不限") // 卫
{
sql = sql + "house like '% " + this.BuyHouseSearchHouse3.Text + "卫%' and ";
}
if (this.BuyHouseSearchHouse4.Text != "不限") // 阳台
{
sql = sql + "house like '%" + this.BuyHouseSearchHouse4.Text + "阳台%' and ";
}
if (this.BuyHouseSearchLowPrice.Text != "") // 最低价格
{
sql = sql + "price >= '" + this.BuyHouseSearchLowPrice.Text + "' and ";
}
if (this.BuyHouseSearchUpPrice.Text != "") // 最高价格
{
sql = sql + "price <= '" + this.BuyHouseSearchUpPrice.Text + "' and ";
}
if (this.BuyHouseSearchProperty.Text != "") // 物业名称
{
sql = sql + "property = '" + this.BuyHouseSearchProperty.Text + "' and ";
}
if (this.BuyHouseSearchAddress.Text != "") // 地址
{
sql = sql + "address = '" + this.BuyHouseSearchAddress.Text + "' and ";
}
sql = sql + " sell = 1"; // 买房
MessageBox.Show(sql);
this.BuyHouseSearchResult.AutoGenerateColumns = false;
DataTable dt = db.QueryDataTable(sql);
this.BuyHouseSearchResult.DataSource = dt;
}
private void BuyHouseSearchCanel_Click(object sender, EventArgs e)
{
if (MessageBox.Show("你确定要关闭此窗口吗?", "提示信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
this.Close();
}
}
}
10、新增房源
(1)新建AddHouse窗体
(2)点击新增房源打开,和上方的新增房源相同,上面的新增房源有鼠标进入和移出背景改变
// 中间的新增房源
private void IndexAddHouse_Click(object sender, EventArgs e)
{
AddHouse add = new Build.AddHouse();
add.ShowDialog();
}
// 上方导航的新增房源
private void AddHouseImage_MouseEnter(object sender, EventArgs e)
{
Bitmap map = new Bitmap(Application.StartupPath + "/img/index_nav/新增房源_active.png");
this.AddHouseImage.Image = map;
}
private void AddHouseImage_MouseLeave(object sender, EventArgs e)
{
Bitmap map = new Bitmap(Application.StartupPath + "/img/index_nav/新增房源.png");
this.AddHouseImage.Image = map;
}
// 和中间的新增房源相同
private void AddHouseImage_Click(object sender, EventArgs e)
{
AddHouse add = new Build.AddHouse();
add.ShowDialog();
}
(3)实现
public partial class AddHouse : Form
{
public AddHouse()
{
InitializeComponent();
}
// 编辑重载,如果id是0就是添加,如果不是就是编辑页面
public int id = 0;
public AddHouse(int id)
{
InitializeComponent(); // 要初始化原来的控件,当传入参数时,不会执行无参的构造函数
this.id = id;
}
DbHelper db = new DbHelper();
private void AddHouse_Load(object sender, EventArgs e)
{
// 房产来源的初始化
DataTable source = db.QueryDataTable("select * from Source");
this.AddHouseSource.DataSource = source;
this.AddHouseSource.DisplayMember = "name";
// 当前状态的初始化
DataTable status = db.QueryDataTable("select * from Status");
this.AddHouseStatus.DataSource = status;
this.AddHouseStatus.DisplayMember = "name";
// 置业顾问的初始化
DataTable adviser = db.QueryDataTable("select * from Adviser");
this.AddHouseAdviser.DataSource = adviser;
this.AddHouseAdviser.DisplayMember = "name";
// 物业用途
DataTable used = db.QueryDataTable("select * from Used");
this.AddHouseUsed.DataSource = used;
this.AddHouseUsed.DisplayMember = "name";
// 物业类别
DataTable propertyType = db.QueryDataTable("select * from PropertyType");
this.AddHousePropertyType.DataSource = propertyType;
this.AddHousePropertyType.DisplayMember = "name";
// 装修程度
DataTable decorate = db.QueryDataTable("select * from Decorate");
this.AddHouseDecorate.DataSource = decorate;
this.AddHouseDecorate.DisplayMember = "name";
// 所处区域
DataTable area = db.QueryDataTable("select * from Area");
this.AddHouseArea.DataSource = area;
this.AddHouseArea.DisplayMember = "name";
// 户型结构 默认为0
this.AddHouseHouse1.Text = "0";
this.AddHouseHouse2.Text = "0";
this.AddHouseHouse3.Text = "0";
this.AddHouseHouse4.Text = "0";
// 价格默认为0
this.AddHouseSellPrice.Text = "0";
this.AddHouseLendPrice.Text = "0";
// 如果id不等于0,就代表是编辑页面,要赋值
if(id > 0)
{
string sql = "select * from HouseMessage where id = @id";
DataTable house = db.QueryDataTable(sql, new SqlParameter("@id", id));
foreach (DataRow dr in house.Rows)
{
this.AddHouseSource.Text = dr["source"].ToString();
this.AddHouseStatus.Text = dr["status"].ToString();
this.AddHouseAdviser.Text = dr["adviser"].ToString();
this.AddHouseProperty.Text = dr["property"].ToString();
this.AddHouseUsed.Text = dr["used"].ToString();
this.AddHousePropertyType.Text = dr["propertyType"].ToString();
this.AddHouseDecorate.Text = dr["decorate"].ToString();
this.AddHouseAddress.Text = dr["address"].ToString();
this.AddHouseArea.Text = dr["area"].ToString();
this.AddHouseYear.Text = dr["year"].ToString();
this.AddHouseHouse1.Text = dr["house"].ToString().Substring(0, 1);
this.AddHouseHouse2.Text = dr["house"].ToString().Substring(2, 1);
this.AddHouseHouse3.Text = dr["house"].ToString().Substring(4, 1);
this.AddHouseHouse4.Text = dr["house"].ToString().Substring(6, 1);
this.AddHouseSum.Text = dr["sum"].ToString();
this.AddHouseTotalFloor.Text = dr["totalFloor"].ToString();
this.AddHouseFloor.Text = dr["floor"].ToString();
this.AddHouseMasterName.Text = dr["masterName"].ToString();
this.AddHouseMasterTel.Text = dr["masterTel"].ToString();
this.AddHouseMatserMore.Text = dr["masterMore"].ToString();
this.AddHouseMasterAdd.Text = dr["masterAdd"].ToString();
this.AddHouseLend.Checked = Convert.ToInt32(dr["lend"]) == 1 ? true : false;
this.AddHouseLendPrice.Text = dr["lendPrice"].ToString();
this.AddHouseLendMore.Text = dr["lendMore"].ToString();
this.AddHouseSell.Checked = Convert.ToInt32(dr["sell"]) == 1 ? true : false;
this.AddHouseSellPrice.Text = dr["price"].ToString();
this.AddHouseSellMore.Text = dr["sellMore"].ToString();
}
}
}
// 保存按钮
private void AddHouseBtnSubmit_Click(object sender, EventArgs e)
{
// 判断不能为空
if(this.AddHouseProperty.Text == "")
{
MessageBox.Show("物业名称不能为空");
return;
}
if(this.AddHouseAddress.Text == "")
{
MessageBox.Show("具体地址不能为空");
return;
}
if(this.AddHouseYear.Text == "")
{
MessageBox.Show("建成年份不能为空");
return;
}
if(this.AddHouseSum.Text == "")
{
MessageBox.Show("建筑面积不能为空");
return;
}
if(this.AddHouseTotalFloor.Text == "")
{
MessageBox.Show("总层数不能为空");
return;
}
if(this.AddHouseFloor.Text == "")
{
MessageBox.Show("层数不能为空");
return;
}
if(Convert.ToInt32(this.AddHouseTotalFloor.Text) < Convert.ToInt32(this.AddHouseFloor.Text))
{
MessageBox.Show("所处层数不能大于总层数");
return;
}
if(this.AddHouseMasterName.Text == "")
{
MessageBox.Show("业主姓名不能为空");
return;
}
if(this.AddHouseMasterTel.Text == "")
{
MessageBox.Show("业主电话不能为空");
return;
}
if(this.AddHouseMasterAdd.Text == "")
{
MessageBox.Show("业主地址不能为空");
return;
}
if(this.AddHouseMatserMore.Text == "")
{
MessageBox.Show("业主说明不能为空");
return;
}
if(this.AddHouseLend.Checked == false && this.AddHouseSell.Checked == false)
{
MessageBox.Show("出售和出租必须选择一个");
return;
}
// 获取数据
string source = this.AddHouseSource.Text;
DateTime created_time = DateTime.Now;
string status = this.AddHouseStatus.Text;
string adviser = this.AddHouseAdviser.Text;
string property = this.AddHouseProperty.Text;
string used = this.AddHouseUsed.Text;
string propertyType = this.AddHousePropertyType.Text;
string decorate = this.AddHouseDecorate.Text;
string address = this.AddHouseAddress.Text;
string area = this.AddHouseArea.Text;
int year = Convert.ToInt32(this.AddHouseYear.Text);
string house = this.AddHouseHouse1.Text + "室" + this.AddHouseHouse2.Text + "厅" + this.AddHouseHouse3.Text + "卫" + this.AddHouseHouse4.Text + "阳台";
int sum = Convert.ToInt32(this.AddHouseSum.Text);
int totalFloor = Convert.ToInt32(this.AddHouseTotalFloor.Text);
int floor = Convert.ToInt32(this.AddHouseFloor.Text);
// 出租选中
Boolean lend = false;
Boolean sell = false;
int lendPrice = 0;
string lendMore = "";
int sellPrice = 0;
string sellMore = "";
if(this.AddHouseLend.Checked == true)
{
lend = true;
sell = false;
lendPrice = Convert.ToInt32(this.AddHouseLendPrice.Text);
lendMore = this.AddHouseLendMore.Text;
sellPrice = Convert.ToInt32(this.AddHouseSellPrice.Text);
sellMore = this.AddHouseSellMore.Text;
}
// 出售选中
if (this.AddHouseSell.Checked == true)
{
lend = false;
sell = true;
lendPrice = Convert.ToInt32(this.AddHouseLendPrice.Text);
lendMore = this.AddHouseLendMore.Text;
sellPrice = Convert.ToInt32(this.AddHouseSellPrice.Text);
sellMore = this.AddHouseSellMore.Text;
}
// 出租出售都选中
if(this.AddHouseSell.Checked == true && this.AddHouseLend.Checked == true)
{
lend = true;
sell = true;
lendPrice = Convert.ToInt32(this.AddHouseLendPrice.Text);
lendMore = this.AddHouseLendMore.Text;
sellPrice = Convert.ToInt32(this.AddHouseSellPrice.Text);
sellMore = this.AddHouseSellMore.Text;
}
string masterName = this.AddHouseMasterName.Text;
string masterTel = this.AddHouseMasterTel.Text;
string masterAdd = this.AddHouseMasterAdd.Text;
string masterMore = this.AddHouseMatserMore.Text;
SqlParameter[] pars = {
new SqlParameter("@source", source),
new SqlParameter("@created_time",created_time),
new SqlParameter("@status",status),
new SqlParameter("@property",property),
new SqlParameter("@house",house),
new SqlParameter("@sum",sum),
new SqlParameter("@area",area),
new SqlParameter("@totalFloor",totalFloor),
new SqlParameter("@floor",floor),
new SqlParameter("@adviser",adviser),
new SqlParameter("@used",used),
new SqlParameter("@propertyType",propertyType),
new SqlParameter("@decorate",decorate),
new SqlParameter("@year",year),
new SqlParameter("@address",address),
new SqlParameter("@masterName",masterName),
new SqlParameter("@masterTel",masterTel),
new SqlParameter("@masterAdd",masterAdd),
new SqlParameter("@masterMore",masterMore),
new SqlParameter("@lend",lend),
new SqlParameter("@sell",sell),
new SqlParameter("@price",sellPrice),
new SqlParameter("@lendPrice",lendPrice),
new SqlParameter("@sellMore",sellMore),
new SqlParameter("@lendMore",lendMore)
};
if (id == 0) // 执行保存的语句
{
string sql = "insert into HouseMessage values(@source, @created_time, @status, @property, @house, @sum, @area, @totalFloor, @floor, @adviser, @used, @propertyType, @decorate, @year, @address, @masterName, @masterTel, @masterAdd, @masterMore, @lend, @sell, @price, @lendPrice, @sellMore, @lendMore)";
int res = db.NonQuery(sql, pars);
if (res > 0)
{
MessageBox.Show("保存成功");
this.Close();
}
else
{
MessageBox.Show("保存失败");
this.Close();
}
} else // 执行更新的语句
{
string sql = "update HouseMessage set source = @source, created_time = @created_time, status = @status, property = @property, house = @house, sum = @sum, area = @area, totalFloor = @totalFloor, floor = @floor, adviser = @adviser, used = @used, propertyType = @propertyType, decorate = @decorate, year = @year, address = @address, masterName = @masterName, masterTel = @masterTel, masterAdd = @masterAdd, masterMore = @masterMore, lend = @lend, sell = @sell, price = @price, lendPrice = @lendPrice, sellMore = @sellMore, lendMore = @lendMore where id = " + id;
int res = db.NonQuery(sql, pars);
if(res > 0)
{
MessageBox.Show("修改成功");
this.Close();
} else
{
MessageBox.Show("修改失败");
this.Close();
}
}
}
// 取消按钮
private void AddHouseBtnCancel_Click(object sender, EventArgs e)
{
this.Close();
}
}
11、中间几个checkbox的实现
// 出租的复选框选中
private void ShowLendMessage_CheckedChanged(object sender, EventArgs e)
{
// 当选中的时候,将出租的信息筛选出来
if(this.ShowLendMessage.Checked == true)
{
string sql_len = "select * from HouseMessage where lend = 1";
this.HouseMessageView.AutoGenerateColumns = false;
DataTable HMView = db.QueryDataTable(sql_len);
this.HouseMessageView.DataSource = HMView;
this.HouseMessageView.Refresh(); // 刷新
}
// 当出租选中且出售选中,或者两个都不选中,显示全部信息(房源只有出租和出售两种情况)
if(this.ShowLendMessage.Checked == true && this.ShowSellMessage.Checked == true || this.ShowLendMessage.Checked == false && this.ShowSellMessage.Checked == false)
{
string sql_houseMessage = "select * from HouseMessage";
this.HouseMessageView.AutoGenerateColumns = false;
DataTable HMView = db.QueryDataTable(sql_houseMessage);
HouseMessageView.DataSource = HMView;
this.HouseMessageView.Refresh();
}
// 当出租取消选中,出售选中的时候,显示出售信息
if(this.ShowLendMessage.Checked == false && this.ShowSellMessage.Checked == true)
{
string sql_sellMessage = "select * from HouseMessage where sell = 1";
this.HouseMessageView.AutoGenerateColumns = false;
DataTable HMView = db.QueryDataTable(sql_sellMessage);
this.HouseMessageView.DataSource = HMView;
this.HouseMessageView.Refresh();
}
}
// 当出售选中的时候
private void ShowSellMessage_CheckedChanged(object sender, EventArgs e)
{
// 筛选出出售的信息,显示出售
if(this.ShowSellMessage.Checked == true)
{
string sql_sellMessage = "select * from HouseMessage where sell = 1";
this.HouseMessageView.AutoGenerateColumns = false;
DataTable HMView = db.QueryDataTable(sql_sellMessage);
this.HouseMessageView.DataSource = HMView;
this.HouseMessageView.Refresh();
}
// 当出售选中,出租也选中或两个都没选中的时候,都显示
if (this.ShowLendMessage.Checked == true && this.ShowSellMessage.Checked == true || this.ShowLendMessage.Checked == false && this.ShowSellMessage.Checked == false)
{
string sql_houseMessage = "select * from HouseMessage";
this.HouseMessageView.AutoGenerateColumns = false;
DataTable HMView = db.QueryDataTable(sql_houseMessage);
HouseMessageView.DataSource = HMView;
this.HouseMessageView.Refresh();
}
// 当出售没选中,出租选中的时候,显示出租
if(this.ShowSellMessage.Checked == false && this.ShowLendMessage.Checked == true)
{
string sql_len = "select * from HouseMessage where lend = 1";
this.HouseMessageView.AutoGenerateColumns = false;
DataTable HMView = db.QueryDataTable(sql_len);
this.HouseMessageView.DataSource = HMView;
this.HouseMessageView.Refresh(); // 刷新
}
}
// 最近信息中的表单只能是数字,所以加键盘按下的事件
private void ShowRecentMessageInput_KeyPress(object sender, KeyPressEventArgs e)
{
if((int)e.KeyChar >= 48 && (int)e.KeyChar <= 57 || (int)e.KeyChar == 8)
{
// 如果按下的键是0-9或是删除键,0的acsii码48,不做操作
} else
{
e.Handled = true; // 去除不合法字符
MessageBox.Show("请输入数字");
}
}
// 显示最近信息选中时,
private void ShowRecentMessage_CheckedChanged(object sender, EventArgs e)
{
if(this.ShowRecentMessage.Checked == true)
{
if(this.ShowRecentMessageInput.Text == "") // 为空时,直接return
{
return;
}
int days = Convert.ToInt32(this.ShowRecentMessageInput.Text); // 转换为天
// 查询小于明天的,大于days之前的
string sql_recent = "select * from HouseMessage where created_time <= @tomorrow and created_time >= @last";
SqlParameter[] pars = {
new SqlParameter("@tomorrow", DateTime.Now.AddDays(1)),
new SqlParameter("@last", DateTime.Now.AddDays(-days)) // 负数
};
this.HouseMessageView.AutoGenerateColumns = false; // 禁止生成列表头,我们已经自己生成了
DataTable HMView = db.QueryDataTable(sql_recent, pars);
HouseMessageView.DataSource = HMView;
this.HouseMessageView.Refresh();
// 前面两个查询禁止
this.ShowLendMessage.Enabled = false;
this.ShowSellMessage.Enabled = false;
} else
{
this.ShowRecentMessageInput.Text = "";
this.ShowLendMessage.Enabled = true;
this.ShowSellMessage.Enabled = true;
string sql_houseMessage = "select * from HouseMessage";
this.HouseMessageView.AutoGenerateColumns = false; // 禁止生成列表头,我们已经自己生成了
DataTable HMView = db.QueryDataTable(sql_houseMessage);
HouseMessageView.DataSource = HMView;
this.HouseMessageView.Refresh();
}
}
// 刷新按钮,重新初始化数据
private void HouseMessageRefresh_Click(object sender, EventArgs e)
{
string sql_houseMessage = "select * from HouseMessage";
this.HouseMessageView.AutoGenerateColumns = false; // 禁止生成列表头,我们已经自己生成了
DataTable HMView = db.QueryDataTable(sql_houseMessage);
HouseMessageView.DataSource = HMView;
this.HouseMessage.Text = ""; // 将表单复选框都清空
this.ShowLendMessage.Checked = false;
this.ShowSellMessage.Checked = false;
this.ShowRecentMessage.Checked = false;
}