asp.net中ADO SQL資料庫 筆記匯總 持續更新中

ADO串連資料庫
1)擷取連接字串
方式一: 記住連接字串
connectionString=" Integrated Security=True; server=. ; database=DBName"
方式二:在visual studio中,點擊”視圖”à服務資源管理員à右擊左側的”資料連線”,選擇”添加串連”à服務名: 為一個點.選擇資料庫名,然後點擊”進階”,然後複製底部的連接字串
2)在web.config中配置連接字串 複製代碼 代碼如下:<connectionStrings>
<addname="SQLconnectionStr"connectionString="Data Source=.;Initial Catalog=NetShopDB;Integrated Security=True"providerName="System.Data.SqlClient"/>
</connectionStrings>

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)