ADO.NET

1.T-SQL练习
（1）
--> 创建一个School数据库
---> 创建一个班级表：
--班级id、班级名称、班级简介
---> 创建一个学生表：
--学生id、姓名、性别、年龄、电话、
--家庭住址、出生日期、身份证号、班级id
(

if(DB_ID('School'))is not null
drop database School; --查看有没有此数据库,有则删除掉
create database School
on
(
name='School',
filename='F:\DBA\mytest\School.mdf'
)
log on
(
name='School_log',
filename='F:\DBA\mytest\School.ldf'
) --创建School数据库
--go
use School;
--go
create table T_class--创建class表
(
ClassID int,
ClassName nvarchar(10),
ClassDesciption nvarchar(50)
)
create table T_Student --创建student表
(
StuID varchar(10),
StuName nvarchar(10),
StuGender bit,
StuAge int,
StuPhone varchar(11),
StuDress nvarchar(50),
StuBorthday datetime,
StuCardID varchar(18),
ClassID int
)
--go
insert into dbo.T_Class(ClassID,ClassName,ClassDesciption) --添加数据
values(1,'DOTNET基础班','DOTNET基础班学习基础');
insert into dbo.T_Class(ClassID,ClassName,ClassDesciption)
values(2,'JAVA基础班','JAVA基础班学习基础');
insert into dbo.T_Class(ClassID,ClassName,ClassDesciption)
values(3,'JAVA基础班','JAVA基础班学习基础');
insert into dbo.T_Class(ClassID,ClassName,ClassDesciption)
values(3,'JAVA基础班','JAVA基础班学习基础');

select * from dbo.T_Class; --查询表
select ClassName from dbo.T_Class; --查询指定字段
select * from dbo.T_Class where ClassID=1; --查询语句

update dbo.T_Class set ClassName='JAVA就业班' ,
ClassDesciption='JAVA就业班毕业就能就业' where ClassID=3;
select * from dbo.T_Class; --更改表的信息

update dbo.T_Class set ClassID=4 where ClassID=3 and ClassName='JAVA基础班';

Delete from dbo.T_Class where ClassID=4; --删除信息

)
（2）
--创建学生成绩表Score
--scoreId,studentId,english,math
--创建老师表Teacher
--tId,tName,tSex,tAge,tSalary,tBirthday
--学生表Student
(

studentId name age

if(DB_ID('Score')) is not null
drop database Score;

create database Score
on
(
name ='Score',
filename ='F:\DBA\Score\Score.mdf'
)
log on
(
name='Score_log',
filename='F:\DBA\Score\Score.1df'
)

use Score;
create table T_Score
(
ScoreID int,
StudentID int,
english int,
Math int
)

create table T_Teacher
(
TId int,
TName nvarchar(10),
TSex bit,
TAge int,
TSalary decimal,
TBirthday datetime
)

create table T_Student
(
StudentID int,
name nvarchar(10),
age int
)

insert into T_Score(ScoreID,StudentID,english,Math) values (1,1,79,85);
insert into T_Score(ScoreID,StudentID,english,Math) values (2,2,73,80);
insert into T_Score(ScoreID,StudentID,english,Math) values (3,3,58,59);

update T_Score set english=98,Math=99 where ScoreID=3;

delete from T_Score where ScoreID=2;
select * from dbo.T_Score;

)
3.
--创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。
--要求：输出所有数据中通话时间最长的5条记录。orderby datediff
--输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum
--输出本月通话总时长最多的前三个呼叫员的编号。
--输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)

