asp.net中ADO SQL数据库笔记汇总持续更新中

ADO连接数据库
1)获取连接字符串
方式一: 记住连接字符串
connectionString=" Integrated Security=True; server=. ; database=DBName"
方式二:在visual studio中,点击”视图”à服务资源管理器à右击左侧的”数据连接”,选择”添加连接”à服务名: 为一个点.选择数据库名,然后点击”高级”,然后复制底部的连接字符串
2)在web.config中配置连接字符串

复制代码代码如下:

3)在DAL层新建SqlConnection类,包含静态方法:
记得先添加Configuration引用和 using System.Configuration;命名空间

复制代码代码如下:

public static string getConnectionStr()
{
return ConfigurationManager.ConnectionStrings["SQLconnectionStr"].ToString();
}

4)在DAL层其他类中调用getConnectionStr()静态方法

复制代码代码如下:

string conStr= SqlConnection.getConnectionStr();

DAL层执行SQL语句的方法

ADO操作SQL语句:方式一

复制代码代码如下:

public List<student> getData1(string myid, string myname)
{
//这里用using灵活方便,用完con不需手动关闭,会自动关闭当前连接
using(SqlConnection con=new SqlConnection(conStr) )
{
//打开连接
con.Open();
string cmdStr = "select * from ns_user where userID=@myid and userName=@myname";
SqlCommand cmd = new SqlCommand(cmdStr,con);
//这里用参数序列化防止注入式攻击
cmd.Parameters.Add(new SqlParameter("@myid", myid));
cmd.Parameters.Add(new SqlParameter("@myname", myname));
//执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行
//object myResult = cmd.ExecuteScalar();
// 对连接执行 Transact-SQL 语句并返回受影响的行数。(负责执行语句, 例如增insert,删delete,改update)
//int ResultRowCount = cmd.ExecuteNonQuery();
SqlDataReader sdr = cmd.ExecuteReader();
List<student> ls = new List<student>();
while (sdr.Read())
{
student s = new student();
s.Sid = sdr["sid"].ToString();
s.Sname = sdr["sname"].ToString();
ls.Add(s);
}
return ls;
}
}
Dataset(记得)
#region 获取教师的详细信息
public DataSet GetTeacherInfo()
{
using (SqlConnection conn = new SqlConnection(dbapp))
{
SqlDataAdapter sda = new SqlDataAdapter("select * from table1 ", conn);
DataSet ds = new DataSet(); //定义一个表的集合
sda.Fill(ds, "teacher");
return ds;
}

}
#endregion

ADO操作存储过程:

复制代码代码如下:

#region
public int UserCheck(string userID,string userName)
{
using(SqlConnection con=new SqlConnection (conStr))
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "sp_MYLogin"; //sp_MYLogin是存储过程名称
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;

//为存储过程赋值
//赋值方式一(赋第一个值括号里面的 "@name"必须和存储过程里的"@name"一模一样)
cmd.Parameters.Add(new SqlParameter("@userID", userID));

//赋值方式二(赋第二个值)
SqlParameter pwd = new SqlParameter("@pwd", SqlDbType.NVarChar, 50);
pwd.Value = userName;
pwd.Direction = ParameterDirection.Input;
cmd.Parameters.Add(pwd);
//定义一个变量来接受存储过程的返回值
SqlParameter result = new SqlParameter("@return", SqlDbType.Int);
result.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(result);

cmd.ExecuteNonQuery(); //执行存储过程

//取得SQL变量@result中存放的存储过程的返回值
int num = Convert.ToInt32(cmd.Parameters["@return"].Value); //Parameters是一个集合["@return"]就是他的索引
return num;
}
}
#endregion

错误日志

复制代码代码如下:

catch (Exception ex)
{ //错误日志
error.ErrorWrite("UserInsert", ex.Message, DateTime.Now.ToString());
return null;
}

数据库技术
建修库表
建库语句

复制代码代码如下:

create database student --创建的数据库名称
on primary ---指定数据文件的各个参数
(
name='studnet3_data', --所有的字符串都以' 间隔
filename='E:\lx\student4_data.mdf', --文件名包含路径和名字.扩展名
size=3MB, ----默认大小，如果不写大小，则默认是MB
maxsize=100MB, ----最大容量
filegrowth=1MB ---自动增长量/扩容,如果为，则不自动扩容
)

