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();
}
}
}
}
}
1.读取微软配置文件使用的System.Configuration.ConfigurationManager，需要添加引用
2.配置文件中需增加数据库连接节点，类似如下
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="dbConnection" connectionString="server=.;database=数据库名称;uid=用户名;pwd=密码;"/>
</connectionStrings>
</configuration>