SqlHelper(基础)
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks;
namespace H.DAL { /// <summary> /// 数据库操作帮助类 /// 此段基础代码为SQLServer数据库帮助类 /// 如需操作MySQL /// 1.将代码中Sql改为MySql /// 2.添加MySql.Data.dll引用(可通过官网或NuGet) /// 3.using System.Data.SqlClient;替换为using MySql.Data.MySqlClient; /// </summary> public class SQLHelper { /// <summary> /// 数据库连接字符串 /// </summary> private static string connectionStr = System.Configuration.ConfigurationManager.ConnectionStrings[“dbConnection”].ConnectionString; public SQLHelper() { } public SQLHelper(string connectionStr) { SQLHelper.connectionStr = connectionStr; } /// <summary> /// 得到连接对象 /// </summary> /// <returns></returns> public static SqlConnection GetConn() { SqlConnection sqlconn = null; sqlconn = new SqlConnection(connectionStr); return sqlconn; }
/// <summary> /// 查询操作 /// </summary> /// <param name=”sql”></param> /// <returns></returns> public static DataTable GetDataTable(string sql, params SqlParameter[] sp) { using (SqlConnection conn = GetConn()) { conn.Open(); using (SqlDataAdapter sda = new SqlDataAdapter(sql, conn)) { sda.SelectCommand.Parameters.AddRange(sp); DataTable dt = new DataTable(); sda.Fill(dt); return dt; } } } /// <summary> /// 增删改操作 /// </summary> /// <param name=”sql”>sql语句</param> /// <returns>执行后的条数</returns> public static int ExecuteNonQuery(string sql, params SqlParameter[] sp) { using (SqlConnection conn = GetConn()) { conn.Open(); using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.AddRange(sp); int i = cmd.ExecuteNonQuery(); return i; } }
}
/// <summary> /// 执行一条SQL语句,返回首行首列 /// </summary> /// <param name=”sql”>sql语句</param> /// <returns>首行首列</returns> public static object ExecuteScalar(string sql, params SqlParameter[] sp) { using (SqlConnection conn = GetConn()) { conn.Open(); using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.AddRange(sp); return cmd.ExecuteScalar(); } } } } }