log on -----日志文件的各个参数
(
name='student5_log',
filename='E:\lx\student4_data.ldf',
size=1MB,
maxsize=10MB,
filegrowth=10% --10%是最大容量的%)
)

sp_helpdb student ---查询数据库名称
sp_renamedb student,stu --重命名数据库
drop database student --删除数据库

建表语句

复制代码代码如下:

drop table person --删除表
create table person --创建表
(
---注意：下面是属性(字段名称)在前，数据类型在后
ID int primary key identity(2,1) not null, -- primary key是设置主键，保证该列值唯一且不为空，identity(2,1)起始值是，步长为
Name nvarchar(10) not null, ---not null是指不能为空
Sex bit not null, --bit是bool类型
age int default 18 , -- default 18是指自动取默认值
scroe decimal(4,1) check(score<=100) --4指小数点前后加起来总共位，代表小数点后位数 check是检查限制约束
cardid int unique --unique 指唯一键，在表中有多列数据需要保证唯一时除了主键以外的列需要设置为唯一列
)

对表的操作
修改表结构,添加删除约束

alter table person --修改表结构
--add NameID int --添加属性\字段NameID列,添加列
--drop column NameID --删除列

--alter column ID int not null ---添加字段不为空
--add constraint 约束名(pk_表名_列名| pk_列名)
--add constraint pk_ID primary key(ID) --修改表时添加主键约束
--add constraint ck_score check(score<150) --修改表时添加检查约束
--add constraint uk_cardi unique(cardid) --修改表时添加唯一键约束
--add constraint df_age default 19 for age --修改表时添加默认约束
--drop constraint CK__person__score__15502E78 --删除约束（格式:drop constraint 约束名）
修改表信息,增(insert) 删(delete) 改(update) 查(select)
--添加记录<insert 表名values(参数)> <1>字符串或日期类型加'' <2>bit用或 <2>自动增长列不需要添加值
insert person values(12,'wang',0,23,32,34)
insert person(sex,age,cardid) values(0,23,32) --有选择性的插入数据（(sex,age,cardid) ）指要手动添加的数据

--修改记录 <update 表名set 修改对象where 条件> <1>修改多个字段，中间以逗号间隔
update person set age=19,sex=0 where ID=1
update person set age=19,age=12 where ID=2 ---<1>
update person set age=19+1 where ID=2---修改信息时进行算术运算
update person set age=19+1 --如果不写where，则对所有数据进行修改

update person set age=SUBSTRING(age,1,1) where ID=1 --substring 是字符的截取

--删除记录< delete 表名where 条件> (如果不加where则删除所有记录)
delete person where ID=1

对表的查询
单表查询

复制代码代码如下:

select * from tableName --查找显示整个表(所有列)
select列名1,列名2 from tableName --显示部分列
select列名1='编号',列名2='品牌' from Product1 --把其中一列的信息统一修改

--修改显示时列标题(方法一)
select '编号'=ProCode,'品牌'=ProTypeName from Product1
--修改显示时列标题(方法二)
Select 列名1 '编号',列名2 '品牌' from Product1

--显示时对列进行算数运算, 显示时添加列
select列名1,列名2,列名3*0.5 '打折后',Number from Product1

select distinct 列名1 from tableName --显示时删除该列的重复行
select top 3 * from Product1 --显示前三列
select top 50 percent * from Product1 --显示总行数的前%行
--and是并且, or是或者 , 条件非: not 列名='值'
select * from tableName where Number=15 and not age=12 and Name='aa' or sex=1
--查找score范围为0到100的数据
select * from Product1 where score<100 and score >=1
select * from Product1 where score between 1 and 100
--in，not in (包含不包含) 以下都是查找number为，，的数据
select * from Product1 where Number=10 or Number=15 or Number=20
select * from Product1 where Number in(10,15,20)
select * from Product1 where not Number in(10,15,20)
--<1>like模式匹配符%可以代替任意个数量的字符<2> _可以代替一个字符 <3>[]是一个查找范围
select * from Product1 where ProCode like 'D%' --查找首字母为D的ProCode的数据
select * from Product1 where ProCode like '_S%' --查找第二个字符是S的procode数据
select * from Product1 where 列名1 like '1%' --即使是float型也要加''
select * from Product1 where 列名1 like '_4_' --查找第二个字符是且字符个数为的3数据
select * from Product1 where 列名1 like '_[5-9]%' --查找第二个字符是5到9的列名1数据
--查找为空,不为空的数据
select *from Product1 where proValue is not null
select *from Product1 where proValue is null
go --排序( desc降序 asc升序什么都不写就默认升序 )
select * from Product1 order by proValue desc --将provalue按降序排列
select * from Product1 order by proValue asc --将provalue按升序排列
select * from Product1 order by Number --将Number按默认(升序)排列
select * from Product1 order by Number desc,proValue asc --第二个条件在第一个条件相同时才执行
go --聚合函数
select MAX(proValue) from Product1 --查找proValue中的最大值
select min(proValue) from Product1 --查找proValue中的最小值
select sum(proValue) from Product1 --查找proValue中数据的和
select avg(proValue) from Product1 --查找proValue中的平均值
select count(*) from Product1 --查找表中的行数*也可以用列名代替