(

CREATE TABLE [CallRecords]
(
[Id] [int] NOT NULL identity(1,1),
[CallerNumber] [nvarchar](50), --三位数字，呼叫中心员工编号（工号）
[TelNum] [varchar](50),
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL --结束时间要大于开始时间,默认当前时间
)

--主键约束
alter table [CallRecords]
add constraint PK_CallRecords primary key(id)

--检查约束
alter table [CallRecords]
add constraint CK_CallRecords check(CallerNumber like '[0-9][0-9][0-9]')

alter table [CallRecords]
add constraint CK_CallRecords_EndDateTime check(EndDateTime > StartDateTime)

--默认约束
alter table [CallRecords]
add constraint DF_CallRecords default(getdate()) for EndDateTime

 

INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '0208888888', CAST(0x00009DAF00A4CB80 AS DateTime), CAST(0x00009DAF00A62E94 AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '0208888888', CAST(0x00009DB000D63BC0 AS DateTime), CAST(0x00009DB000D68DC8 AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '89898989', CAST(0x00009DB000E85C60 AS DateTime), CAST(0x00009DB000E92F50 AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('002', '98987676', CAST(0x00009DB2015BB7A0 AS DateTime), CAST(0x00009DB2015C4DA0 AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('002', '02188839389', CAST(0x00009DA4014C9C70 AS DateTime), CAST(0x00009DA4014E0308 AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '767676766', CAST(0x00009DB400DAA0C0 AS DateTime), CAST(0x00009DB400DD5FE0 AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('003', '0227864656', CAST(0x00009DB200B9AB40 AS DateTime), CAST(0x00009DB200B9FC1C AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('003', '676765777', CAST(0x00009DB8014042B8 AS DateTime), CAST(0x00009DB80141804C AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '89977653', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('004', '400400400', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));

use MyDataBase
select * from dbo.CallRecords
--要求：输出所有数据中通话时间最长的5条记录。orderby datediff
select TOP 5
*
from dbo.CallRecords
order by
datediff(SECOND,StartDateTime,EndDateTime) desc;

--输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum

select
SUM(datediff(SECOND,StartDateTime,EndDateTime)) as 长途总时长
from dbo.CallRecords
where
TelNum like '0%'

--输出本月通话总时长最多的前三个呼叫员的编号。
select TOP 3
CallerNumber as 呼叫员,
SUM(datediff(SECOND,StartDateTime,EndDateTime)) as 通话总时间
from dbo.CallRecords
group by
CallerNumber
order by
SUM(datediff(SECOND,StartDateTime,EndDateTime)) desc ;

--输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)
select TOP 3
CallerNumber as 呼叫员,
COUNT(CallerNumber) as 通话次数
from dbo.CallRecords
group by
CallerNumber
order by
COUNT(CallerNumber) desc;

)
4.产生随机姓名
namespace 产生随机姓名
(
{
class Program
{
static void Main(string[] args)
{
List<string> list = new List<string>();
string familyname = File.ReadAllText(@"百家姓.txt", Encoding.Default);
MatchCollection ms = Regex.Matches(familyname, @"〔(\w+)〕");
foreach (Match m in ms)
{
list.Add(m.Groups[1].Value);
//Console.WriteLine(m.Groups[1].Value);
}
List<string> list1 = new List<string>();
string girlname = File.ReadAllText(@"好听的女孩的名字.txt", Encoding.Default);
string[] strs = girlname.Split(new char[] { '、', '。' }, StringSplitOptions.RemoveEmptyEntries);
list1.AddRange(strs);

List<string> list2 = new List<string>();
string boyname = File.ReadAllText(@"好听的男孩名字.txt", Encoding.UTF8);
string[] strboy = boyname.Split(new char[] { '\r', '\n', '。' }, StringSplitOptions.RemoveEmptyEntries);
list2.AddRange(strboy);

Random r = new Random();
StringBuilder sb = new StringBuilder();
List<string> boylist = new List<string>();
List<string> girllist = new List<string>();
for (int i = 0; i < 100; i++)
{
string s1 = list[r.Next(1, list.Count)];
string s2 = list1[r.Next(1, list1.Count)];
string s3 = list2[r.Next(1, list2.Count)];
if (r.Next(1, 100) % 2 == 0)
{
sb.AppendFormat("{0}{1}", s1, s3);
boylist.Add(sb.ToString());
sb.Clear();
}
else
{
sb.AppendFormat("{0}{1}", s1, s2);
girllist.Add(sb.ToString());
sb.Clear();
}
}

using (SqlConnection conn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=randomname;Integrated Security=True"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
foreach (var item in boylist)
{

int age = r.Next(18, 35);
cmd.CommandText = @"insert into T_Boy(BoyName,BoyAge) values('" + item + "','" + age + "')";
cmd.ExecuteNonQuery();
}
foreach (var item in girllist)
{
int age = r.Next(18, 35);
cmd.CommandText = @"insert into T_Girl(GirlName,GirlAge) values('" + item + "','" + age + "')";
cmd.ExecuteNonQuery();
}
}
}

//Console.ReadKey();
}
}
}
) 5.创建表同时添加约束
(

create database D_0710
on
(
name='D_0710',
filename='F:\DBA\0710\D_0710.mdf'
)
log on
(
name='D_0710_log',
filename='F:\DBA\0710\D_0710.ldf'
)
use D_0710;
create table T_Classroom
(
RoomID int primary key,
RoomName nvarchar(10)
)

create table T_Student
(
StuID int primary key,
StuNum varchar(10) unique,
Stuname nvarchar(10),
Stuphone varchar(11) check(Len(Stuphone)=11),
StuEnterdate datetime default(getdate()),
RoomID int foreign key references T_Classroom(RoomID)
)

create table T_Teacher
(
TeaID int primary key,
TeaNum varchar(10) unique,
TeaName nvarchar(10),
TeaPhone varchar(11) check(Len(TeaPhone)=11),
TeaEnterDate datetime default(getdate()),
RoomID int foreign key references T_Classroom(RoomID)
)

insert into T_Classroom(RoomID,RoomName) values(1,'.NET基础班');
insert into T_Classroom(RoomID,RoomName) values(2,'.NET就业班');
insert into T_Classroom(RoomID,RoomName) values(3,'JAVA基础班');
insert into T_Classroom(RoomID,RoomName) values(4,'JAVA就业班');

select * from dbo.T_Classroom;



insert into T_Student(StuID,StuNum,Stuname,Stuphone,StuEnterdate,RoomID)
values(1,'061901','董超','12345678900','2012-06-19',1);

insert into T_Student(StuID,StuNum,Stuname,Stuphone,RoomID)
values(2,'061902','庞永帅','12345678901',2);

insert into T_Student(StuID,StuNum,Stuname,Stuphone,RoomID)
values(3,'061903','翟冲','12345678902',4);

insert into T_Student(StuID,StuNum,Stuname,Stuphone,RoomID)
values(4,'061904','赵希平','12345678903',4);

select * from dbo.T_Student;

insert into dbo.T_Teacher(TeaID,TeaNum,TeaName,TeaPhone,TeaEnterDate,RoomID)
values(1,'001','蒋坤','12345678900','2012-01-01',3);


insert into dbo.T_Teacher(TeaID,TeaNum,TeaName,TeaPhone,RoomID)
values(2,'002','苏坤','12345678901',1);


insert into dbo.T_Teacher(TeaID,TeaNum,TeaName,TeaPhone,RoomID)
values(3,'003','赵小虎','12345678902',4);

insert into dbo.T_Teacher(TeaID,TeaNum,TeaName,TeaPhone,RoomID)
values(4,'004','牛亮亮','12345678903',1);

select * from dbo.T_Teacher;
)

6SQLHelper
(
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Data;

namespace SQLHelper
{
public class Class1
{
static string strcoon = System.Configuration.ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
-- #region 封装ExcuteNonQuery
public static int ExcuteNonQurey(string commandText, params SqlParameter[] para)
{
using (SqlConnection conn = new SqlConnection(strcoon))
{
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
if (para != null && para.Length != 0)
{
cmd.Parameters.AddRange(para);
}
return cmd.ExecuteNonQuery();
}
}
}
#endregion

-- #region 二次封装ExcuteNonQuery
public static int ExcuteNonQurey(string commandText, params object[] objs)
{
MatchCollection ms = Regex.Matches(commandText, @"@\w+");
List<SqlParameter> list = new List<SqlParameter>();
if (ms.Count != objs.Length)
{
throw new Exception();
}
for (int i = 0; i < ms.Count; i++)
{
list.Add(new SqlParameter(ms[i].Value, objs[i]));
}
return ExcuteNonQurey(commandText, list.ToArray());
}
#endregion

-- #region 封装ExcuteScalar
public static object ExcuteScalar(string commandText, params SqlParameter[] para)
{
using (SqlConnection conn = new SqlConnection(strcoon))
{
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
if (para != null && para.Length != 0)
{
cmd.Parameters.AddRange(para);
}
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
return cmd.ExecuteScalar();
}
}
}
#endregion

-- #region 二次封装ExcuteScalar
public static object ExcuteScalar(string commandText, params object[] objs)
{
MatchCollection ms = Regex.Matches(commandText, @"@\w+");
List<SqlParameter> list = new List<SqlParameter>();
if (ms.Count != objs.Length)
{
throw new Exception();
}
for (int i = 0; i < ms.Count; i++)
{
list.Add(new SqlParameter(ms[i].Value, objs[i]));
}
return ExcuteScalar(commandText, list.ToArray());
}
#endregion

-- #region 封装ExcuteReader
public static SqlDataReader ExcuteReader(string commandText, params SqlParameter[] para)
{
SqlConnection conn = new SqlConnection(strcoon);
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
if (para != null && para.Length != 0)
{
cmd.Parameters.AddRange(para);
}
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
}
#endregion

-- #region 二次封装ExcuteReader
public static SqlDataReader ExcuteReader(string commandText, params object[] objs)
{
MatchCollection ms = Regex.Matches(commandText, @"@\w+");
List<SqlParameter> list = new List<SqlParameter>();
if (ms.Count != objs.Length)
{
throw new Exception();
}
for (int i = 0; i < ms.Count; i++)
{
list.Add(new SqlParameter(ms[i].Value, objs[i]));
}
return ExcuteReader(commandText, list.ToArray());
}
#endregion

-- #region 封装DataAdapter
public static DataSet DataAdapter(string commandText, params SqlParameter[] para)
{
DataSet ds = new DataSet();
using (SqlDataAdapter sda = new SqlDataAdapter(commandText, strcoon))
{
if (para != null && para.Length != 0)
{
sda.SelectCommand.Parameters.AddRange(para);
}
sda.Fill(ds);
return ds;
}
}
#endregion

-- #region 二次封装DataAdapter
public static DataSet DataAdapter(string commandText, params object[] objs)
{
MatchCollection ms = Regex.Matches(commandText, @"@\w+");
List<SqlParameter> list = new List<SqlParameter>();
if (ms.Count != objs.Length)
{
throw new Exception();
}
for (int i = 0; i < ms.Count; i++)
{
list.Add(new SqlParameter(ms[i].Value, objs[i]));
}
return DataAdapter(commandText, list.ToArray());
}
#endregion

}
}
)
7.省市联动
--首先添加SQL类型，写应用程序配置文件，并分别引用
( --Model类
namespace 省市联动专属Helper版
{
class model
{
public int ID
{
get;
set;
}
public string Name
{
get;
set;
}
-- //重写ToString
public override string ToString()
{
return Name;
}
}
}

--专属Helper
static class PCHelper
{
public static List<model> PCExcute(string commandText, params object[] objs)
{
List<model> list = new List<model>();
using (SqlDataReader reader = SQLHelper.Class1.ExcuteReader(commandText, objs))
{
if (reader.HasRows)
{
while (reader.Read())
{
list.Add(
new model {
ID=Convert.ToInt32(reader["AreaId"]),
Name=reader["AreaName"].ToString()
}
);
}
}
}
return list;
}
}
--窗口
namespace 省市联动专属Helper版
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
string str = @"select AreaId,AreaName from TblArea where AreaPId=@p";
cmdpro.Items.AddRange(PCHelper.PCExcute(str,0).ToArray());
}

private void cmdpro_SelectedIndexChanged(object sender, EventArgs e)
{
cmbcity.Items.Clear();
cmbcity.Text = string.Empty;
string str = @"select AreaId,AreaName from TblArea where AreaPId=@pid";
int id = ((model)(cmdpro.SelectedItem)).ID;
cmbcity.Items.AddRange(PCHelper.PCExcute(str, id).ToArray());
}
}
}
)

8.登录注册
---首先添加SQL类型，写应用程序配置文件，并分别引用
--主窗口
(
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace 用户登录
{
public partial class MainForm : Form
{
public static string strLogName = string.Empty;
public MainForm()
{
InitializeComponent();
}
private void btnlogin_Click(object sender, EventArgs e)
{
Form1 f1 = new Form1();
this.Hide();
f1.Show();
}
private void btnenter_Click(object sender, EventArgs e)
{
//判断文本框不为空
if (txtid.Text == "" || txtpwd.Text == "")
{
MessageBox.Show("用户名或者密码不能为空");
return;
}
string myname = txtid.Text.Trim();
string sqlstr = "select count(userName) from T_userinformation where userName=@name";
int res = Convert.ToInt32(SQLHelper.Class1.ExcuteScalar(sqlstr, myname));
//查询用户名是否存在
if (res > 0)
{
Found:
string str2 = "select time from T_userinformation where username=@name";
bool res2 =Convert.IsDBNull(SQLHelper.Class1.ExcuteScalar(str2, myname));
//查询数据库设定的time是否为空
if (res2)
{
string str = "select userPWD from T_userinformation where username=@name";
string pwd = Convert.ToString(SQLHelper.Class1.ExcuteScalar(str, myname));
//查询输入的密码是否正确
if (pwd == txtpwd.Text.Trim())
{
MessageBox.Show("登录成功");
strLogName = txtid.Text.Trim();
messageform mform = new messageform();
this.Hide();
mform.Show();
}
else
{
string str1 = "select seed from T_userinformation where username=@name";
int myseed = Convert.ToInt32(SQLHelper.Class1.ExcuteScalar(str1, myname));
//设定种子，小于3则给与提示，并自增1
if (myseed < 3)
{
myseed++;
string str4 = "update T_userinformation set seed='" + myseed + "' where username=@name";
SQLHelper.Class1.ExcuteNonQurey(str4, myname);
MessageBox.Show(string.Format("密码错误，你还有{0}次登录机会", 4 - myseed));
}
//大于3则锁定账户，往数据库插入当前时间
else
{
btnenter.Enabled = false;
string str3 = "update T_userinformation set time=GETDATE() where username=@name";
SQLHelper.Class1.ExcuteNonQurey(str3, myname);
MessageBox.Show("你的用户名已被锁定，15分钟后可以登录");
}
}
}
else
{
//用当前时间减去数据库里的时间，大于15分钟则设数据库里的时间为NULL并返回，小于15分钟
//则给予提示
string str4 = "select time from T_userinformation where username=@name";
DateTime savetime =Convert.ToDateTime(SQLHelper.Class1.ExcuteScalar(str4, myname));
string str1 = @"select datediff(SECOND,'"+savetime+"',getdate())";
int time =Convert.ToInt32(SQLHelper.Class1.ExcuteScalar(str1,null));
if (time < 900)
{
MessageBox.Show(string.Format("还有{0}分钟可以登录", time / 60 + 1));
}
else
{
string str5 = "update T_userinformation set seed=1,time=null where username=@name";
SQLHelper.Class1.ExcuteNonQurey(str5, myname);
goto Found;
}
}
}
else
{
MessageBox.Show("用户名不存在");
}
}
private void btnalter_Click(object sender, EventArgs e)
{
alterpwd ap = new alterpwd();
this.Hide();
ap.Show();
}
}
}
--修改密码窗口
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;

namespace 用户登录
{
public partial class alterpwd : Form
{
public alterpwd()
{
InitializeComponent();
}
bool b1, b2, b3;
private void textBox1_Leave(object sender, EventArgs e)
{
b1 = false;
if (string.IsNullOrEmpty(txtuid.Text.Trim()))
{
return;
}
string sqlstr1 = "select count(userName) from T_userinformation where userName=@name";
int res = Convert.ToInt32(SQLHelper.Class1.ExcuteScalar(sqlstr1, txtuid.Text.Trim()));
if (res > 0)
{
lbuid.ForeColor = Color.Green;
lbuid.Text = "用户名正确";
b1 = true;
}
else
{
lbuid.ForeColor = Color.Red;
lbuid.Text = "用户名不存在";
}
}
private void txtnewpwd_Leave(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtnewpwd.Text.Trim()))
{
return;
}
b2 = Regex.IsMatch(txtnewpwd.Text.Trim(), @"^\d{6}$");
if (b2)
{
lbpwd.ForeColor = Color.Green;
lbpwd.Text = "恭喜，密码输入正确！";
}
else
{
lbpwd.ForeColor = Color.Red;
lbpwd.Text = "输入错误，请检查密码长度和格式";
}
}

private void txtnewpwd2_Leave(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtnewpwd2.Text.Trim()))
{
return;
}
b3 = txtnewpwd2.Text.Trim() == txtnewpwd.Text.Trim();
if (b3)
{
lbpwd2.ForeColor = Color.Green;
lbpwd2.Text = "恭喜，密码验证正确！";
}
else
{
lbpwd2.ForeColor = Color.Red;
lbpwd2.Text = "密码不一致";
}
}

private void btnok_Click(object sender, EventArgs e)
{
string str = "select userPWD from T_userinformation where username=@name";
string pwd = Convert.ToString(SQLHelper.Class1.ExcuteScalar(str, txtuid.Text.Trim()));
if (b1 && b2 && b3)
{
if (pwd == txtoldpwd.Text.Trim())
{
string str2 = "update T_userinformation set userPWD=@pwd where username=@name";
SQLHelper.Class1.ExcuteNonQurey(str2, txtnewpwd2.Text.Trim(), txtuid.Text.Trim());
MessageBox.Show("修改成功");
}
else
{
MessageBox.Show("原始密码错误");
}
}
else
{
MessageBox.Show("请填写正确的信息");
}
}

private void button1_Click(object sender, EventArgs e)
{
MainForm mf = new MainForm();
this.Hide();
mf.Show();
}
}
}

--注册窗口
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.Data.SqlClient;

namespace 用户登录
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
bool b1, b2, b3, b4, b5, b6;
private void button1_Click(object sender, EventArgs e)
{
try
{
if (b1 && b2 && b3 && b4 && b5 && b6)
{
string str = @"insert into T_userinformation
(userName, userPWD, userPhone, userEmial, userBirthday)
values
(@t1,@t2,@t3,@t4,@t5)";
SQLHelper.Class1.ExcuteNonQurey(str,txtuid.Text,txtpwd.Text,txtphone.Text,txtmail.Text,Convert.ToDateTime(txtbirth.Text));
MessageBox.Show("注册成功");
MainForm mf = new MainForm();
this.Hide();
mf.Show();
}
else
{
MessageBox.Show("请完善信息");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

private void textBox1_Leave(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtuid.Text.Trim()))
{
return;
}
b1 = Regex.IsMatch(txtuid.Text.Trim(), @"^\w{0,20}$");
string str = "select count(userName) from T_userinformation where userName=@n";
int res = Convert.ToInt32(SQLHelper.Class1.ExcuteScalar(str, txtuid.Text.Trim()));
if (b1)
{
if (res == 0)
{
labelUid.ForeColor = Color.Green;
labelUid.Text = "恭喜，用户名可以使用！";
}
else
{
labelUid.ForeColor = Color.Red;
labelUid.Text = "用户名已存在";
}
}
else
{
labelUid.ForeColor = Color.Red;
labelUid.Text = "用户名长度超过20";
}
}

private void textBox3_Leave(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtpwd.Text.Trim()))
{
return;
}
b2 = Regex.IsMatch(txtpwd.Text.Trim(), @"^\d{6}$");
if (b2)
{
labelpas.ForeColor = Color.Green;
labelpas.Text = "恭喜，密码输入正确！";
}
else
{
labelpas.ForeColor = Color.Red;
labelpas.Text = "输入错误，请检查密码长度和格式";
}
}

