C# 高階語法 —— Winfrom鏈接SQL數據庫的存儲過程

存儲過程在應用程序端的使用的優點

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

SQL自定義無參數存儲過程：

創建存儲過程 usp_ScoreQuery1
-- 查詢考試成績，顯示：學號、姓名、班級、總成績，并按成績的總分高低排序。
-- 統計分析考試成績，顯示班級名稱、C#平均分、數據庫平均分，按照班級分組實現。

 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

針對上面實現的效果，繼續修改，改為參數可以帶默認值?

use SMDB
go
if exists (select * from sysobjects where name = 'usp_ScoreQuery4')
drop procedure usp_ScoreQuery4
go
create procedure usp_ScoreQuery4-- 帶默認值@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-- 調用參數帶默認值的存儲過程
use SMDB
go
exec usp_ScoreQuery4 -- 兩個都走的默認值
exec usp_ScoreQuery4 70,80 -- 第二個參數取默認值
exec usp_ScoreQuery4 @SqlserverDB = 70 -- 第一個參數取默認值
exec usp_ScoreQuery4 default,70 -- 第一個參數取默認值(同上)

3. 自定義帶輸出參數的存儲過程
-- 問題：查詢考試成績，要求自定義分數線，顯示查詢列表，并輸出缺考總人數、不及格總人數?

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

鏈接SQL數據庫

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

?搭建Winfrom窗體?

一：無參數的存儲過程的調用?

? 不帶參數存儲過程的使用?

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

 private void button1_Click(object sender, EventArgs e){//1 定義存儲過程名稱string proceName = "usp_ScoreQuery1";//2 創建一個指令對象SqlCommand cmd = new SqlCommand();//3 添加執行sql語句和設置連接對象cmd.CommandText = proceName;// 添加執行的sqlcmd.Connection = conn; //設置連接對象//4 執行命令類型cmd.CommandType = System.Data.CommandType.StoredProcedure;// 5 取出數據List<Model1> model1s = new List<Model1>();// 存儲第一個表的數據List<Model2> models2 = new List<Model2>();//存儲第二個表的數據try{SqlDataReader dr = cmd.ExecuteReader(); // 讀取數據庫數據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"])});}//如果有第二個數據源 讀取第二個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;}}

二：帶輸入參數的存儲過程的使用

帶輸入參數的存儲過程的調用

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

private void button2_Click(object sender, EventArgs e)
{//1 指明存儲過程string proceName = "usp_ScoreQuery4";//2 創建指令對象SqlCommand cmd = new SqlCommand();//3 設置連接和設置執行過程cmd.Connection = conn; cmd.CommandText = proceName;//4  執行類型cmd.CommandType = CommandType.StoredProcedure;//5 設置輸入參數//定義參數方法1SqlParameter csharp = new SqlParameter();csharp.ParameterName = "@CSharp";// 設置csharp是存儲過程中對應@CSharp輸入參數csharp.Direction = ParameterDirection.Input;//設置為輸入參數csharp.Value = 180;// 設置參數的值為70csharp.SqlDbType = SqlDbType.Int; //設置參數類型cmd.Parameters.Add(csharp); // 把輸入參數添加到參數列表里面// 定義參數方法2 @SqlserverDBcmd.Parameters.Add(new SqlParameter() { ParameterName = "@SqlserverDB",Direction = ParameterDirection.Input,Value = 180,SqlDbType = SqlDbType.Int,});// 6 獲取數據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;}
}

三：調用帶輸出參數的存儲過程對應的是model3對象

帶輸入參數帶輸出參數的調用

添加輸出參數即可
??? ?cmd.Parameters.Add(new SqlParameter()
? ? ? ? ? ? {
? ? ? ? ? ? ? ? ParameterName = "@AbsentCount",
? ? ? ? ? ? ? ? Direction = ParameterDirection.Output,
? ? ? ? ? ? ? ? SqlDbType = SqlDbType.Int,
? ? ? ? ? ? });
???獲取輸出參數的值
??? ? ? 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;//設置輸出參數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,});//輸入參數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});//取數據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;//輸出參數的值怎么取？ 缺考總人數? 不及格人數?this.label1.Text = "缺考總人數:" + cmd.Parameters["@AbsentCount"].Value.ToString() + "人";this.label2.Text = "不及格總人數:" + cmd.Parameters["@FailCount"].Value.ToString() + "人";
}