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

2009-10-19 10:03:02 1.4k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQLHelper.cs 背景这篇文章不对SqlHelper类中的具体方法和如何使用进行介绍，只是希望把博主了解的SqlHelper和查到的一些资料给大家分享一下。概念SqlHelper.cs是许多年前微软发布的一个使用ADO.Net方法对SQL Server数据库进行操作的封装类。是一个包含优化数据访问代码的.NET组件，它让我们不需要一次又一次重复写相同的数据访问代码，可以减少我们创建、测试、维护等自定义代码的数量。随后阅读详情

RT```

...全文

SqlHelper 用法总结对SQLHelper，还是有一点陌生的，但是大多数人都在使用，我就有一种想了解并使用的意愿，于是查了很多资料，发现一片不错的博客，放在下面，作为自己或读者使用的材料。一、SqlHelper 出场不是因为大家都在用SqlHelper所以才用，是因为连接数据库关闭数据阅读详情

囧

.NET社区 62,272 成员 668,968 内容

+ 关注 + 加入社区

切换为时间正序

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

_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);

            }

        }

    }

}

lovexilove 2009-10-19

打赏
举报

高手分享下吧！

以专业开发人员为伍 2009-10-19

打赏
举报

从来不用。自己写了个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;

            }

        }

    }