大家的SqlHelper是什么样的? 有没简化版的?

2009-10-19 10:03:02
RT```
...全文
1265 21 打赏 收藏 转发到动态 举报
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
DreamCrane 2010-11-04
  • 打赏
  • 举报
回复
学习中!!!
jiankeqcaf 2010-06-02
  • 打赏
  • 举报
回复
好贴 收藏
zuoming120 2009-10-19
  • 打赏
  • 举报
回复
namespace SystemDAL
{
public class Dbhelpe
{
/// <summary>
/// 构造函数
/// </summary>
public Dbhelpe()
{ }

/// <summary>
/// 返回第一行的语句
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="param">边参</param>
/// <returns>返回查询记录</returns>
public static object GetExecuteScalar(CommandType commtype, string sql, params SqlParameter[] param)
{
object o = null;
using (SqlConnection conn = new SqlConnection(ConnString))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, conn, null, commtype, sql, param);
o = cmd.ExecuteScalar();
}
return o;
}
/// <summary>
/// sql执行语句
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="param">边参</param>
/// <returns>返回1或0</returns>
public static bool ExecSqlCommand(CommandType commtype, string sql, params SqlParameter[] param)
{
int i = 0;
using (SqlConnection conn = new SqlConnection(ConnString))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, conn, null, commtype, sql, param);
i = cmd.ExecuteNonQuery();
}
return i > 0 ? true : false;
}
/// <summary>
/// 返回查询的语句
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="param">边参</param>
/// <returns>返回查询记录</returns>
public static DataTable GetDatableSQL(CommandType commtype, string sCommand, params SqlParameter[] param)
{
DataTable dtRet = new DataTable();
using (SqlConnection conn = new SqlConnection(ConnString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand command = new SqlCommand();
PrepareCommand(command, conn, null, commtype, sCommand, param);
adapter.SelectCommand = command;
adapter.Fill(dtRet);
}
return dtRet;
}

/// <summary>
/// 调用存储过程
/// </summary>
/// <param name="sCommand">存储过程的名字</param>
/// <param name="param">参数</param>
/// <returns>返回语句</returns>
public static DataTable GetDatablePrcoSQL(CommandType commtype, string sCommand, params SqlParameter[] param)
{
DataTable dtRed = new DataTable();
using (SqlConnection conn = new SqlConnection(ConnString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand command = new SqlCommand();
adapter.SelectCommand = command;
PrepareCommand(command, conn, null, commtype, sCommand, param);
adapter.Fill(dtRed);
}
return dtRed;
}

/// <summary>
/// 执行数据库的语句
/// </summary>
/// <param name="command">commd对象</param>
/// <param name="conn">数据库链接对象</param>
/// <param name="trans">事物</param>
/// <param name="cmdType">sql语句的类型</param>
/// <param name="cmdText">sql语句</param>
/// <param name="param">参数</param>
private static void PrepareCommand(SqlCommand command, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] param)
{

if (conn.State != ConnectionState.Open)
conn.Open();
command.Connection = conn;
command.CommandText = cmdText;
if (trans != null)
command.Transaction = trans;
command.CommandType = cmdType;
if (param != null)
{
command.Parameters.AddRange(param);
}
}
}

}
mark620 2009-10-19
  • 打赏
  • 举报
回复
现在已经很多版本了
zjybushiren88888 2009-10-19
  • 打赏
  • 举报
回复
数据库连接对象采用单例模式获取 别的楼上都回答很多勒
加油馒头 2009-10-19
  • 打赏
  • 举报
回复
[Quote=引用 12 楼 free_wind22 的回复:]
谢谢各位..


引用 11 楼 caobob 的回复:
多线程还用静态方法,残念。


你觉得应该怎么改?
[/Quote]

吧静态的 参数 //存储过程参数和值
public static string[] Params = null;
public static object[] Values = null;
改成 非静态就OK

因为在多线程情况下,你的对象独立,但是对象的静态成员是共享的,所以才会出现 结果不多
2009-10-19
  • 打赏
  • 举报
回复
谢谢各位..


[Quote=引用 11 楼 caobob 的回复:]
多线程还用静态方法,残念。
[/Quote]

你觉得应该怎么改?
caobob 2009-10-19
  • 打赏
  • 举报
回复
多线程还用静态方法,残念。
puzhichen 2009-10-19
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 sp1234 的回复:]
从来不用。自己写了个ORM,比Linq to SQL好用多了。
[/Quote]
分享下?
snowman3510 2009-10-19
  • 打赏
  • 举报
回复
public static class DBHelper
{

private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=E:\BookShop\DB\MyBookShop.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
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, SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}

public static int ExecuteCommand(string sql, SqlParameter value)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.Add(value);
int result = cmd.ExecuteNonQuery();
return result;
}


public static int ExecuteScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = (int)cmd.ExecuteScalar();
return result;
}

public static int ExecuteScalar(string sql, SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = (int)cmd.ExecuteScalar();
return result;
}

public static int ExecuteScalar(string sql, SqlParameter value)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.Add(value);
int result = (int)cmd.ExecuteScalar();
return result;
}

public static SqlDataReader ExecuteReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}

public static SqlDataReader ExecuteReader(string sql, SqlParameter value)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.Add(value);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}

public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
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;
}




}
yidao8808 2009-10-19
  • 打赏
  • 举报
回复
学习了
wuyq11 2009-10-19
  • 打赏
  • 举报
回复
private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = "Data Source=localhost;Initial Catalog=MyBookShop;User ID=sa;password=123456";
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, SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}

public static int ExecuteCommand(string sql, SqlParameter value)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.Add(value);
int result = cmd.ExecuteNonQuery();
return result;
}


public static int ExecuteScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = (int)cmd.ExecuteScalar();
return result;
}

public static int ExecuteScalar(string sql, SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = (int)cmd.ExecuteScalar();
return result;
}

public static int ExecuteScalar(string sql, SqlParameter value)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.Add(value);
int result = (int)cmd.ExecuteScalar();
return result;
}

public static SqlDataReader ExecuteReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}

public static SqlDataReader ExecuteReader(string sql, SqlParameter value)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.Add(value);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}

public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
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;
}
http://topic.csdn.net/u/20090808/10/89f29f28-8b4d-4f16-8844-f367e3952f78.html?46063

lovexilove 2009-10-19
  • 打赏
  • 举报
回复
高手分享下吧!
_see_you_again_ 2009-10-19
  • 打赏
  • 举报
回复

public abstract class SQLAccess
{
// Fields
public static readonly string connString;
private static string key = string.Empty;
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
private static int[] tmp = new int[] { 0x17, 0xea, 0xc3, 0xa5, 0xc9, 240, 0x8f, 0xc6 };

// Methods
static SQLAccess()
{
foreach (int num in tmp)
{
key = key + ((char) num).ToString();
}
try
{
connString = SecurityManager.Decrypt(ConfigurationManager.AppSettings["connectstring"].ToString(), key);
}
catch
{
}
}

protected SQLAccess()
{
}

public static void CacheParam(string cacheKey, params SqlParameter[] cmdParam)
{
parmCache[cacheKey] = cmdParam;
}

public static DataSet ExecuteDataSet(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
DataSet set2;
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
DataSet dataSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dataSet);
cmd.Parameters.Clear();
set2 = dataSet;
}
catch
{
conn.Close();
throw;
}
finally
{
if (conn != null)
{
conn.Dispose();
}
}
return set2;
}

public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParams);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return num;
}

public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParams);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
connection.Close();
return num;
}
}

public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlDataReader reader2;
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
reader2 = reader;
}
catch
{
conn.Close();
throw;
}
return reader2;
}

public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParams);
object obj2 = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return obj2;
}
}

public static SqlParameter[] GetCacheParam(string cacheKey)
{
SqlParameter[] parameterArray = (SqlParameter[]) parmCache[cacheKey];
if (parameterArray == null)
{
return null;
}
return parameterArray;
}

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
if (conn.get_State() != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.set_CommandText(cmdText);
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.set_CommandType(cmdType);
if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
cmd.Parameters.Add(parameter);
}
}
}
}



  • 打赏
  • 举报
回复
从来不用。自己写了个ORM,比Linq to SQL好用多了。
2009-10-19
  • 打赏
  • 举报
回复
照着微软的改的. 把参数改成类的属性了.发现在用多线程的时候会混乱,这个怎么改?


public abstract class SqlHelper {
//链接字符串
public static readonly string ConnnectionString = ConfigurationManager.ConnectionStrings["SqlConnString"].ConnectionString;

//存储过程参数和值
public static string[] Params = null;
public static object[] Values = null;

//错误信息
public static string ErrorString = "";


/// <summary>
/// 填充SqlCommand对象
/// </summary>
/// <param name="conn">SqlConnection 连接对象</param>
/// <param name="cmd">要填充的SqlCommand对象</param>
/// <param name="strSqlOrProcedure">要执行的Sql语句或存储过程</param>
/// <param name="cmdType">命令类型</param>
protected static void PrepareCommand(SqlConnection conn, SqlCommand cmd, string strSqlOrProcedure, CommandType cmdType) {
cmd.Connection = conn;
cmd.CommandText = strSqlOrProcedure;
cmd.CommandType = cmdType;

if (Params != null && Values != null && Params.Length == Values.Length) {
for (int i = 0; i < Params.Length; i++) {
SqlParameter sp = new SqlParameter();
sp.ParameterName = Params[i];
sp.Value = Values[i];
sp.Direction = ParameterDirection.Input;

cmd.Parameters.Add(sp);
}
}
Params = null;
Values = null;
}


/// <summary>
/// 执行查询,只返回影响的行数
/// </summary>
/// <param name="strSqlOrProcedure">要执行的Sql语句或存储过程</param>
/// <param name="cmdType">命令类型</param>
/// <returns>返回影响的行数</returns>
public static int ExecNonQuery(string strSqlOrProcedure, CommandType cmdType) {
SqlCommand cmd = new SqlCommand();
SqlConnection conn = null;

try {
conn = new SqlConnection(ConnnectionString);
conn.Open();
PrepareCommand(conn, cmd, strSqlOrProcedure, cmdType);

int nCount = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();

return nCount;
}
catch (Exception e) {
ErrorString = e.Message;
conn.Close();

throw;
}
}

/// <summary>
/// 执行查询,只返回第一行第一列的数据
/// </summary>
/// <param name="strSqlOrProcedure">要执行的Sql语句或存储过程</param>
/// <param name="cmdType">命令类型</param>
/// <returns>返回第一行第一列的数据</returns>
public static object ExecScalar(string strSqlOrProcedure, CommandType cmdType) {
SqlCommand cmd = new SqlCommand();
SqlConnection conn = null;
object val = null;

try {
conn = new SqlConnection(ConnnectionString);
conn.Open();
PrepareCommand(conn, cmd, strSqlOrProcedure, cmdType);

val = cmd.ExecuteScalar();
cmd.Parameters.Clear();

return val;
}
catch (Exception e) {
ErrorString = e.Message;
conn.Close();

throw;
}
}

public static SqlDataReader ExecReader(string strSqlOrProcedure, CommandType cmdType) {
SqlCommand cmd = new SqlCommand();
SqlConnection conn = null;

try {
conn = new SqlConnection(ConnnectionString);
conn.Open();
PrepareCommand(conn, cmd, strSqlOrProcedure, cmdType);

SqlDataReader sdr = cmd.ExecuteReader();
cmd.Parameters.Clear();

return sdr;
}
catch (Exception e) {
ErrorString = e.Message;
conn.Close();

throw;
}
}

/// <summary>
/// 执行查询,并返回结果
/// </summary>
/// <param name="strSqlOrProcedure">要执行的Sql语句或存储过程</param>
/// <param name="cmdType">命令类型</param>
/// <returns>返回DataSet查询结果</returns>
public static DataSet ExecQuery(string strSqlOrProcedure, CommandType cmdType) {
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
SqlConnection conn = null;

try {
conn = new SqlConnection(ConnnectionString);
conn.Open();
PrepareCommand(conn, cmd, strSqlOrProcedure, cmdType);

SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(ds);
cmd.Parameters.Clear();

return ds;
}
catch (Exception e) {
ErrorString = e.Message;
conn.Close();

throw;
}
}
}
igelf 2009-10-19
  • 打赏
  • 举报
回复
没有.
sdjz1988sd 2009-10-19
  • 打赏
  • 举报
回复
自己写个 不久简化了
LutzMark 2009-10-19
  • 打赏
  • 举报
回复
自己写,用什么写什么
rnmmdb 2009-10-19
  • 打赏
  • 举报
回复
楼上的各位的都不错
但考虑实际的需求 一般公司里都会有自己封装的一套控件
具体情况具体而定!!!
加载更多回复(1)

62,025

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术交流专区
javascript云原生 企业社区
社区管理员
  • ASP.NET
  • .Net开发者社区
  • R小R
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

.NET 社区是一个围绕开源 .NET 的开放、热情、创新、包容的技术社区。社区致力于为广大 .NET 爱好者提供一个良好的知识共享、协同互助的 .NET 技术交流环境。我们尊重不同意见,支持健康理性的辩论和互动,反对歧视和攻击。

希望和大家一起共同营造一个活跃、友好的社区氛围。

试试用AI创作助手写篇文章吧