(1)在app.config中添加数据库连接字符串
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
</startup>
<connectionStrings>
<add connectionString="server = 127.0.0.1;database = DBTest;uid = sa;pwd = 123" name="conStr"/>
</connectionStrings>
</configuration>
(2)添加对System.Configuration的引用
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
namespace BJSGS
{
public class SqlHelper
{
//读取连接字符串
private static readonly string str = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
#region [主方法]<ExecuteNonQuery>此方法用于数据的增删改
/// <summary>
/// 此方法用于数据的增删改
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="ps">sql语句中的参数</param>
/// <returns>返回的受影响的行数,int类型</returns>
public static int ExecuteNonQuery(string sql, params SqlParameter[] ps)
{
//连接数据库
using (SqlConnection con = new SqlConnection(str))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
try
{
con.Open();
if (ps != null)
{
cmd.Parameters.AddRange(ps);
}
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
con.Close();
con.Dispose();
throw ex;
}
}
}
}
#endregion
#region [主方法]<ExecuteScalar>该方法用于查询读取数据,返回首行首列
/// <summary>
/// 该方法用于查询读取数据,返回首行首列
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="ps">sql语句中的参数</param>
/// <returns>首行首列,object类型</returns>
public static object ExecuteScalar(string sql, params SqlParameter[] ps)
{
using (SqlConnection con = new SqlConnection(str))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
try
{
con.Open();
if (ps != null)
{
cmd.Parameters.AddRange(ps);
}
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
con.Close();
con.Dispose();
throw ex;
}
}
}
}
#endregion
#region [主方法]<ExecuteReader>该方法用于查询读取数据,返回SqlDataReader对象
/// <summary>
/// 该方法用于查询读取数据,返回SqlDataReader对象
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="ps">sql语句中的参数</param>
/// <returns>返回的是SqlDataReader对象,里面有数据</returns>
public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] ps)
{
SqlConnection con = new SqlConnection(str);
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (ps != null)
{
cmd.Parameters.AddRange(ps);
}
try
{
con.Open();
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
con.Close();
con.Dispose();
throw ex;
}
}
}
#endregion
#region [主方法]<ExecuteTable>该方法用于查询读取数据,返回DataTable对象
/// <summary>
/// 该方法用于查询读取数据,返回DataTable对象
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="ps">sql语句中的参数</param>
/// <returns>返回的是DataTable对象,里面有数据</returns>
public static DataTable ExecuteTable(string sql, params SqlParameter[] ps)
{
DataTable dt = new DataTable();
using (SqlDataAdapter sda = new SqlDataAdapter(sql, str))
{
sda.SelectCommand.Parameters.AddRange(ps);
sda.Fill(dt);
}
return dt;
}
#endregion
}
}