存儲過程在應(yīng)用程序端的使用的優(yōu)點(diǎn)

????????1 如果sql語句直接寫在客戶端，以一個字符串的形式體現(xiàn)的，提示不友好，會導(dǎo)致效率降低
????????2 sql語句寫在客戶端，可以利用sql注入進(jìn)行攻擊，為了安全性，可以把sql封裝在服務(wù)器存儲過程，在客戶端進(jìn)行

SQL自定義無參數(shù)存儲過程：

創(chuàng)建存儲過程 usp_ScoreQuery1
-- 查詢考試成績，顯示：學(xué)號、姓名、班級、總成績，并按成績的總分高低排序。
-- 統(tǒng)計分析考試成績，顯示班級名稱、C#平均分、數(shù)據(jù)庫平均分，按照班級分組實(shí)現(xiàn)。

 use SMDBgoif exists (select * from sysobjects where name = 'usp_ScoreQuery1')drop procedure usp_ScoreQuery1gocreate procedure usp_ScoreQuery1as-- 查詢考試成績select Students.StudentId,StudentName,ClassName, ScoreSum = (CSharp + SqlserverDB) from Studentsinner join StudentClass on StudentClass.ClassId = Students.ClassIdinner join ScoreList on ScoreList.StudentId = Students.StudentIdorder by ScoreSum DESC-- 分析考試信息select ClassName,C#Avg=AVG(CSharp),DBAvg=AVG(SqlserverDB) from ScoreListinner join Students on Students.StudentId = ScoreList.StudentIdinner join StudentClass on StudentClass.ClassId = Students.ClassIdgroup by ClassNameorder by ClassNamego

針對上面實(shí)現(xiàn)的效果，繼續(xù)修改，改為參數(shù)可以帶默認(rèn)值?

use SMDB
go
if exists (select * from sysobjects where name = 'usp_ScoreQuery4')
drop procedure usp_ScoreQuery4
go
create procedure usp_ScoreQuery4-- 帶默認(rèn)值@CSharp int = 60,@SqlserverDB int = 60
asselect Students.StudentId,StudentName,CSharp as C#,SqlserverDB as DB from ScoreListinner join Students on Students.StudentId = ScoreList.StudentIdwhere CSharp < @CSharp or SqlserverDB < @SqlserverDB
go-- 調(diào)用參數(shù)帶默認(rèn)值的存儲過程
use SMDB
go
exec usp_ScoreQuery4 -- 兩個都走的默認(rèn)值
exec usp_ScoreQuery4 70,80 -- 第二個參數(shù)取默認(rèn)值
exec usp_ScoreQuery4 @SqlserverDB = 70 -- 第一個參數(shù)取默認(rèn)值
exec usp_ScoreQuery4 default,70 -- 第一個參數(shù)取默認(rèn)值(同上)

3. 自定義帶輸出參數(shù)的存儲過程
-- 問題：查詢考試成績，要求自定義分?jǐn)?shù)線，顯示查詢列表，并輸出缺考總?cè)藬?shù)、不及格總?cè)藬?shù)?

use SMDB
go
if exists (select * from sysobjects where name = 'usp_ScoreQuery5')
drop procedure usp_ScoreQuery5
go
create procedure usp_ScoreQuery5-- 輸出參數(shù)（習(xí)慣：輸出參數(shù)放在輸入?yún)?shù)的前面）@AbsentCount int output, -- 缺考總?cè)藬?shù)@FailCount int output, -- 不及格總?cè)藬?shù)-- 輸入?yún)?shù)@CSharp int = 60, -- CSharp分?jǐn)?shù)線@SqlserverDB int = 60 -- SqlserverDB分?jǐn)?shù)線
as-- 查詢考試成績，要求自定義分?jǐn)?shù)線select Students.StudentId,StudentName,CSharp as C#,SqlserverDB as DB from ScoreListinner join Students on Students.StudentId = ScoreList.StudentIdwhere CSharp < @CSharp or SqlserverDB < @SqlserverDB-- 缺考總?cè)藬?shù)select @AbsentCount = count(*) from Students where StudentId not in (select StudentId from ScoreList)-- 不及格總?cè)藬?shù)select @FailCount = count(*) from ScoreList where CSharp < @CSharp or SqlserverDB < @SqlserverDB
go-- 調(diào)用參數(shù)帶默認(rèn)值的存儲過程
use SMDB
go
-- 首先定義輸出參數(shù)（命名可以和上面的保持一致，不一樣也行）
declare @AbsentCount int, @FailCount int
-- 調(diào)用存儲過程時，輸出參數(shù)后面也必須加上 output 關(guān)鍵字
exec usp_ScoreQuery5 @AbsentCount output,@FailCount output 
select @AbsentCount as 缺考總?cè)藬?shù),@FailCount as 不及格總?cè)藬?shù)
-- 打印一下結(jié)果
print @AbsentCount
print @FailCount

