[DBHelper] Ultimate Optimized Code

admin · Posted on 5/6/2015 12:03:28 PM

web.config configuration file

<connectionStrings>
<add name="ApplicationServices"
connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
providerName="System.Data.SqlClient" />
<add name="SQLConn" connectionString="server=.;database=shop_db;integrated security=true;"/>
</connectionStrings>

Copy code

HBHelper Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace shopDAL
{
public class DBHelper
{
//数据库连接对象
private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
//在配置web.config文件中获取sqlserver服务器ip账号和密码
string connectionString = ConfigurationManager.ConnectionStrings["SQLConn"].ConnectionString;
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
public static int GetScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteNonQuery());
return result;
}
public static int GetScalar(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteNonQuery());
return result;
}
public static SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
//private static string strConn = ConfigurationManager.ConnectionStrings["SQLConn"].ConnectionString;
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="sql">放入sql语句</param>
public static void setsql(string sql)
{
SqlCommand sqlComm = new SqlCommand(sql, Connection);
sqlComm.ExecuteNonQuery();
}
/// <summary>
/// 查询数据，返回table类型
/// </summary>
/// <param name="sql">放入sql语句</param>
/// <returns></returns>
public static DataTable gettable(string sql)
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(sql, Connection);
SqlDataAdapter sqlDA = new SqlDataAdapter(cmd);
sqlDA.Fill(dt);
return dt;
}
/// <summary>
/// 利用反射来返回数据
/// </summary>
/// <typeparam name="T">泛型集合</typeparam>
/// <param name="strSql">放入sql语句</param>
/// <returns></returns>
public static List<T> getList<T>(string strSql)
{
//委托的实例化,指向packBean方法
//PackageBean packageBean = new PackageBean(packBean);
List<T> list = new List<T>();
SqlCommand sqlComm = new SqlCommand(strSql, Connection);
SqlDataReader sqlDR = sqlComm.ExecuteReader();
while (sqlDR.Read())
{
//得到T的类型
Type t = typeof(T); ;
//查看类中的属性：
PropertyInfo[] pis = t.GetProperties();
// 用反射生成对象
T model = Activator.CreateInstance<T>();
foreach (PropertyInfo pi in pis)
{
if (pi != null)
{
//取得特定字段并赋值
pi.SetValue(model, sqlDR[pi.Name].ToString(), null);
}
}
list.Add(model);
}
return list;
}
}
}

Copy code

The temperature in the northern city is 22 degrees · Posted on 5/6/2015 9:13:18 PM

A lot

[Source] [DBHelper] Ultimate Optimized Code

Related Posts