--group by分组（where在group by 之前，having分组之后的筛选条件，where和having都是筛选条件）
--分组:可以显示分组列的信息和分组后的信息分别进行统计
select列名1,max(列名2),min(列名3) from tableName where proTypeName='电视机' group by 列名1
select proTypeName,max(proValue),min(proValue) from Product1 group by proTypeName having count(proValue)>1

多表查询

复制代码代码如下:

--内连接inner join 查询两表共有的信息
--from查询列名1.Name=列名2.Name 是关联条件(关联条件中列的内容要一致)
select * from tableName inner join Product2 on列名1.Name=列名2.Name
--显示查询后的部分列，p1.*意思是显示p1的所有列
select p1.*,proArea,proBrand from Product1 as p1 inner join Product2 on p1.ProCode=Product2.ProCode
--Product1 as p1 意思是给Product1起了个别名p1 ，as 可以省略
select * from Product1 as p1 inner join Product2 as p2 on p1.ProCode=p2.ProCode
--where查询，省略了as <格式:select * from 表,表where 关联条件>
select * from Product1 p1,Product2 p2 where p1.ProCode=p2.ProCode

--外连接 --先显示两表关联到的数据，再显示关联不到的数据
go --<格式:select * from 表left\right\full outer join 表on 关联条件>
select * from Product1 p1 left outer join Product2 p2 on p1.ProCode=p2.ProCode --左外连接
select * from Product1 p1 right outer join Product2 p2 on p1.ProCode=p2.ProCode --右外连接
select * from Product1 p1 full outer join Product2 p2 on p1.ProCode=p2.ProCode --全外连接

--交叉连接(又叫笛卡尔连接:疯狂连接，n对n连接,没有关联条件)
--格式:select * from 表cross join 表
select * from Product1 cross join Product2
--自连接(自查询:是把一张表分解成两张表来用)
select c1.*,c2.Name from ST_class c1,ST_class c2 where c1.ID=c2.Department and c1.Name='计算机学院'
--嵌套查询
--子查询返回多个值
select * from product1 where ProCode in(select ProCode from Product2 where proArea='北京')
select * from product1 where ProCode not in(select ProCode from Product2 where proArea='北京')
--子查询返回一个值
select* from Product1 where proValue=(select MAX(provalue) from Product1)

--子查询返回多个值时可以用any（返回结果中的任何一个【最小的一个】）all（返回结果中的所有【最大值】）

--联合查询 union all (用union all连接两个单独的子查询)
select SNAME,SSEX,SBIRTHDAY from STUDENT union all select TNAME,TSEX,TBIRTHDAY from TEACHER

存储过程

复制代码代码如下:

--创建(create)/修改(alter)一个存储过程
alter proc sp_SMGetCity ---sp_SMGetCity 是存储过程的名字
(
@code nvarchar(50), --数据类型要和比较的字符相同
@name nvarchar(50) output ,
@grade int=1
--'这里要注意:存储过程赋初值,只能在排在最后的参量中'
---一个@ 符号是局部变量
---两个@ 符号是全局变量
)
as
begin
select @name=Name from TBName where Code like @code+'%' and Grade=@grade --begin和end 中间是要执行的SQL语句
print @name --带输出参数
end

declare @outname nvarchar(50) -- 定义一个变量
exec sp_SMGetCity'11',@outname output --@aa把变量赋值给输出参数，用来接收返回值
select @outname