鏈接SQL數(shù)據(jù)庫

public string connString = @"Server=.;DataBase=SMDB;Uid=sa;Pwd=123456";

?搭建Winfrom窗體?

一：無參數(shù)的存儲過程的調(diào)用?

? 不帶參數(shù)存儲過程的使用?

????????1指定存儲過程名稱
????????string proceName = "usp_ScoreQuery5";
????????2創(chuàng)建指令對象傳遞連接對象和存儲過程
????????SqlCommand cmd = new SqlCommand(proceName,conn);
????????3 指定指令執(zhí)行類型
????????cmd.CommandType = CommandType.StoredProcedure;
????????4 執(zhí)行指令
????????SqlDataReader dr = cmd.ExecuteReader();

 private void button1_Click(object sender, EventArgs e){//1 定義存儲過程名稱string proceName = "usp_ScoreQuery1";//2 創(chuàng)建一個指令對象SqlCommand cmd = new SqlCommand();//3 添加執(zhí)行sql語句和設(shè)置連接對象cmd.CommandText = proceName;// 添加執(zhí)行的sqlcmd.Connection = conn; //設(shè)置連接對象//4 執(zhí)行命令類型cmd.CommandType = System.Data.CommandType.StoredProcedure;// 5 取出數(shù)據(jù)List<Model1> model1s = new List<Model1>();// 存儲第一個表的數(shù)據(jù)List<Model2> models2 = new List<Model2>();//存儲第二個表的數(shù)據(jù)try{SqlDataReader dr = cmd.ExecuteReader(); // 讀取數(shù)據(jù)庫數(shù)據(jù)while (dr.Read()){model1s.Add(new Model1() { StudentId =Convert.ToInt32( dr["StudentId"]),StudentName = dr["StudentName"].ToString(),ClassName = dr["ClassName"].ToString(),ScoreNum = Convert.ToInt32(dr["ScoreSum"])});}//如果有第二個數(shù)據(jù)源 讀取第二個selectif (dr.NextResult()){while (dr.Read()){models2.Add(new Model2() {ClassName = dr["ClassName"].ToString(),CSharpAvg = Convert.ToInt32(dr["C#Avg"]),DBAvg   = Convert.ToInt32(dr["DBAvg"]),});}}dr.Close();this.dataGridView1.DataSource = model1s;this.dataGridView2.DataSource = models2;}catch{throw;}}

二：帶輸入?yún)?shù)的存儲過程的使用

帶輸入?yún)?shù)的存儲過程的調(diào)用

????????1指定存儲過程名稱
????????????????string proceName = "usp_ScoreQuery5";
????????2創(chuàng)建指令對象傳遞連接對象和存儲過程
? ? ? ? ????????SqlCommand cmd = new SqlCommand(proceName,conn);
????????3 指定指令執(zhí)行類型
????????????????cmd.CommandType = CommandType.StoredProcedure;
????????4 添加輸入?yún)?shù)
????????????????cmd.Parameters.Add(new SqlParameter()
? ? ? ? ? ? ????????{
? ? ? ? ? ? ? ????????? ParameterName = "@CSharp",
? ? ? ? ? ? ? ? ????????Direction = ParameterDirection.Input,
? ? ? ? ? ? ? ? ????????SqlDbType = SqlDbType.Int,
? ? ? ? ? ? ? ? ????????Value = 80
? ? ? ? ? ? ????????});
????????5執(zhí)行指令
????????????????SqlDataReader dr = cmd.ExecuteReader();

