Question

在C#中，编写一个数据查询程序，在数据库中查询相应地信息并显示。如学生成绩查询

Answer 1

set nocount on
--创建文件夹
exec xp_cmdshell 'md D:\bank',no_output
--建库
use master
if exists(select * from sysdatabases where name='bankDB')
drop database bankDB
go
create database bankDB
on
(
name='bankDB',
filename='D:\bank\bankDB.mdf'
)
go
use bankDB
go
--建立用户信息表
create table userInfo
(
customerID int identity(1,1),--顾客编号
customerName varchar(10) not null,--开户名
PID char(19) not null,--身份证
telephone char(13) not null,--联系电话
address text --居住地址
)
--用户表约束
alter table userInfo
add constraint PK_customerID primary key(customerID)
,constraint CK_PID check(len(PID)=18 or len(PID)=15)
,constraint UQ_PID unique(PID)
,constraint CK_telephone check(telephone like '____-________' or len(telephone)=11)--建立银行卡信息表
create table cardInfo
(
cardID char(19) not null,--卡号
curType char(4) not null,--货币种类
savingType char(10) not null,--存款类型
openDate datetime not null,--开户日期
openMoney money not null,--开户金额
balance money not null,--余额
pass char(6) not null,--密码
IsReportLoss char(2) not null,--是否挂失
customerID int not null --顾客编号
)
--银行表约束
alter table cardInfo
add constraint PK_cardID primary key(cardID)
,constraint CK_cardID check(len(cardID)=19)
,constraint DF_curType default('RMB') for curType
,constraint CK_savingType check(savingType in('活期','定活两便','定期'))
,constraint DF_openDate default(getdate()) for openDate
,constraint CK_openMoney check(openMoney>=1)
,constraint CK_balance check(balance>=1)
,constraint DF_pass default('888888') for pass
,constraint CK_IsReportLoss check(IsReportLoss in ('是','否'))
,constraint DF_IsReportLoss default ('否') for IsReportLoss
,constraint FK_cardInfo_userInfo foreign key(customerID) references userInfo(customerID)--建立交易信息表
create table transInfo
(
transDate datetime not null,--交易时间
cardID char(19) not null,--卡号
transType char(4) not null,--交易类型
transMoney money not null,--交易金额
remark text --备注
)
--交易表约束
alter table transInfo
add constraint DF_transDate default(getdate()) for transDate
,constraint FK_transInfo_cardInfo foreign key(cardID) references cardInfo(cardID)
,constraint CK_transType check(transType in ('存入','取出'))
,constraint CK_transMoney check(transMoney>0)--插入测试数据
--插入测试数据
delete from cardInfodelete from userInfo
declare @customerID int
insert into userInfo values('张三',123456789012345,'0010-67898978','北京海淀')
select @customerID=max(customerID) from userInfo
insert into cardInfo (cardID,savingType,openMoney,balance,customerID)
values('1010 3576 1234 5678','活期',1000,1000,@customerID)insert into userInfo (customerName,PID,telephone)
values('李四',321245678912345678,'0478-44443333')
select @customerID=max(customerID) from userInfo
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values('1010 3576 1212 1134','定期',1,1,@customerID)select * from userInfo
select * from cardInfo--张三取900，李四存5000
insert into transInfo(transType,cardID,transMoney) values('取出','1010 3576 1234 5678',900)
update cardInfo set balance = balance-900 where cardID='1010 3576 1234 5678' insert into transInfo(transType,cardID,transMoney) values('存入','1010 3576 1212 1134',5000)
update cardInfo set balance = balance+5000 where cardID='1010 3576 1212 1134'--7常规业务模拟
--1.修改密码
update cardInfo set pass=123456 where cardID='1010 3576 1234 5678'
update cardInfo set pass=123123 where cardID='1010 3576 1212 1134'
--2.李四申请挂失
update cardInfo set IsReportLoss='是' where cardID='1010 3576 1212 1134'
select * from cardInfo
--3.统计银行的资金流通余额和盈利结算
declare @inMoney money,@outMoney money
select @inMoney=sum(transMoney) from transInfo where transType ='存入'
select @outMoney=sum(transMoney) from transInfo where transType='取出'
print '银行流通余额总计为：'+convert(varchar(10),(@inMoney-@outMoney))+'RMB'
print '盈利结算为：'+convert(varchar(10),(@outMoney*0.008-@inMoney*0.003))
--4.查询本周开户的卡号，显示该卡相关信息。
SELECT * FROM cardInfo WHERE (DATEDIFF(Day,getDate(),openDate)<DATEPART(weekday,openDate))
/*---------查询本月交易金额最高的卡号----------------------*/
SELECT * FROM transInfo
SELECT DISTINCT cardID FROM transInfo WHERE transMoney=(SELECT Max(transMoney) FROM transInfo)
/*---------查询挂失帐号的客户信息---------------------*/
SELECT customerName as 客户姓名,telephone as 联系电话 FROM userInfo
WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss='是')
/*------催款提醒：例如某种业务的需要，每个月末，如果发现用户帐上余额少于200元，将致电催款。---*/
SELECT customerName as 客户姓名,telephone as 联系电话,balance as 帐上余额
FROM userInfo INNER JOIN cardInfo ON userInfo.customerID=cardInfo.customerID WHERE balance<200

Answer 2

首先在c#语言中要链接sql数据库要用到ado.net中的一些数据对象，下面我简单的举个例子在sql数据库中检索某个表中的一些字段：注意：这些代码是在load事件中写的，为了以后方便可以单独创建个类文件，方便调用。首先导入个命名空间：using System.data.sqlclient;然后在load事件中写代码：string sql="select 字段名 from 表名 where 条件"；这是创建sql命令sqlconnection conn=new sqlconnection("server=local; initial catalog=数据库的名字;integrated security=true ");conn.open();sqlcommand cmd=new sqlcommand(sql,conn);com.ExecuteNonQuery();conn.close();这是最简单的连接sql数据库查询表中某个字段

Answer 3

楼上哪本书上的例子吧，其实楼主这个就是个select查询，看看书就明白了，这东西不需要搞什么程序吧