資料庫結構

主鍵表category cId 與外鍵表 product categoryId相關聯

Product表

category表

存盤程序

1. getProByName

USE [ProductDB]
GO
/****** Object:  StoredProcedure [dbo].[getProByName]    Script Date: 2020/10/23 13:23:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[getProByName]
@name nvarchar(50)
as
begin
  select * from Product a,Category b where a.categoryId=b.cId and a.pName like '%'+@name+'%'
end

2. getProNum

USE [ProductDB]
GO
/****** Object:  StoredProcedure [dbo].[getProNum]    Script Date: 2020/10/23 13:25:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[getProNum]
@num int output
as
begin
	select @num = COUNT(*) from Product
end

3. getResult

USE [ProductDB]
GO
/****** Object:  StoredProcedure [dbo].[getResult]    Script Date: 2020/10/23 13:26:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[getResult]
as
begin
	return 666
end

4. getResultAndNum

USE [ProductDB]
GO
/****** Object:  StoredProcedure [dbo].[getResultAndNum]    Script Date: 2020/10/23 13:27:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[getResultAndNum]
@num int output
as
begin
	select @num = COUNT(*) from Product
	return @num
end

DBHelper

連接字串（配置在config檔案中）

1.config檔案配置

<connectionStrings>
      <add name="constr" connectionString="server=.;database=productdb;uid=sa;pwd=scce;"/>
</connectionStrings>

2.通過ConfigurationManager獲取config檔案中的連接字串

ConfigurationManager需要在參考---->添加參考---->程式集中添加System.Configuration參考

private static string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

sql查詢方法

//查詢
//回傳datatable
public static DataTable ExecuteQuery(string sql)
{
    SqlConnection conn = new SqlConnection(constr);
    conn.Open();
    SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
    DataTable dt = new DataTable();
    sda.Fill(dt);
    conn.Close();
    return dt;
}

sql增刪改方法

//增刪改
//回傳影響行數
 public static int ExecuteNonQuery(string sql)
 {
     SqlConnection conn = new SqlConnection(constr);
     conn.Open();
     SqlCommand com = new SqlCommand(sql, conn);
     int rows = com.ExecuteNonQuery();
     conn.Close();
     return rows;
 }

呼叫存盤程序(撰寫的可能不算嚴密)

先定義生成存盤程序引數的方法
1.輸出引數

/// <summary>
 /// 創建輸入引數,回傳SqlParameter
 /// </summary>
 /// <param name="name">引數名稱</param>
 /// <param name="val">引數值value</param>
 /// <returns>一個 Input SqlParameter物件</returns>
 public static SqlParameter CreateInput(string name,object val)
 {
     return new SqlParameter
     {
         ParameterName = name,
         Value = val
     };
 }

2.輸出引數

/// <summary>
/// 創建輸出引數,回傳SqlParameter
/// </summary>
/// <param name="name">引數名稱</param>
/// <param name="type">引數值類別 SqlDbType型別</param>
/// <returns>一個OutPut SqlParameter物件</returns>
public static SqlParameter CreateOutput(string name,SqlDbType type)
{
    return new SqlParameter
    {
        ParameterName = name,
        SqlDbType = type,
        Direction = ParameterDirection.Output
    };
}

3.Return值

/// <summary>
/// 創建回傳值
/// </summary>
///<param name="type">引數值類別</param>
/// <returns>一個ReturnValue SqlParameter物件</returns>
public static SqlParameter CreateReturn(SqlDbType type)
{
    return new SqlParameter
    {
        ParameterName = "@ReturnValue",
        SqlDbType = type,
        Direction = ParameterDirection.ReturnValue
    };
}

定義執行存盤程序的方法
1.執行查詢的存盤程序

/// <summary>
/// 執行查詢的存盤程序
/// </summary>
/// <param name="procName">存盤程序名稱</param>
/// <param name="parameters">存盤程序引數集</param>
/// <returns>一個DataTable物件</returns>
public static DataTable ExecuteProcedureQuery(string procName,SqlParameter[] parameters)
{
    SqlConnection conn = new SqlConnection(constr);
    conn.Open();
    SqlCommand command = new SqlCommand(procName, conn);
    command.CommandType = CommandType.StoredProcedure;
    if (parameters != null)
    {
        command.Parameters.AddRange(parameters);
    }
    DataTable dt = new DataTable();
    using (SqlDataReader sdr = command.ExecuteReader())
    {
        dt.Load(sdr);
    }
    conn.Close();
    return dt;

}

2.執行其他存盤程序

/// <summary>
/// 執行其他存盤程序
/// </summary>
/// <param name="procName">存盤程序名</param>
/// <param name="parameters">存盤程序引數集</param>
/// <returns>一個字典集合的SqlParameter結果集</returns>
public static IDictionary<string, SqlParameter> ExecuteProcedure(string procName,SqlParameter[] parameters)
{
    SqlConnection conn = new SqlConnection(constr);
    conn.Open();
    SqlCommand command = new SqlCommand(procName, conn);
    command.CommandType = CommandType.StoredProcedure;
    if (parameters != null)
    {
        command.Parameters.AddRange(parameters);
    }
    command.ExecuteNonQuery();
    var dict = new Dictionary<string, SqlParameter>();
    foreach (var item in parameters)
    {
        dict.Add(item.ParameterName, item);
    }
    conn.Close();
    return dict;
}

呼叫DBHelper

查詢方法

string querySql = "select * from product a,category b where a.categoryId = b.cId";
var queryResult = DBHelper.ExecuteQuery(querySql);
foreach (DataRow item in queryResult.Rows)
{
    Console.WriteLine($"{item["pId"]}\t{item["pName"]}\t{item["pPrice"]}\t{item["pStock"]}\t{item["pDesc"]}\t{item["cName"]}");
}

結果

增刪改方法(列舉一個)

string pName = "996";
int categoryId = 3;
decimal pPrice = 18.88M;
int pStock = 100;
string pDesc = "2020注定不平凡 2020-1024=996";
string addSql = $"insert into product values('{pName}',{categoryId},{pPrice},{pStock},'{pDesc}')";
int row = DBHelper.ExecuteNonQuery(addSql);
Console.WriteLine($"執行成功，添加成功{row}條資料！");

結果
執行成功，添加成功1條資料！
資料庫也成功添加一條資料

存盤程序（目錄一的四個）

1.getProByName

const string procName = "getProByName";
//回傳datatable結果集
SqlParameter[] param = new SqlParameter[]
{
    DBHelper.CreateInput("@name","滾筒洗衣機")
};
var temp = DBHelper.ExecuteProcedureQuery(procName, param);
foreach (DataRow item in temp.Rows)
{
    Console.WriteLine($"{item["pId"]}\t{item["pName"]}\t{item["pPrice"]}\t{item["pStock"]}\t{item["pDesc"]}\t{item["cName"]}");
}

結果

2.getProNum

const string outProcName = "getProNum";
//輸出引數
SqlParameter[] outputParams = new SqlParameter[]
{
    DBHelper.CreateOutput("@num",SqlDbType.Int)                
};
var res = DBHelper.ExecuteProcedure(outProcName, outputParams);
Console.WriteLine(res["@num"].Value);

結果
表內目前一共有10條資料
3.getResult

const string returnProcName = "getResult";
//return結果
SqlParameter[] returnParams = new SqlParameter[]
{
   	DBHelper.CreateReturn(SqlDbType.Int)
};
var data = DBHelper.ExecuteProcedure(returnProcName, returnParams);
Console.WriteLine(data["@ReturnValue"].Value);

結果
666
4.getResultAndNum

const string outAndReturnProcName = "getResultAndNum";
//輸出引數 + return 結果
SqlParameter[] lastParms = new SqlParameter[]
{
    
    DBHelper.CreateOutput("@num",SqlDbType.Int),
    DBHelper.CreateReturn(SqlDbType.Int)
};
var last = DBHelper.ExecuteProcedure(outAndReturnProcName, lastParms);
Console.WriteLine(last["@num"].Value+"\r\n"+last["@ReturnValue"].Value);

結果
10
10

總結

蕪湖起飛，ADO.NET連接SQL Server的DBHelper案例就完了，不喜勿噴，謝謝，