private void button2_Click(object sender, EventArgs e)
{//1 指明存儲過程string proceName = "usp_ScoreQuery4";//2 創(chuàng)建指令對象SqlCommand cmd = new SqlCommand();//3 設(shè)置連接和設(shè)置執(zhí)行過程cmd.Connection = conn; cmd.CommandText = proceName;//4  執(zhí)行類型cmd.CommandType = CommandType.StoredProcedure;//5 設(shè)置輸入?yún)?shù)//定義參數(shù)方法1SqlParameter csharp = new SqlParameter();csharp.ParameterName = "@CSharp";// 設(shè)置csharp是存儲過程中對應(yīng)@CSharp輸入?yún)?shù)csharp.Direction = ParameterDirection.Input;//設(shè)置為輸入?yún)?shù)csharp.Value = 180;// 設(shè)置參數(shù)的值為70csharp.SqlDbType = SqlDbType.Int; //設(shè)置參數(shù)類型cmd.Parameters.Add(csharp); // 把輸入?yún)?shù)添加到參數(shù)列表里面// 定義參數(shù)方法2 @SqlserverDBcmd.Parameters.Add(new SqlParameter() { ParameterName = "@SqlserverDB",Direction = ParameterDirection.Input,Value = 180,SqlDbType = SqlDbType.Int,});// 6 獲取數(shù)據(jù)List<Model3> list = new List<Model3>();try{SqlDataReader dr = cmd.ExecuteReader();while (dr.Read()){list.Add(new Model3(){StudentId = Convert.ToInt32(dr["StudentId"]),StudentName = dr["StudentName"].ToString(),CSharp = Convert.ToInt32(dr["C#"]),DB = Convert.ToInt32(dr["DB"]),});}dr.Close();this.dataGridView1.DataSource = list;}catch{throw;}
}

三：調(diào)用帶輸出參數(shù)的存儲過程 對應(yīng)的是model3對象

帶輸入?yún)?shù)帶輸出參數(shù)的調(diào)用

添加輸出參數(shù)即可
??? ?cmd.Parameters.Add(new SqlParameter()
? ? ? ? ? ? {
? ? ? ? ? ? ? ? ParameterName = "@AbsentCount",
? ? ? ? ? ? ? ? Direction = ParameterDirection.Output,
? ? ? ? ? ? ? ? SqlDbType = SqlDbType.Int,
? ? ? ? ? ? });
???獲取輸出參數(shù)的值
??? ? ? cmd.Parameters["@AbsentCount"].Value.ToString()

private void button3_Click(object sender, EventArgs e)
{string proceName = "usp_ScoreQuery5";SqlCommand cmd = new SqlCommand(proceName,conn);//cmd.CommandText = proceName;//cmd.Connection = conn;cmd.CommandType = CommandType.StoredProcedure;//設(shè)置輸出參數(shù)cmd.Parameters.Add(new SqlParameter() { ParameterName= "@AbsentCount",Direction = ParameterDirection.Output,SqlDbType=SqlDbType.Int,});cmd.Parameters.Add(new SqlParameter(){ParameterName = "@FailCount",Direction = ParameterDirection.Output,SqlDbType = SqlDbType.Int,});//輸入?yún)?shù)cmd.Parameters.Add(new SqlParameter(){ParameterName = "@CSharp",Direction = ParameterDirection.Input,SqlDbType = SqlDbType.Int,Value = 80});cmd.Parameters.Add(new SqlParameter(){ParameterName = "@SqlserverDB",Direction = ParameterDirection.Input,SqlDbType = SqlDbType.Int,Value = 80});//取數(shù)據(jù)List<Model3> list = new List<Model3>();SqlDataReader dr = cmd.ExecuteReader();while (dr.Read()){list.Add(new Model3(){StudentId = Convert.ToInt32(dr["StudentId"]),StudentName = dr["StudentName"].ToString(),CSharp = Convert.ToInt32(dr["C#"]),DB = Convert.ToInt32(dr["DB"]),});}dr.Close();this.dataGridView1.DataSource = list;//輸出參數(shù)的值怎么取？ 缺考總?cè)藬?shù)? 不及格人數(shù)?this.label1.Text = "缺考總?cè)藬?shù):" + cmd.Parameters["@AbsentCount"].Value.ToString() + "人";this.label2.Text = "不及格總?cè)藬?shù):" + cmd.Parameters["@FailCount"].Value.ToString() + "人";
}