sp_help sp_SMGetCity --查看名为sp_studentmanager的存储过程的创建信息
sp_helptext sp_SMGetCity --查看名为sp_studentmanager的存储过程的创建代码
drop proc sp_SMGetCity --删除存储过程

--return只能返回整型数据
--删除存储过程drop proc sp_Name
-- exec(@aa)--执行@aa(SQL语句)，所以要加括号,执行字符串中的SQL语句

存储过程调用存储过程
as
begin
declare @return int
exec @return=sp_checkUser@id,@name --存储过程调用存储过程
if @return=0
print '没有重复,return只能返回整型'
else
print '用户已注册'
end

一些例子
数据库联查授权

复制代码代码如下:

alter view vw_Role
as
declare @num int
declare @title nvarchar(100)
declare @ctitle nvarchar(200)
set @ctitle=''
select @num=count(*)from dbo.Operate
while(@num>0)
begin
select @title=name from (select row_number() over(order by id) 'newid' ,name from dbo.Operate )ta where newid =@num
if(@num>1)
set @ctitle+=@title+','
else
set @ctitle+=@title
set @num=@num-1
end
Declare @sql varchar(8000)
set @sql='select * from(select 1 "isture",rolename,modulename,operatename,role_ID,Module_id from vw_userrole group by rolename,modulename,operatename,role_ID,Module_id)a pivot(count(isture)for operatename in('+@ctitle+')) b'
exec(@sql)

分页器存储过程,分页存储过程

复制代码代码如下:

alter proc cutpage
(
@tablename nvarchar(100),----分页的表
@columnname nvarchar(100), ----分页的列
@ordertype nvarchar(100)='asc', ----排序方式
@pageindex int =1,
@pagecount int =1
)
as
begin
declare @aa nvarchar(max);
set @aa= 'select * from
(select *,ROW_NUMBER() over(order by '+@columnname+' '+@ordertype+') as uprow from '+@tablename+' ) as newtable
where uprow between '+cast((@pageindex-1)*@pagecount+1 as nvarchar(100))+' and '+convert(nvarchar(100), @pageindex*@pagecount)
exec (@aa) --这里@aa必须加括号（）

end

exec cutpage 'SM_Class', 'classid'

事务 Transaction

复制代码代码如下:

---事务关键语句讲解----
BEGIN TRANSACTION ---事务关键字transaction

DECLARE @errorSum INT
SET @errorSum=0 --初始化为，即无错误

update bank SET money=money+1000 where name='张三'
SET @errorSum=@errorSum+@@error
update bank SET money=money-1000 where name='李四'
SET @errorSum=@errorSum+@@error --累计是否有错误(@@error是非零)

if @errorSum<>0
begin
print '不成功，有错误,错误代号是：'
print @errorsum
rollback transaction
end
else
begin
print '成功'
select * from Bank
commit TRANSACTION
end

触发器 Trigger

复制代码代码如下:

--概念：一种特殊的存储过程,将本表中的数据更改(增删改)，就会自动执行实现定义的语句
--特点：跨越相关表的级联修改
--关键字：trigger

alter trigger trigger_name_f
on SM_Class
for update --(for是在增删改之前执行after在增删改之后执行，instead of 是所有【增|删|改】都不执行,之前触发，但不改值)
as
begin
if update(remark) ---判断SM_Class表中remark列是否发生数据变化
begin
select * from inserted ---存放修改的新值的表
select * from deleted ----存放修改的旧值的表
print 'remark列发生更改'
end
else
print '其他列发生变化'
print '测试触发器，当修改表SM_Class时，显示这句话,且for时这句话在前'
end

游标 Cursor

复制代码代码如下:

--游标类似于sql dateReader 一行一行的读取
--一般是在万不得已的情况下使用, 时间长,服务器压力大,吃更多的内存,宽带,
--游标是对一个集合的遍历读取

declare cur_test cursor
for
select pid,pname from ns_product

open cur_test
declare @id uniqueidentifier;
declare @name nvarchar(50);
--读取一行(第一次读取当然是第一行了)
fetch next from cur_test into @id,@name

--判断一下是否读到数据了,状态为零说明读到数据了(@@FETCH_STATUS=0)
while @@FETCH_STATUS=0
begin
print @id
print @name
print '----------------------'
--然后读取下一行
fetch next from cur_test into @id,@name
end
close cur_test
deallocate cur_test

