数据库
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();
}
}
}
}
}