private void textBox2_Leave(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtpwd2.Text.Trim()))
{
return;
}
b3 = txtpwd2.Text.Trim() == txtpwd.Text.Trim();
if (b3)
{
labelpas2.ForeColor = Color.Green;
labelpas2.Text = "恭喜，密码验证正确！";
}
else
{
labelpas2.ForeColor = Color.Red;
labelpas2.Text = "输入错误，请检查密码";
}
}

private void textBox4_Leave(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtmail.Text.Trim()))
{
return;
}
b4 = Regex.IsMatch(txtmail.Text.Trim(), @"^[0-9a-zA-Z\.-_]+@[0-9a-zA-Z\-_]+(\.[0-9a-zA-Z\-_]+)+$");
if (b4)
{
labelmail.ForeColor = Color.Green;
labelmail.Text = "恭喜，邮箱输入正确！";
}
else
{
labelmail.ForeColor = Color.Red;
labelmail.Text = "输入错误，请检查邮箱格式";
}
}

private void textBox5_Leave(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtphone.Text.Trim()))
{
return;
}
b5 = Regex.IsMatch(txtphone.Text.Trim(), @"^\d{11}$");
if (b5)
{
labelphone.ForeColor = Color.Green;
labelphone.Text = "恭喜，手机输入正确";
}
else
{
labelphone.ForeColor = Color.Red;
labelphone.Text = "输入错误，请检查手机格式";
}
}