零散知识点
1)截取字符串: substring(字段名, 起始位置(首位为1),截取的长度 )
select SUBSTRING(age,1,1) from person where ID=2
2)关于GUID:
select NEWID()
insert person values('','',NEWID())
3)将一张表(person2)插入到另一张表(person1)中( 列数要对应)
insert person1
select列,列,列 from person2
4)条件语句 (注意: C#中的大括号{}在sql中用begin end 代替 )

复制代码代码如下:

declare @x int ,@y int
set @x=1
set @y =2
if @x>@y
print 'x>y'
else
print 'x<y'

select code,name,grade,
case Grade
when '1' then '省'
when '2' then '市'
when '3' then '县'
end '等级'
from SM_PostCode

-------------------------------------------------------------
while (select MAX(DEGREE) from SCORE)<85
begin
if (select MAX(DEGREE) from SCORE where CNO='3-105')>=100
break
end

5)判断是否存在if exists( select * from TBName where CityName='22')

6) 添加自动增长列row_number over(order by **) ROW_NUMBER是关键字 over指根据哪一列排序
select ROW_NUMBER() over(order by classID) ,* from SM_Class
select rank() over(order by classID) ,* from SM_Class ---也是自动增长的行号，如果出现重复会并列行号，下一个值会自动加二

--一条查询语句返回的结果作为另外一条查询语句的数据源表
select * from ( select ROW_NUMBER() over(order by 列名1)此处新列名, * from TBName) 此处新表名where 新列名between 1 and 3
7)临时表
declare @table table(id uniqueidentifier,name varchar(50))
--执行插入操作(插入条数据),并将这条数据的hid字段返回并插入到@table表id属性中
insert images(Hname,Himage) output inserted.Hid into @table(id) values(@hname,@Himage)
declare @picid uniqueidentifier
select @picid=id from @table

------------------------------------------------------------

--下面是执行效率的对比
--sql 语句一：执行需要s
declare @tempTable table(id varchar(20),name int,score datetime)
insert @tempTable(id,name,score)
select userID,userName,userScore from scoreTable
select * from @tempTable

--sql 语句二：执行只需要s
DROP TABLE #Bicycles
SELECT userID,userName,userScore
INTO #Bicycles
from scoreTable
select * from #Bicycles

8)关于日期时间的操作
--数据库中获取北京时间和国际时间
select getdate(), getutcdate()
--时间的增加（增加的类型[年/月/日]，增量，给谁加[当前时间/日期]）dateAdd
select dateadd(YEAR,2,GETDATE()) ----将当前的年份加上两年

--时间的减法DateDiff
select DATEDIFF(HOUR,getdate(),getutcdate()) --国际时间的小时减去当前北京时间的小时（后边减前边）

--获取时间中的年份, 月份, 天等同理
select year(getdate())-year(birthday())
select year(getdate())-year('1988-10-07')
9)行列转换
select * from ( select * from TableName) a pivot(count(stuName)) for columnName in('aa','bb','cc','dd')
10) 双引号只能用于表名和列名(不加双引号也可以)
set @aa='select ClassName "sd" from SM_Class' --注意：'' 里面原来的'sd' 现在要写成"sd"
exec (@aa)

-----------------这里要多加注意------------------------------
declare @bb nvarchar(max);
--在使用数据值时只能用''电商二班''
set @bb ='select * from SM_Class where ClassName=''电商二班''' --注意：原来的'电商二班'要写成''电商二班''
exec (@bb)

11) --快速创建表结构
select c.Cid,c.Ccount into newTB1 from ns_comment c where 1<>1
12) --重点再记一下
declare @na nvarchar(10),@str nvarchar(max);
set @str=' select top 1 @bb=ClassID from SM_Class '
--@str包含SQL语句的变量，对@str中的变量进行定义N标明是字符串，用来接收@str中变量的变量(即@na=@bb)
exec sp_executesql@str, N'@bb nvarchar(10) output',@na output
select @na,@str
13) -------------并发问题--------
--概念：多个用户同时和一个对象(库,表)交互
--出现问题：脏数据,非重复读取,丢失更新,幻影读取）
--解决：SQL Server使用锁来确保事务完整性（共享锁,排它锁,更新锁,意向锁,架构锁,批量更新锁）
14)