超市管理系統

資料庫

use master
if exists (select * from sysdatabases where name='SuperMark')
drop database SuperMark
create database SuperMark
on PRIMARY
(
name='SuperMark_data',
FILENAME='F:\SuperMark.mdf',
filegrowth=20%,
size=10MB
)
LOG ON
(
name='SuperMark_log',
FILENAME='F:\SuperMark_log.ldf',
size=3MB,
MAXSIZE=20MB
)

USE [SuperMark]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[States](
[Id] [int] IDENTITY(1,1) NOT NULL,
[StatesName] [varchar](20) NOT NULL,
CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[UsersInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CustomerId] [varchar](20) NOT NULL,
[CustomerPassword] [varchar](20) NOT NULL,
[CustomerType] [varchar](10) NOT NULL,
[Score] [int] NOT NULL,
[statusId] [int] NOT NULL,
CONSTRAINT [PK_UsersInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[UsersInfo] WITH CHECK ADD CONSTRAINT [FK_UsersInfo_States] FOREIGN KEY([statusId])
REFERENCES [dbo].[States] ([Id])
GO

ALTER TABLE [dbo].[UsersInfo] CHECK CONSTRAINT [FK_UsersInfo_States]
GO

窗體

帳號文本框 name 為 txtCustomerId
密碼文本框 name為txtPwd
金卡 name 為rdoJinka
鉑金卡 name為rdoBojinka
鑽石卡 name為rdoZuanshika
狀態 下拉框 name 為cboStatus
賬戶積分文本框name 為 txtScore 屬性ReadOnly 為True
顯示控制項 name為dgvUserInfo
ID 綁定數據Id
賬號 綁定數據CustomerId
卡別綁定數據CustomerType
積分綁定數據Score
狀態綁定數據StatesName

後臺代碼

鏈接資料庫字元串請根據實際情況而定

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;
using System.Data.SqlClient;

namespace SuperMarkSystem
{
public partial class FrmMain : Form
{
//鏈接資料庫字元串
string connStr = "Data Source=.;Initial Catalog=SuperMark;Integrated Security=True";
DataSet ds;
SqlDataAdapter adapter;

public FrmMain()
{
InitializeComponent();
}

private void FrmMain_Load(object sender, EventArgs e)
{
this.dgvUserInfo.AutoGenerateColumns = false;

BindStates();

BindUserInfo();
}

//綁定會員狀態下拉菜單
private void BindStates()
{
try
{
SqlConnection conn = new SqlConnection(connStr);
ds = new DataSet();
string sql = "SELECT Id,StatesName FROM States";
adapter = new SqlDataAdapter(sql, conn);
adapter.Fill(ds, "States");
this.cboStatus.DataSource = ds.Tables["States"];
this.cboStatus.ValueMember = "Id";
this.cboStatus.DisplayMember = "StatesName";
this.cboStatus.SelectedIndex = 0;
}
catch (Exception)
{

}
}

private void btnExit_Click(object sender, EventArgs e)
{
Application.Exit();
}

private void rdoJinka_CheckedChanged(object sender, EventArgs e)
{
if (rdoJinka.Checked==true)
{
txtScore.Text = "500";
}
}

private void rdoBojinka_CheckedChanged(object sender, EventArgs e)
{
if (rdoBojinka.Checked == true)
{
txtScore.Text = "2000";
}
}

private void rdoZuanshika_CheckedChanged(object sender, EventArgs e)
{
if (rdoZuanshika.Checked == true)
{
txtScore.Text = "5000";
}
}

/// <summary>
/// 綁定會員信息
/// </summary>
private void BindUserInfo()
{
try
{
SqlConnection conn = new SqlConnection(connStr);
ds = new DataSet();
string sql = "SELECT u.Id,CustomerId,CustomerType,Score,s.StatesName FROM UsersInfo AS u,States AS s WHERE u.statusId=s.Id";
adapter = new SqlDataAdapter(sql, conn);
adapter.Fill(ds, "UsersInfo");
this.dgvUserInfo.DataSource = ds.Tables["UsersInfo"];
}
catch (Exception)
{

}
}

private void btnAdd_Click(object sender, EventArgs e)
{
if (txtCustomerId.Text.Trim().Equals(string.Empty) || txtPwd.Text.Trim().Equals(string.Empty) || cboStatus.Text.Trim().Equals(string.Empty))
{
MessageBox.Show("請填寫完整的會員信息！");
return;
}
SqlConnection conn = new SqlConnection(connStr);
try
{
string customerType = string.Empty;
if (rdoJinka.Checked == true)
{
customerType = "金卡";
}
else if (rdoBojinka.Checked == true)
{
customerType = "鉑金卡";
}
else
{
customerType = "鑽石卡";
}
string sql = string.Format(@"INSERT INTO UsersInfo(CustomerId,CustomerPassword,CustomerType,Score,statusId)
VALUES('{0}','{1}','{2}',{3},{4})",
txtCustomerId.Text.Trim(),
txtPwd.Text.Trim(),
customerType,
txtScore.Text.Trim(),
cboStatus.SelectedValue);
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
int count = command.ExecuteNonQuery();
if (count == 1)
{
MessageBox.Show("新增成功！");
//刷新用戶列表數據
BindUserInfo();
}
else
{
MessageBox.Show("新增失敗！");
}
}
catch (Exception)
{
MessageBox.Show("發生異常！");
}
finally
{
conn.Close();
}
}

private void 刪除ToolStripMenuItem_Click(object sender, EventArgs e)
{
string id = this.dgvUserInfo.SelectedRows[0].Cells["Id"].Value.ToString();
if (MessageBox.Show("確定要刪除該條信息嗎？" + id, "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
SqlConnection conn = new SqlConnection(connStr);
try
{
string sql = "delete from UsersInfo where id=" + id;
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
int count = command.ExecuteNonQuery();
if (count == 1)
{
MessageBox.Show("刪除成功！");
//刷新用戶列表數據
BindUserInfo();
}
else
{
MessageBox.Show("刪除成功！");
}
}
catch (Exception)
{
MessageBox.Show("發生異常！");
}
finally
{
conn.Close();
}
}

}
}
}