如何使用C# 操作存儲過程，執行sql語句？ 閑話不多說，直接上代碼： /// <summary> /// Sql通用類 /// </summary> public class SqlHelper { 首先配置連接字元串 public static string connStr = Configur ...
如何使用C# 操作存儲過程，執行sql語句？
閑話不多說，直接上代碼：
/// <summary>/// Sql通用類
/// </summary>
public class SqlHelper
{ 首先配置連接字元串
public static string connStr = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;//ConnString表示webconfig中的連接字元串 執行存儲過程不設置超時時間
/// <summary>
/// 調用存儲過程
/// </summary>
/// <param name="connStr">連接字元串</param>
/// <param name="storedProcedureName">存儲過程名稱</param>
/// <param name="ResponseBool">存儲過程執行狀態</param>
/// <param name="ResponseMsg">執行存儲過程狀態描述</param>
/// <param name="paramsObject">存儲過程輸入參數</param>
/// <returns></returns>
public static DataSet Sql_GetStoredProcedureFunction(string connStr, string storedProcedureName, out bool ResponseBool, out string ResponseMsg, params ParameterKeyValuesEntity[] paramsObject)
{
DataSet ResponseDs = new DataSet();
ResponseBool = true;
ResponseMsg = "獲取成功！";
try
{
using (SqlConnection sqlConn = new SqlConnection(connStr))
{
sqlConn.Open();
using (SqlCommand sqlCmd = new SqlCommand(storedProcedureName, sqlConn))
{
sqlCmd.CommandType = CommandType.StoredProcedure;
if (paramsObject.Count() > 0)
{
for (int i = 0; i < paramsObject.Count(); i++)
{
SqlParameter sqlParameter = new SqlParameter(paramsObject[i].Key, paramsObject[i].Value);
sqlCmd.Parameters.Add(sqlParameter);
}
}
SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
sda.Fill(ResponseDs);
}
}
}
catch (Exception e)
{
ResponseBool = false;
ResponseMsg = $"查詢存儲過程時出現異常，存儲過程：【{storedProcedureName}】\n 異常原因：【{e.Message}】\n 異常詳細信息：【{e.StackTrace}】！";
}
return ResponseDs;
} 當存儲過程執行時間太長時，存儲過程的預設超時時間是30s，需要設置存儲過程執行超時時間 /// <summary>
/// 調用存儲過程 (自定義超時時間)
/// </summary>
/// <param name="connStr">連接字元串</param>
/// <param name="storedProcedureName">存儲過程名稱</param>
/// <param name="commandOutTime">執行存儲過程請求超時時間(單位：s)</param>
/// <param name="ResponseBool">存儲過程執行狀態</param>
/// <param name="ResponseMsg">執行存儲過程狀態描述</param>
/// <param name="paramsObject">存儲過程輸入參數</param>
/// <returns></returns>
public static DataSet Sql_GetStoredProcedureFunction(string connStr, string storedProcedureName, int commandOutTime, out bool ResponseBool, out string ResponseMsg, params ParameterKeyValuesEntity[] paramsObject)
{
DataSet ResponseDs = new DataSet();
ResponseBool = true;
ResponseMsg = "獲取成功！";
try
{
using (SqlConnection sqlConn = new SqlConnection(connStr))
{
sqlConn.Open();
using (SqlCommand sqlCmd = new SqlCommand(storedProcedureName, sqlConn))
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandTimeout = commandOutTime;
if (paramsObject.Count() > 0)
{
SqlParameter[] sqlParameters = new SqlParameter[paramsObject.Count()];
for (int i = 0; i < paramsObject.Count(); i++)
{
SqlParameter sqlParameter = new SqlParameter(paramsObject[i].Key, paramsObject[i].Value);
sqlCmd.Parameters.Add(sqlParameter);
}
}
SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
sda.Fill(ResponseDs);
}
}
}
catch (Exception e)
{
ResponseBool = false;
ResponseMsg = $"查詢存儲過程時出現異常，存儲過程：【{storedProcedureName}】\n 異常原因：【{e.Message}】\n 異常詳細信息：【{e.StackTrace}】！";
}
return ResponseDs;
} 執行sql語句，進行增刪改操作 /// <summary>
/// 增刪改數據
/// </summary>
/// <param name="sqlConnStr, ">資料庫連接字元串</param>
/// <param name="sql">執行的sql語句</param>
/// <param name="paramsObject">輸入參數</param>
/// <returns></returns>
public static int SQLExecuteData(string sqlConnStr, string sql, params ParameterKeyValuesEntity[] paramsObject)
{
int count = 0;
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn); //定義一個sql操作命令對象
if (paramsObject.Count() > 0)
{
for (int i = 0; i < paramsObject.Count(); i++)
{
SqlParameter sqlParameter = new SqlParameter(paramsObject[i].Key, paramsObject[i].Value);
cmd.Parameters.Add(sqlParameter);
}
} count = cmd.ExecuteNonQuery(); //執行語句
conn.Close(); //關閉連接
cmd = null;
conn.Dispose(); //釋放對象
} return count;
} 當資料庫中表關係及其複雜，並且數據量特別多的時候(一般情況下用緩存解決問題)，執行sql查詢語句相當耗時，需要設置sql語句請求超時時間。 執行sql查詢語句，設置sql查詢語句超時時間
/// <summary>
/// 執行SQL腳本
/// </summary>
/// <param name="connStr">連接字元串</param>
/// <param name="sqlScript">SQL腳本</param>
/// <param name="ResponseBool">執行狀態</param>
/// <param name="ResponseMsg">狀態描述</param>
/// <param name="commandOutTime">執行sql語句請求超時時間(單位：s)</param>
/// <param name="paramsObject">輸入參數</param>
/// <returns></returns>
public static DataSet Sql_GetStored(string connStr, string sqlScript, out bool ResponseBool, out string ResponseMsg, int commandOutTime = 500, params ParameterKeyValuesEntity[] paramsObject)
{
DataSet ResponseDs = new DataSet();
ResponseBool = true;
ResponseMsg = "獲取成功！";
try
{
using (SqlConnection sqlConn = new SqlConnection(connStr))
{
sqlConn.Open();
using (SqlCommand sqlCmd = new SqlCommand(sqlScript, sqlConn))
{
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandTimeout = commandOutTime;
if (paramsObject.Count() > 0)
{
for (int i = 0; i < paramsObject.Count(); i++)
{
SqlParameter sqlParameter = new SqlParameter(paramsObject[i].Key, paramsObject[i].Value);
sqlCmd.Parameters.Add(sqlParameter);
}
}
SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
sda.Fill(ResponseDs);
}
}
}
catch (Exception e)
{
ResponseBool = false;
ResponseMsg = $"查詢存儲過程時出現異常，SQL腳本：【{sqlScript}】\n 異常原因：【{e.Message}】\n 異常詳細信息：【{e.StackTrace}】！";
}
return ResponseDs;
} 入參實體建類
/// <summary>
/// 輸入參數實體 參數名稱(Key)/參數值(Value)
/// </summary>
public class ParameterKeyValuesEntity
{
/// <summary>
/// 參數名稱
/// </summary>
public string Key { get; set; }
/// <summary>
/// 參數值
/// </summary>
public object Value { get; set; }
} 執行存儲過程示例： public Result 方法名(string 入參1,string 入參2, string 入參3)
{
try
{
//定義輸出參數
Result result = new Result();
//存儲過程名稱
string procName = "存儲過程名稱";
#region -- 執行存儲過程獲取數據
//返回值狀態
bool responseBool = true;
//返回值狀態描述
string responseMsg = string.Empty;
//存儲過程輸入參數實體
ParameterKeyValuesEntity[] parameterKeyValue = new ParameterKeyValuesEntity[]
{
new ParameterKeyValuesEntity(){Key="@存儲過程入參1",Value=賦值1},
new ParameterKeyValuesEntity(){Key="@存儲過程入參2",Value=賦值2},
new ParameterKeyValuesEntity(){Key="@存儲過程入參3",Value=賦值3},
};
//使用sql通用類的方法執行存儲過程
DataSet ds = SqlHelper.Sql_GetStoredProcedureFunction(connStr, procName, out responseBool, out responseMsg, parameterKeyValue);
if (!responseBool)
{
result.code = "204";
result.msg = $"查詢存儲過程時出現異常，異常信息：{responseMsg}";
ExceptionLogHelper.WriteLog($"業務異常：存儲過程名：{procName}---異常信息：{responseMsg}");//項目中的異常日誌
return result;
}
DataTable dt = ds.Tables[0];
if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
{
獲取存儲過程執行後的數據，給實體類賦值
}
#endregion
result.data = loopbackdata;
string json = JsonConvert.SerializeObject(result.data);
result = ResultHelper.ReturnResultSuccess(json, typeof(JObject));
return result;
}
catch (Exception e)
{
ExceptionLogHelper.WriteLog($"業務異常：{e}");
return ResultHelper.ReturnResultError($"異常信息：{e}");
}
} The END.......................