private void textBox6_Leave(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtbirth.Text.Trim()))
{
return;
}
b6 = Regex.IsMatch(txtbirth.Text.Trim(), @"^\d{4}\-((0[1-9])|(1[0-2]))-((0[1-9])|(1[0-9])|(2[0-9])|(3[0-1]))$");
if (b6)
{
labelbirth.ForeColor = Color.Green;
labelbirth.Text = "恭喜，出生日期输入正确";
}
else
{
labelbirth.ForeColor = Color.Red;
labelbirth.Text = "输入错误，请检查出生日期格式";
}
}

private void Form1_Load(object sender, EventArgs e)
{

}

private void btnreturn_Click(object sender, EventArgs e)
{
MainForm mf = new MainForm();
this.Hide();
mf.Show();
}
}
}

--信息显示窗口
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 用户登录
{
public partial class messageform : Form
{
public messageform()
{
InitializeComponent();
}

private void messageform_Load(object sender, EventArgs e)
{
string uid = MainForm.strLogName;
string str = "select * from T_userinformation where username=@name";
SqlDataReader reader = SQLHelper.Class1.ExcuteReader(str, uid);
if (reader.HasRows)
{
while (reader.Read())
{
label5.Text = reader["userName"].ToString();
label6.Text = reader["userPhone"].ToString();
label7.Text = reader["userEmial"].ToString();
label8.Text = reader["userBirthday"].ToString();
}
}
}
}
}
)
9.case函数练习题 (
<1>--显示分数等级A(100-90)B(89-80)C(79-70)D(69-60)E
select top 50 * from Score
select top 50
scId,
case
when scBaseDotNet>=90 then 'A'
when scBaseDotNet>=80 then 'B'
when scBaseDotNet>=70 then 'C'
when scBaseDotNet>=60 then 'D'
else 'E'
end AS Net基础,
scDataBase,
case
when scDataBase>=90 then 'A'
when scDataBase>=80 then 'B'
when scDataBase>=70 then 'C'
when scDataBase>=60 then 'D'
else 'E'
end AS 数据库,
scJavaScript
from
Score
<2>----面试题
单号 金额
Rk1 10
Rk2 20
Rk3 -30
Rk4 -10 将上面的表输出为如下的格式：
单号 收入 支出
Rk1 10 0
Rk2 20 0
Rk3 0 30
Rk4 0 10

create table test
(
number varchar(10),
amount int
)
insert into test(number,amount) values('RK1',10)
insert into test(number,amount) values('RK2',20)
insert into test(number,amount) values('RK3',-30)
insert into test(number,amount) values('RK4',-10)

select * from test

select number as 单号,
case
when amount>0 then amount
else 0
end as 收入,
case
when amount<0 then -amount
else 0
end as 支出
from test
<3>--有如下一张表，使用SQL实现如表2的数据集
学号 课程 成绩
1 0001 语文 87
2 0001 数学 79
3 0001 英语 95
4 0002 语文 69
5 0002 数学 84
表2
学号 数学 语文 英语
1 0001 79 87 95
2 0002 84 69 0
--create table TScore
(
学号 nvarchar(10),
课程 nvarchar(10),
成绩 int
)

insert into TScore values('0001','语文',87);
insert into TScore values('0001','数学',79);
insert into TScore values('0001','英语',95);
insert into TScore values('0002','语文',69);
insert into TScore values('0002','数学',84);

select * from TScore

--要使用学生进行分组
select 学号 from TScore group by 学号

--前面不能显示各个科目
--可以考虑使用聚合函数
select 学号,
SUM(case when 课程='数学' then 成绩 else 0 end) as 数学,
SUM(case when 课程='语文' then 成绩 else 0 end) as 语文,
SUM(case when 课程='英语' then 成绩 else 0 end) as 英语
from TScore group by 学号

--知道，这种将行变列的用法叫做透视pivot

)
10.实现日记和分页
(
--视图 --表的内连接
SELECT t2.UserName, t1.Title, t1.CreateTime,t2.ID
FROM dbo.DiaryInfo AS t1 INNER JOIN
dbo.Users AS t2 ON t1.UserID = t2.ID
--存储过程
create proc usp_Getpaged --时间分页
@pageindex int,
@pagesize int,
@pagecount int output
as
begin
declare @num int
select @num=COUNT(*) from dbo.vw_myvw
set @pagecount=CEILING(@num*1.0/@pagesize)
select * from
(select ROW_NUMBER() over(order by ID) as num,* from dbo.vw_myvw)as t
where num between (@pageindex-1)*@pagesize+1 and @pageindex*@pagesize
order by t.ID
end
--查询 declare @n int
exec dbo.usp_Getpaged 1,3,@n output
print @n

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
---登录主页面
namespace 日记
{
public partial class LoginForm : Form
{
public LoginForm()
{
InitializeComponent();
}

private void btnenter_Click(object sender, EventArgs e)
{
if(string.IsNullOrEmpty(txtuid.Text.Trim())||string.IsNullOrEmpty(txtpwd.Text.Trim()))
{
return;
}
string str = @"select count(*) from Users where UserName=@n";
int res =Convert.ToInt32(SQLHelper.Class1.ExcuteScalar(str, txtuid.Text.Trim()));
if (res > 0)
{
string str2 = @"select Pwd from Users where UserName=@n";
string mypwd = Convert.ToString(SQLHelper.Class1.ExcuteScalar(str2, txtuid.Text.Trim()));
if (mypwd == txtpwd.Text.Trim())
{
ShowtxtForm sf = new ShowtxtForm();
sf.Show();
this.Hide();
}
else
{
MessageBox.Show("密码错误");
return;
}
}
else
{
MessageBox.Show("用户名不存在");
return;
}
}

private void btnlogin_Click(object sender, EventArgs e)
{
try
{
string str = @"insert into Users(UserName,Pwd) values(@n,@p)";
SQLHelper.Class1.ExcuteNonQurey(str, txtuid.Text.Trim(), txtpwd.Text.Trim());
MessageBox.Show("注册成功");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
--显示日记的页面
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 日记
{
public partial class ShowtxtForm : Form
{
public ShowtxtForm()
{
InitializeComponent();
}
int pageIndex = 1;
int pageSize = 3;
int pageCount;
private void button1_Click(object sender, EventArgs e)
{
AddtxtForm af = new AddtxtForm();
this.Close();
af.Show();
}

private void ShowtxtForm_Load(object sender, EventArgs e)
{
BindDGV();
}
void BindDGV()
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(@"Data Source=(local);Initial Catalog=DiaryManager;Integrated Security=True"))
{
using (SqlCommand cmd = new SqlCommand("usp_Getpaged", conn)) //存储过程名字
{
cmd.CommandType = CommandType.StoredProcedure; //设置类型为存储过程类型，默认是Text

SqlParameter[] para =
{
new SqlParameter("@pageindex",SqlDbType.Int),
new SqlParameter("@pagesize",SqlDbType.Int),
new SqlParameter("@pagecount",SqlDbType.Int) //不忙给赋值，先定义类型
};
//输入参数
para[0].Value = pageIndex;
para[1].Value = pageSize;
//输出参数
para[2].Direction = ParameterDirection.Output;
//添加参数
cmd.Parameters.AddRange(para);
//执行存储过程
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
//必须调用完存储过程 才能获取输出参数的值
pageCount = Convert.ToInt32(para[2].Value);
//设置dgv不自动生成列
dataGridView1.AutoGenerateColumns = false;
//设置dgv的数据源
dataGridView1.DataSource = dt;
}
}
btnfirst.Enabled = true;
btnpre.Enabled = true;
btnnext.Enabled = true;
btnlast.Enabled = true;
if (pageIndex == 1)
{
btnfirst.Enabled = false;
btnpre.Enabled = false;
}
if (pageIndex == pageCount)
{
btnnext.Enabled = false;
btnlast.Enabled = false;
}
}
//首页
private void btnfirst_Click(object sender, EventArgs e)
{
pageIndex = 1;
BindDGV();
}
//上一页
private void btnpre_Click(object sender, EventArgs e)
{
if (pageIndex > 1)
{
pageIndex--;
BindDGV();
}
}
//下一页
private void btnnext_Click(object sender, EventArgs e)
{
if (pageIndex < pageCount)
{
pageIndex++;
BindDGV();
}
}
//末页
private void btnlast_Click(object sender, EventArgs e)
{
pageIndex = pageCount;
BindDGV();
}
}
}
--添加日记的页面
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 日记
{
public partial class AddtxtForm : Form
{
public AddtxtForm()
{
InitializeComponent();
}

private void AddtxtForm_Load(object sender, EventArgs e)
{
string str = @"select ID,UserName from Users";
List<model> list = new List<model>();
SqlDataReader reader = SQLHelper.Class1.ExcuteReader(str, null);
using (reader)
{
if (reader.HasRows)
{
while (reader.Read())
{
list.Add(
new model
{
ID=Convert.ToInt32(reader["ID"]),
Name=(reader["UserName"]).ToString()
}
);
}
}
}
cmbauther.Items.AddRange(list.ToArray());
}

private void btnok_Click(object sender, EventArgs e)
{
try
{
string str1 = @"insert into DiaryInfo(Title,Content,CreateTime,UserID)
values(@t,@c,getdate(),@id)";
SQLHelper.Class1.ExcuteNonQurey(str1, txttitle.Text.Trim(), txtboby.Text.Trim(), ((model)cmbauther.SelectedItem).ID);
MessageBox.Show("添加成功");
ShowtxtForm sf = new ShowtxtForm();
this.Close();
sf.Show();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
class model
{
public int ID
{
get;
set;
}
public string Name
{
get;
set;
}
public override string ToString()
{
return Name;
}
}
}
) 11.