ASP.NETCore3.0(一)--编写SqlHelper通用类连接数据库

/// Object obj = ExecuteScalar(connString, Procedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// /// ⼀个有效的数据库连接字符串 /// SqlCommand命令类型 (存储过程， T-SQL语句，

等等。) /// 存储过程的名字或者 T-SQL

语句 /// 以数组形式提供SqlCommand命令中⽤到的参数列表 /// An object that should be converted to the expected type using {Type} public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = eScalar(); (); return val; } #endregion ///

参数缓存的键 /// an array of SqlParamters to be cached

被缓存的参数数组 public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) { parmCache[cacheKey] = commandParameters; } ///

⽤于查找参数的键 /// Cached SqlParamters array

缓存参数数组 public static SqlParameter[] GetCachedParameters(string cacheKey) { SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey]; if (cachedParms == null) return null; SqlParameter[] clonedParms = new SqlParameter[]; for (int i = 0, j = ; i < j; i++) for (int i = 0, j = ; i < j; i++) clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms; } ///

"AllowedHosts": "*"}四、编写业务逻辑代码using System;using c;using ;using ;using ;using ;using ent;namespace { public class studentDLL { public DataTable GetData() { var sql = "select * from Student"; var data = le(, sql, new SqlParameter[] { }); return data[0]; }

}}五、前台展⽰打开项⽬⾃动⽣成的Pages⽂件夹⾥的⽂件(1)在⽂件中，编写代码：using System;using c;using ;using ;using ;using ;using ;using ages;using g;namespace { public class IndexModel : PageModel { private readonly ILogger _logger; public IndexModel(ILogger logger) { _logger = logger; }

public DataTable Data;

public void OnGet() {

var studentDll = new studentDLL(); Data = a(); }

}}（2）在⽂件中编写代码@page@model IndexModel@{ ViewData["Title"] = "Home page";}@{

var data = ; if ( > 0) { for (int i = 0; i < ; i++) { var row = [i]; @row["ID"]; @row["Name"].ToString(); @row["Sex"]; } }}六、数据库⽂件七、运⾏结果

2023年6月21日发(作者：)

e3.0（⼀）--编写SqlHelper通⽤类连接数据库⼀、新建⼀个.NET Core的⼯程 ⽂件⼆、新建sqlHelper类需要使⽤NuGet安装⼀下⽂件FrameworkCore以下代码较长，可以先粘贴复制，以后再仔细打磨using uration;using s;using System;using tions;using c;using uration;using ;using ent;using ;using ;using ;namespace { ///

此类为抽象类， ///

不允许实例化，在应⽤时直接调⽤即可 /// public abstract class SqlHelper { //加载 static IConfiguration configuration = new ConfigurationBuilder() .SetBasePath(rentDirectory()) .AddJsonFile("").Build(); ///

等等。) /// 存储过程的名字或者 T-SQL

语句 /// 以数组形式提供SqlCommand命令中⽤到的参数列表 /// 返回⼀个数值表⽰此SqlCommand命令执⾏后影响的⾏数 public static int ExecteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) { //通过PrePareCommand⽅法将参数逐个加⼊到SqlCommand的参数集合中 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = eNonQuery(); //清空SqlCommand中的参数列表 (); return val; } } ///

等等。) /// 存储过程的名字或者 T-SQL

语句 /// 以数组形式提供SqlCommand命令中⽤到的参数列表 /// 返回⼀个数值表⽰此SqlCommand命令执⾏后影响的⾏数 public static int ExecteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { return ExecteNonQuery(connectionString, cmdType, cmdText, commandParameters); } ///

语句 /// 以数组形式提供SqlCommand命令中⽤到的参数列表 /// 返回⼀个表集合(DataTableCollection)表⽰查询得到的数据集 public static DataTableCollection GetTable(string connecttionString, CommandType cmdTye, string cmdText, SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connecttionString)) { PrepareCommand(cmd, conn, null, cmdTye, cmdText, commandParameters); SqlDataAdapter adapter = new SqlDataAdapter(); Command = cmd; (ds); } DataTableCollection table = ; return table; } ///

语句 /// 以数组形式提供SqlCommand命令中⽤到的参数列表 /// 返回⼀个表集合(DataTableCollection)表⽰查询得到的数据集 public static DataTableCollection GetTable(CommandType cmdTye, string cmdText, SqlParameter[] commandParameters) { return GetTable(connectionString, cmdTye, cmdText, commandParameters); } ///

语句 /// 以数组形式提供SqlCommand命令中⽤到的参数列表1 /// 返回⼀个表集合(DataTableCollection)表⽰查询得到的数据集 public static DataTableCollection GetTableProducts(string cmdText, SqlParameter[] commandParameters) { return GetTable(Procedure, cmdText, commandParameters); } ///

语句 /// 以数组形式提供SqlCommand命令中⽤到的参数列表 /// 返回⼀个表集合(DataTableCollection)表⽰查询得到的数据集 public static DataTableCollection GetTableText(string cmdText, SqlParameter[] commandParameters) { return GetTable(, cmdText, commandParameters); } #endregion ///

命令 /// 已经存在的数据库连接 /// 数据库事物处理 /// SqlCommand命令类型 (存储过程， T-SQL语句，

等等。) /// Command text，T-SQL语句

例如 Select * from Products /// 返回带参数的命令 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { //判断数据库连接状态 if ( != ) (); tion = conn; dText = cmdText; //判断是否需要事物处理 if (trans != null) ction = trans; dType = cmdType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) (parm); } } ///

等等。) /// 存储过程的名字或者 T-SQL

语句 /// 以数组形式提供SqlCommand命令中⽤到的参数列表 /// A SqlDataReader containing the results public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connectionString); // we use a try/catch here because if the method throws an exception we want to

// close the connection throw code, because no datareader will exist, hence the

// onnection will not work try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataReader rdr = eReader(onnection); (); return rdr; } catch { (); throw; } } #region//ExecuteDataSet⽅法 ///

等等。) /// 存储过程的名字或者 T-SQL

语句 /// 以数组形式提供SqlCommand命令中⽤到的参数列表 /// return a dataset public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlConnection conn = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); Command = cmd; (ds); return ds; } catch { (); throw; } } ///

等等。) /// 存储过程的名字或者 T-SQL

语句 /// 以数组形式提供SqlCommand命令中⽤到的参数列表 /// return a dataset public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { return ExecuteDataSet(connectionString, cmdType, cmdText, commandParameters); } ///

语句 /// 以数组形式提供SqlCommand命令中⽤到的参数列表 /// return a dataset public static DataSet ExecuteDataSetText(string cmdText, params SqlParameter[] commandParameters) { return ExecuteDataSet(connectionString, , cmdText, commandParameters); } public static DataView ExecuteDataSet(string connectionString, string sortExpression, string direction, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlConnection conn = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); Command = cmd; (ds); DataView dv = [0].DefaultView; = sortExpression + " " + direction; return dv; } catch { (); throw; } } #endregion #region // ExecuteScalar⽅法 ///

等等。) /// 存储过程的名字或者 T-SQL

语句 /// 以数组形式提供SqlCommand命令中⽤到的参数列表 /// 返回⼀个对象 public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { return ExecuteScalar(tionString, cmdType, cmdText, commandParameters); } ///

语句 /// 以数组形式提供SqlCommand命令中⽤到的参数列表 /// 返回⼀个对象 public static object ExecuteScalarText(string cmdText, params SqlParameter[] commandParameters) { return ExecuteScalar(tionString, , cmdText, commandParameters); } ///