功能展示
主窗体
新增固定资产窗体
查询固定资产窗体
数据库表
数据库名称assetsDB
表名 | assets | 中文表名称 | 固定资产信息表 | |||
序号 | 字段名称 | 字段说明 | 类型 | 长度 | 属性 | 备注 |
1 | id | 标识ID | bigint | 非空,标识列 | 主键 | |
2 | assetId | 资产编号 | nchar | 5 | 非空 | |
3 | assetName | 资产名称 | nvarchar | 100 | 非空 | |
4 | assetType | 资产类型 | nvarchar | 50 | 非空 | 检查约束:只能是电子设备、机械设备、运输设备 |
5 | intoDate | 入库日期 | datetime | 非空 | 检查约束:不能晚于当前日期 |
SQL语句
use assetsDB
--查看数据
select * from assets
select assetId,assetName,assetType,intoDate from assets where assetId = 'd1000'
--插入数据
insert into assets (assetId,assetName,assetType,intoDate)
values ('d1001','笔记本','电子设备','2010-9-30'),
('d1002','台式机','电子设备','2010-10-1'),
('d1003','蒸汽锅炉','机械设备','2010-11-12'),
('d1004','小轿车','运输设备','2010-1-6')
代码
主窗体部分
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace AssetsManagementSystem
{
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent();
}
/// <summary>
/// 新增固定资产选项
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void 新增固定资产ToolStripMenuItem_Click(object sender, EventArgs e)
{
NewlyIncreasedFixedAssets newlyIncreased = new NewlyIncreasedFixedAssets();
newlyIncreased.MdiParent = this;
newlyIncreased.Show();
}
/// <summary>
/// 查询固定资产选项
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void 查询固定资产ToolStripMenuItem_Click(object sender, EventArgs e)
{
QueryFxedAsets queryFxedAsets = new QueryFxedAsets();
queryFxedAsets.MdiParent = this;
queryFxedAsets.Show();
}
/// <summary>
/// 退出选项
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void 退出ToolStripMenuItem_Click(object sender, EventArgs e)
{
Application.Exit();
}
}
}
增加功能窗体
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;
namespace AssetsManagementSystem
{
public partial class NewlyIncreasedFixedAssets : Form
{
public NewlyIncreasedFixedAssets()
{
InitializeComponent();
}
/// <summary>
/// 增加按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_add_Click(object sender, EventArgs e)
{
//资产编号
string assetId = txt_assetId.Text;
//资产名称
string assetName = txt_assetName.Text;
//资产类型
string assetType = cmb_assetType.Text;
//入库日期
string intoDate = txt_intoDate.Text;
//判断信息是否完整
if (assetId.Equals(string.Empty) || assetName.Equals(string.Empty) || assetType.Equals(string.Empty) || intoDate.Equals(string.Empty))
{
MessageBox.Show("请完整填写信息!","信息提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
return;
}
try
{
SqlConnection conn = DBHelper.GetConnection();
DBHelper.Open();
string sql = $"insert into assets (assetId,assetName,assetType,intoDate) values('{assetId}', '{assetName}', '{assetType}', '{intoDate}')";
SqlCommand cmd = new SqlCommand(sql, conn);
int row = cmd.ExecuteNonQuery();
if (row > 0)
{
MessageBox.Show("新增成功!", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show("系统异常");
throw ex;
}
finally
{
DBHelper.Close();
}
}
/// <summary>
/// 清空按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_empty_Click(object sender, EventArgs e)
{
//清空资产编号
txt_assetId.Clear();
//清空资产名称
txt_assetName.Clear();
//清空资产类型
cmb_assetType.SelectedIndex = 0;
//清空入库日期
txt_intoDate.Clear();
}
}
}
查询功能窗体
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;
namespace AssetsManagementSystem
{
public partial class QueryFxedAsets : Form
{
DataSet ds = new DataSet();
SqlDataAdapter adapter = null;
public QueryFxedAsets()
{
InitializeComponent();
}
private void QueryFxedAsets_Load(object sender, EventArgs e)
{
QueryAll();
}
/// <summary>
/// 查询所有固定资产信息
/// </summary>
private void QueryAll()
{
try
{
SqlConnection conn = DBHelper.GetConnection();
string sql = "select assetId,assetName,assetType,intoDate from assets";
adapter = new SqlDataAdapter(sql, conn);
adapter.Fill(ds, "assets");
//将数据集 ds 与 DataGridView 控件的 DataSoure 绑定
dataGridView1.DataSource = ds.Tables["assets"];
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBHelper.Close();
}
}
/// <summary>
/// 查询按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
DataTable dt = this.dataGridView1.DataSource as DataTable;
dt.Rows.Clear();
this.dataGridView1.DataSource = dt;
string sql = "select assetId,assetName,assetType,intoDate from assets";
//资产编号
string assetId = txt_assetId.Text;
//资产类型
string assetType = cmb_assetType.Text;
//资产编号查询
if (!assetId.Equals(string.Empty) && assetType == "请选择类型")
{
sql += $" where assetId = '{assetId}'";
}
//资产类型查询
else if (assetId.Equals(string.Empty) && assetType != "请选择类型")
{
sql += $" where assetType = '{assetType}'";
}
//默认查询
else if (assetId.Equals(string.Empty) && assetType == "请选择类型")
{
sql += "";
}
try
{
SqlConnection conn = DBHelper.GetConnection();
adapter = new SqlDataAdapter(sql, conn);
adapter.Fill(ds, "assets");
//将数据集 ds 与 DataGridView 控件的 DataSoure 绑定
dataGridView1.DataSource = ds.Tables["assets"];
//判断查询不到结果的情况
if (ds.Tables["assets"].Rows.Count <= 0)
{
MessageBox.Show("没有查询到结果!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBHelper.Close();
}
}
}
}
连接数据库封装代码
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace AssetsManagementSystem
{
class DBHelper
{
public static SqlConnection conn = null;
/// <summary>
/// 获取数据库连接
/// </summary>
/// <returns></returns>
public static SqlConnection GetConnection()
{
string connStr = @"Data Source=.;Initial Catalog=assetsDB;User ID=sa;Password=root";
if (conn == null || conn.State == ConnectionState.Closed)
{
conn = new SqlConnection(connStr);
return conn;
}
else
{
return conn;
}
}
/// <summary>
/// 数据库连接打开
/// </summary>
public static void Open()
{
if (conn != null)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
else if (conn.State == ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
}
}
/// <summary>
/// 数据库连接关闭
/// </summary>
public static void Close()
{
if (conn != null)
{
conn.Close();
}
}
}
}