昨天发了一个贴。。引来大家发粪。。。那大家公布一下你们自己的DbHelperSQL。，发来，大家一起起学习习，改进一下。。。
http://bbs.csdn.net/topics/390685831?page=1#post-396529087
如题，大家发一下，你自己的DbHelperSQL。，借鉴学习一下。。
谢谢。。。
[解决办法]
代码
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace Common
{
public abstract class DbHelperSQL
{
//格式化字符串
public static string inSQL(string formatStr)
{
string Str = formatStr;
if (formatStr != null && formatStr != string.Empty)
{
Str = Str.Replace("'", "''");
}
return Str;
}
//获取连接字符串
public static string ConnectionString
{
get
{
string _connectionstring = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
string ConStringEncrypt = ConfigurationManager.AppSettings["ApplicationServices"];
if (ConStringEncrypt =="true")
{
_connectionstring = DESEncrypt.Encrypt(_connectionstring);
}
return _connectionstring;
}
}
#region 执行带参数的SQL语句
// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
{
SqlConnection connection = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd,connection,null,SQLString,cmdParms);
SqlDataReader myReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return myReader;
}
catch(System.Data.SqlClient.SqlException e)
{
throw e;
}
}
// 执行SQL语句，返回影响的记录数
public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd,connection,null,SQLString,cmdParms);
int rows=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch(System.Data.SqlClient.SqlException e)
{
throw e;
}
}
}
}
// 执行查询语句，返回DataSet
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput
[解决办法]
parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
#endregion
}
}
在这个方法库中，如果一个方法返回类型为 DbDataReader，请谨慎，最好不要设计。你并不能保证使用者会及时关闭连接，因此这种设计就好像“开个小超市但是疏于管理，等于邀请小偷来光顾”，是自找麻烦。
你可以直接返回 IEnumerable<T> 用户定义类型的数据集合。例如对于实体类型
public class MyObj
{
public string Name;
public double Value;
}
List<MyObj> result;
using (var conn = new OracleConnection(cnStr))
{
conn.Open();
var comm = conn.CreateCommand();
comm.CommandText = "select TheName, Number from Users ..........";
comm.CommandType = System.Data.CommandType.Text;
result = ( from DbDataRecord record in comm.ExecuteReader()
select new MyObj{
Name = (string)record["TheName"],
Value = (double)record["Number"]
};
).ToList();
}
public static List<T> ExecuteReader(string SQLString, Func<DbDataReader, List<T>> converter
{
using (var conn = new OracleConnection(cnStr))
{
conn.Open();
var comm = conn.CreateCommand();
comm.CommandText = SQLString;
comm.CommandType = System.Data.CommandType.Text;
return Conveter(comm.ExecuteReader());
}
}
static List<MyObj> ConvertToMyObjList(DbDataReader dataReader)
{
return ( from DbDataRecord record in dataReader
select new MyObj{
Name = (string)record["TheName"],
Value = (double)record["Number"]
};
).ToList());
}
var result = ExecuteReader("select TheName, Number from Users ..........", r=> ConvertToMyObjList(r) );