说明:程序是在.NET 4.0下用C#语言编写
COM组件也是用C#编写
一. 功能需求:
1. 能存款
2. 能取款
3. 能查询余额及交易明细
4. 能转账
5. 能修改密码
二. 总体设计计:
1. 客户端设计:
(1) Admin类(登录窗体)
(2) MainForm类(主窗体)
(3) WithDraw类(取款窗体)
(4) Deposit类(存款窗体)
(5) TransForm类(转账窗体)
(6) Query类(查询余额及明细窗体)
(7) ModifyPwd类(修改密码窗体)
(8) OpenAccountInfo类(开户窗体)
2. COM组件端的函数设计:
(1) 连接数据库函数组
A. ReadSqlConnString()(读取连接字符串)
B. GetConnection()(打开与数据库的连接)
C. Con_Close()(关闭与数据库的连接)
D. GetDataSet()(将查询结果表填充在DataSet中)
(2) Proc_Admin()(用户登录验证)
(3) Manager_Admin(管理员登录验证)
(4) WhithDraw()(取款)
(5) Deposit()(存款)
(6) TransForm()(转账)
(7) Query()(查询)
(8) ModifyPwd()(修改密码)
(9) Proc_CreateAccount()(开户)
3. 数据库设计:
(1) 表设计
userInfo(用户信息表)
cardInfo(卡信息表)
transInfo(交易信息表)
manager(管理员信息表)
(2) 增加约束条件
如:设置主键
电话号码必须为数字
身份证号为18位
存款类型:活期、定期及定活两便三者之一
等约束。
(3) 创建存储过程
(proc_createAccount)开户存储过程
(proc_takeMoney)存取款存储过程
(proc_randCardNo)随机产生卡号存储过程
(proc_tranMoney)转账存储过程
三. 详细设计:
1. 客户端设计:
(1) 登录窗体
(2)主窗体
(3)取款窗体
(4)存款窗体
(5)查询余额及明细窗体
(6)修改密码窗体
(7)开户窗体
2.COM组件端的函数设计:
(8) 连接数据库函数组
A. ReadSqlConnString()(读取连接字符串)
#region 读取连接字符串
///<summary>
///
///</summary>
public static string ReadSqlConString()
{
StreamReader sr = new StreamReader("DatabaseLinkString.ini");
string DatabaseLinkString = "";
while (!sr.EndOfStream)
{
DatabaseLinkString += sr.ReadLine();
}
sr.Close();
return DatabaseLinkString;
}
#endregion
B.GetConnection()(打开与数据库的连接)
#region 打开与数据库的连接
///<summary>
///打䨰开a与®?数ºy据Y库a的Ì?连¢?接¨®
///</summary>
///<returns>返¤¦Ì回?SqlConnection对?象¨®</returns>
public static SqlConnection GetConnection()
{
try
{
// StrConn = ReadSqlConString1(ID,Pwd);
StrConn = ReadSqlConString();
conn = new SqlConnection(StrConn); //用®?SqlConnection对?
if (conn.State != ConnectionState.Open)
conn.Open(); //打开与数据库的连接¨®
return conn; //返¤¦Ì回?SqlConnection对?象¨®
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion
C.Con_Close()(关闭与数据库的连接)
#region 关?闭À?数ºy据Y库a连¢?接¨®
///<summary>
///关?闭À?数ºy据Y库a连¢?接¨®
///</summary>
public void Con_Close()
{
if (conn.State == ConnectionState.Open) //判D断?数ºy据Y库a是º?否¤
{
conn.Close(); //关?闭À?数ºy据Y库a连¢?接¨®
conn.Dispose(); //释º¨ª放¤?My_con
}
}
#endregion
D.GetDataSet()(将查询结果表填充在DataSet中)
#region 执¡ä行DSQL语®?句?,返¤¦Ì回?DataSet对?象¨®
/// <summary>
/// 执¡ä行DSQL语®?句?,返¤¦Ì回?DataSet对?象¨®
/// </summary>
/// <param name="sql">Sql语®?句?</param>
/// <returns>DataSet对?象¨®</returns>
public DataSet GetDataSet(string sql)
{
GetConnection();
try
{
var da = new SqlDataAdapter(sql, conn); var ds = new DataSet(); //
da.Fill(ds); Con_Close();
return ds;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
Con_Close();
}
}
#endregion
(9)Proc_Admin()(用户登录验证)
#region 用户登录验证
/// <summary>
/// 用®?户¡ì登Ì?录?验¨¦证¡è
/// </summary>
/// <param name="userName">用®?户¡ì名?</param>
/// <param name="passWord">密¨¹码?</param>
/// <param name="cardId">卡¡§号?</param>
/// <returns>返¤¦Ì回?值¦Ì为a 1 则¨°登Ì?录?成¨¦功|,ê?为a 0 则¨°登Ì?录?失º¡ì败㨹</returns>
public DataTable Proc_Admin(string userName, string passWord, string cardId)
{
string sql = "Select * from cardInfo where cardID = '" + cardId + "' and password = '" + passWord + "'";
var getds = GetDataSet(sql);
var dt = getds.Tables[0];
return dt;
}
#endregion
(10)Manager_Admin(管理员登录验证)
#region 管¨¹理¤¨ª员¡À登Ì?录?验¨¦证¡è
/// <summary>
/// 管¨¹理¤¨ª员¡À登Ì?录?验¨¦证¡è
/// </summary>
/// <param name="userName">管¨¹理¤¨ª员¡À名?</param>
/// <param name="passWord">密¨¹码?</param>
/// <param name="cardId">卡¡§号?</param>
/// <returns>返¤¦Ì回?值¦Ì为a 1 则¨°登Ì?录?成¨¦功|,ê?为a 0 则¨°登Ì?录?失º¡ì败㨹</returns>
public DataTable Manager_Admin(string managerName, string passWord, string cardId)
{
string sql = "Select * from manager where managerId = '" + cardId + "' and managePwd = '" + passWord + "' and managerName = '"+managerName+"'";
var getds = GetDataSet(sql);
var dt = getds.Tables[0];
return dt;
}
#endregion
(11)Proc_TransForm(转账)
#region 转Áa账?
/// <summary>
/// 转Áa账?
/// </summary>
/// <param name="fromCardId">转Áa出?的Ì?卡¡§号?</param>
/// <param name="toCardId">转Áa入¨?的Ì?卡¡§号?</param>
/// <param name="money">金e额?</param>
/// <param name="passWord">转Áa出?卡¡§的Ì?密¨¹码?</param>
/// <returns>返¤¦Ì回?存ä?储ä¡é过y程¨¬的Ì?提¬¨¢示º?信?息¡é</returns>
public string Proc_TransForm(string fromCardId,string toCardId,string money,string passWord)
{
string message = "";//提¬¨¢示º?信?息¡é
try
{
GetConnection();
cmd = new SqlCommand("proc_tranMoney", conn);
//设¦¨¨置?命¨¹令¢?的Ì?类¤¨¤型¨ª为a存ä?储ä¡é过y程¨¬
cmd.CommandType = CommandType.StoredProcedure;
//设¦¨¨置?参?数ºy
cmd.Parameters.Add("@fromCard", SqlDbType.VarChar,19).Value = fromCardId;
//注Á¡é意°a输º?出?参?数ºy要°a设¦¨¨置?大䨮小?,否¤?则¨°size默?认¨?为a0,
cmd.Parameters.Add("@toCard", SqlDbType.VarChar, 19).Value = toCardId;
cmd.Parameters.Add("@money", SqlDbType.Money).Value = money;
cmd.Parameters.Add("@pass", SqlDbType.VarChar, 6).Value = passWord;
cmd.Parameters.Add("@Tips", SqlDbType.NVarChar, 50);
//设¦¨¨置?参?数ºy的Ì?类¤¨¤型¨ª为a输º?出?参?数ºy,默?认¨?情¨¦况?下?是º?输º?入¨?,
cmd.Parameters["@Tips"].Direction = ParameterDirection.Output;
//执¡ä行D
cmd.ExecuteNonQuery();
//得Ì?到Ì?输º?出?参?数ºy的Ì?值¦Ì,把ã?赋3值¦Ì给?name,注Á¡é意°a,这a里¤?得Ì?到Ì?的Ì?是º?object类¤¨¤型¨ª的Ì?,要°a进?行D相¨¤应®|的Ì?类¤¨¤型¨ª轮?换?
message = cmd.Parameters["@Tips"].Value.ToString();
Con_Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return message;
}
#endregion
(11)WhithDraw()(取款)
#region 取¨?款?
/// <summary>
/// 取¨?款?
/// </summary>
/// <param name="cardId">卡¡§号?</param>
/// <param name="balance">金e额?</param>
/// <param name="passWord">密¨¹码?</param>
/// <returns>返¤¦Ì回?查¨¦询¡¥的Ì?表À¨ª</returns>
public DataTable WithDraw(string cardId, string balance,string passWord)
{
//查¨¦询¡¥的Ì?Sql语®?句?
string sql = "EXEC proc_takeMoney '" + cardId + "'," + balance + ",'支¡ì出?','" + passWord + "'";
sql += "Select * from cardInfo where cardID = '" + cardId + "'";
var getds = GetDataSet(sql); //将?查¨¦询¡¥结¨¢果?存ä?入¨?DataSet对?象¨®中D
var dt = getds.Tables[0]; //将?查¨¦询¡¥结¨¢果?存ä?入¨?DataTable对?象¨®中D
return dt;
}
#endregion
(12)Deposit(存款)
#region 存ä?款?
/// <summary>
/// 存ä?款?
/// </summary>
/// <param name="cardId">卡¡§号?</param>
/// <param name="balance">金e额?</param>
/// <returns>返¤¦Ì回?查¨¦询¡¥表À¨ª</returns>
public DataTable Deposit(string cardId, string balance)
{
//Sql查¨¦询¡¥语®?句?
string sql = "Update cardInfo set balance = balance +" + balance + " where cardID = '" + cardId + "'";
sql += " Insert Into transInfo(transType,cardID,transMoney) values ('存ä?入¨?','" + cardId + "'," + balance + ")";
sql += " Select * from cardInfo where cardID = '" + cardId + "'";
var getds = GetDataSet(sql);
var dt = getds.Tables[0];
return dt;
}
#endregion
(13)Query(查询)
#region 查¨¦询¡¥
/// <summary>
/// 查¨¦询¡¥
/// </summary>
/// <param name="tableType">查¨¦询¡¥那?个?表À¨ª</param>
/// <param name="cardID">卡¡§号?</param>
/// <returns>查¨¦询¡¥结¨¢果?</returns>
public DataTable QueryData(string tableType, string cardID)
{
string sql;
if (tableType == "cardInfo")//查¨¦询¡¥卡¡§信?息¡é表À¨ª
{
sql = "Select customerID 客¨ª户¡ì编À¨¤号?,balance 账?户¡ì余®¨¤额?,cardType 币À¨°种?,savingType 存ä?款?类¤¨¤型¨ª,openDate 开a户¡ì日¨?期¨²"
+ " from " + tableType + " where cardID = '" + cardID + "'";
}
else //查¨¦询¡¥transInfo (转Áa账?及¡ã余®¨¤额?表À¨ª)ê?
{
sql = "Select transID 交?易°¡Á明¡Â细?编À¨¤号?,transDate 交?易°¡Á日¨?期¨²,transType 交?易°¡Á类¤¨¤型¨ª,transMoney 交?易°¡Á金e额?"
+ " from " + tableType + " where cardID = '" + cardID + "' Order by transID";
}
var connect = new ConnectDB();
var dt = connect.GetDataSet(sql).Tables[0];
return dt;
}
#endregion
(14)ModifyPwd(修改密码)
#region 修T改?密¨¹码?
/// <summary>
/// 修T改?密¨¹码?
/// </summary>
/// <param name="cardId">卡¡§号?</param>
/// <param name="newPwd">新?密¨¹码?</param>
/// <returns>返¤¦Ì回?查¨¦询¡¥设¦¨¨定¡§新?密¨¹码?的Ì?结¨¢果?</returns>
public DataTable ModifyPwd(string cardId, string newPwd)
{
string sql = "Update cardInfo set password = '" + newPwd + "' where cardID = '" + cardId + "'";
sql += " Select * from cardInfo where cardID = '" + cardId + "' and password = '" + newPwd + "'";
var getds = GetDataSet(sql);
var dt = getds.Tables[0];
return dt;
}
#endregion
(15)Proc_CreateAccount(开户)
#region 开a户¡ì
public string Proc_CreateAccount(string customerName, string customerPid,
string telephone, string openMoney, string savingType,
string address)
{
string message = "";//提¬¨¢示º?信?息¡é
try
{
GetConnection();
cmd = new SqlCommand("proc_createAccount", conn);
//设¦¨¨置?命¨¹令¢?的Ì?类¤¨¤型¨ª为a存ä?储ä¡é过y程¨¬
cmd.CommandType = CommandType.StoredProcedure;
//设¦¨¨置?参?数ºy
cmd.Parameters.Add("@customerName", SqlDbType.VarChar, 10).Value = customerName;
//注Á¡é意°a输º?出?参?数ºy要°a设¦¨¨置?大䨮小?,否¤?则¨°size默?认¨?为a0,
cmd.Parameters.Add("@customerPID", SqlDbType.VarChar, 18).Value = customerPid;
cmd.Parameters.Add("@telephone", SqlDbType.VarChar,13).Value = telephone;
cmd.Parameters.Add("@openMoney", SqlDbType.Money).Value = openMoney;
cmd.Parameters.Add("@savingType", SqlDbType.VarChar, 8).Value = savingType;
cmd.Parameters.Add("@address", SqlDbType.VarChar, 50).Value = address;
cmd.Parameters.Add("@Tips", SqlDbType.NVarChar, 500);
//设¦¨¨置?参?数ºy的Ì?类¤¨¤型¨ª为a输º?出?参?数ºy,默?认¨?情¨¦况?下?是º?输º?入¨?,
cmd.Parameters["@Tips"].Direction = ParameterDirection.Output;
//执¡ä行D
cmd.ExecuteNonQuery();
//得Ì?到Ì?输º?出?参?数ºy的Ì?值¦Ì,把ã?赋3值¦Ì给?name,注Á¡é意°a,这a里¤?得Ì?到Ì?的Ì?是º?object类¤¨¤型¨ª的Ì?,要°a进?行D相¨¤应®|的Ì?类¤¨¤型¨ª轮?换?
message = cmd.Parameters["@Tips"].Value.ToString();
Con_Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return message;
}
#endregion
3.数据库设计:
(1) 表设计
A.userInfo(用户信息表)
B.cardInfo(卡信息表)
C.transInfo(交易信息表)
D.manager(管理员信息表)
(2) 增加约束条件
A.为UserInfo表增加约束
--为UserInfo表增加约束
ALTER TABLE UserInfo
--为UserInfo表customerID列增加主键约束
ADD constraint PK_ID PRIMARY KEY(customerID),
--为customerPID列增加检查约束,用到系统函数len()
constraint CK_PID CHECK(len(customerPID) = 15 or len(customerPID) = 18),
--确保身份证列数据唯一
constraint UQ_PID UNIQUE(customerPID),
--约束电话号码为-2530102或
constraint CK_TELEPHONE CHECK(telephone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or telephone like '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
GO
B.为cardInfo表增加约束
--为cardInfo表增加约束
ALTER TABLE cardInfo
ADD CONSTRAINT PK_CARDID PRIMARY KEY(cardID),
CONSTRAINT CK_CARDID CHECK(cardID like '1234[0-9][0-9][0-9][0-9]'),
--增加默认约束,默认值为“RMB”
CONSTRAINT DF_CARDTYPE DEFAULT('RMB') FOR cardType,
--增加检查约束,用IN限制可以输入的数据
CONSTRAINT CK_SAVINGTYPE CHECK(savingType IN ('活期','定活两便','定期')),
--调用日期函数获得当前时间设为默认值
CONSTRAINT DF_OPENTYPE DEFAULT(GETDATE()) FOR openDate,
CONSTRAINT CK_OPENMONEY CHECK(openMoney>1),
CONSTRAINT CK_BALANCE CHECK(balance>1),
CONSTRAINT CK_PASS CHECK(passWord like '[0-9][0-9][0-9][0-9][0-9][0-9]'),
CONSTRAINT DF_PASS DEFAULT('888888') FOR password,
CONSTRAINT DF_LOSS DEFAULT(0) FOR isLossbit,
CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES UserInfo(customerID)
GO
C.为表transInfo表增加约束
--为表transInfo表增加约束
ALTER TABLE transInfo
ADD CONSTRAINT PK_ID PRIMARY KEY(transID),
--增加检查约束,设置该列的值只能是“存入","支出”
CONSTRAINT CK_TRANSTYPE CHECK(transType IN ('存入','支出')),
CONSTRAINT CK_TRANSMONEY CHECK(transMoney>1),
CONSTRAINT DF_TRANSDATE DEFAULT(GETDATE()) FOR transDate,
--增加外键约束,transInfo表的cardID受cardID的cardID列约束
CONSTRAINT FK_CARDID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID)
GO
(3)创建存储过程
(proc_createAccount)开户存储过程
--系统对象中存在此存储过程名则删除
if exists(SELECT * FROM sysobjects WHERE name = 'proc_createAccount')
drop PROC proc_createAccount
GO
--创建卡户存储过程
CREATE PROC proc_createAccount @customerName char(10),@customerPID char(18),@telephone varchar(13)
,@openMoney money,@savingType char(8),@address varchar(50) = ''
,@Tips varchar(500)='' output
AS
--声明局部变量
DECLARE @mycardID nvarchar(19),@cur_customerID int
--调用产生随机卡号的存储过程,生成随机卡号
EXEC dbo.proc_randCardNo @mycardID output
--产生的卡号已存在,就一直产生,知道没有重复
while exists(SELECT * FROM cardInfo WHERE cardID = @mycardID)
EXEC proc_randCardNo @mycardID output
SET @Tips = '尊敬的用户,您开户成功!系统为您产生的随机卡号为:'+@mycardID
+ '开户日期:'+CONVERT(char(10),getdate(),111)
+ '开户金额:'+CONVERT(varchar(20),@openMoney)
PRINT @Tips
IF not exists(SELECT * FROM UserInfo WHERE customerPID = @customerPID)
BEGIN
--插入客户信息
INSERT INTO UserInfo(customerName,customerPID,telephone,address)
VALUES(@customerName,@customerPID,@telephone,@address)
--查询出最后一次插入的自增列值
SELECT @cur_customerID = @@IDENTITY FROM UserInfo
--插入卡信息
INSERT INTO cardInfo(cardID,savingType,openMoney,balance,customerID)
VALUES(@mycardID,@savingType,@openMoney,@openMoney,@cur_customerID)
--插入明细账
INSERT INTO transInfo(transType,cardID,transMoney)
VALUES('存入',@mycardID,@openMoney)
END
GO
(proc_takeMoney)存取款存储过程
if exists(select * from sysobjects where name = 'proc_takemoney')
drop proc proc_takemoney
go
create procedure proc_takeMoney(@card char(19),@money money,@type char(4),@passWord char(6)='')
AS
print '交易正在进行,请稍后...'
if((select isLoss From carcInfo where cardID = @card) = 1) --检测卡是否被锁定
begin
raiserror('卡已被锁定,请联系工作人员',16,1)
return
end
if(@type = '支出')
if((select passWord from cardID where cardID = @card) <> @passWord) --检测密码是否正确
begin
raiserror('密码错误后卡已被锁定!',16,1)
return
end
DECLARE @balance money
select @balance = balance From cardID where cardID = @card
if(@type = '支出')
if(@balance > @money+1) --判断余额是否足够
update cardInfo set balance = balance - @money where cardID = @card
else
begin
raiserror('余额不足!不能交易!',16,1)
print '卡号'+@card+' 余额:'+convert(varchar(20),@balance)
return
end
else
update cardInfo set balance = balance +@money where cardID = @card
INSERT INTO transInfo(transType,cardID,transMoney)
VALUES(@type,@card,@money) --插入交易明细
print '交易成功! 交易金额:'+convert(varchar(20),@money)
select @balance = balance from cardInfo where cardID = @card
print '卡号'+@card+' 的余额是:'+convert(varchar(20),@balance)
GO
(proc_randCardNo)随机产生卡号存储过程
--系统对象中存在此存储过程名则删除
if exists(SELECT * FROM sysobjects WHERE name = 'proc_randCardNo')
drop PROC proc_randCardNo
GO
--创建产生卡号的存储过程
CREATE PROC proc_randCardNo(@randCardID nvarchar(19) output)
AS
DECLARE @str char(10)
DECLARE @r numeric(10,8)
--在单个查询中反复调用RAND()函数将产生相同的值,所以使用rand([seed])时,每次要修改种子seed的值
--才能得出不一样的随机数
--这里采用毫秒做种子来取随机数
SELECT @r = RAND(DATEPART(ms,GETDATE()))
SELECT @str = CAST(@r as CHAR(10)) --转化为char型
--截取.XXXXXXXX的小数点后-4为组成为规定格式的卡号
SET @randCardID = '1234'+SUBSTRING(@str,3,4)
GO
(proc_tranMoney)转账存储过程
--系统对象中存在此存储过程名则删除
if exists(SELECT * FROM sysobjects WHERE name = 'proc_createAccount')
drop PROC proc_createAccount
GO
--创建卡户存储过程
CREATE PROC proc_createAccount @customerName char(10),@customerPID char(18),@telephone varchar(13)
,@openMoney money,@savingType char(8),@address varchar(50) = ''
,@Tips varchar(500)='' output
AS
--声明局部变量
DECLARE @mycardID nvarchar(19),@cur_customerID int
--调用产生随机卡号的存储过程,生成随机卡号
EXEC dbo.proc_randCardNo @mycardID output
--产生的卡号已存在,就一直产生,知道没有重复
while exists(SELECT * FROM cardInfo WHERE cardID = @mycardID)
EXEC proc_randCardNo @mycardID output
SET @Tips = '尊敬的用户,您开户成功!系统为您产生的随机卡号为:'+@mycardID
+ '开户日期:'+CONVERT(char(10),getdate(),111)
+ '开户金额:'+CONVERT(varchar(20),@openMoney)
PRINT @Tips
IF not exists(SELECT * FROM UserInfo WHERE customerPID = @customerPID)
BEGIN
--插入客户信息
INSERT INTO UserInfo(customerName,customerPID,telephone,address)
VALUES(@customerName,@customerPID,@telephone,@address)
--查询出最后一次插入的自增列值
SELECT @cur_customerID = @@IDENTITY FROM UserInfo
--插入卡信息
INSERT INTO cardInfo(cardID,savingType,openMoney,balance,customerID)
VALUES(@mycardID,@savingType,@openMoney,@openMoney,@cur_customerID)
--插入明细账
INSERT INTO transInfo(transType,cardID,transMoney)
VALUES('存入',@mycardID,@openMoney)
END
GO
四. 测试与实现:
五. 总结:
通过对基于COM组件技术的练习,是我对COM组件的通用性有了进一步的了解,这样的技术的使用为代码更高级别的重用提供了良好的契机。同时,我也掌握了用.NET下C#语言创建组件及ADO.NET技术连接并操作数据库的技术,是我受益匪浅
本程序的优点:实现了ATM的基本功能,界面友好,用户输入限制较完善。
本程序的缺点:对数据库的操作使用了sa登录,安全性有待提高,其他功能不完善等。