帮忙看看这个数据库的访问类是否有问题？

[panzi667]

请大家帮忙，帮忙看看这个数据库的访问类是否有问题？
如果是一个操作基本没有什么问题，但是同时有很多人使用的时候就报错。
以下是代码


public class SQLServerHelper
{
private static string conString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

public static void AddParamInCmd(SqlCommand cmd, string paramName, SqlDbType type, int size, object value)
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = paramName;
parameter.SqlDbType = type;
parameter.Size = size;
parameter.Value = value;
cmd.Parameters.Add(parameter);
}

private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
return command;
}

private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
}

public static int ExecuteNonQuery(SqlTransaction transaction, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandText, commandParameters);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return num;
}

public static SqlDataReader ExecuteReader(string sqlString)
{
SqlDataReader reader2;
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(sqlString, connection);
SqlDataReader reader = null;
try
{
connection.Open();
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
reader2 = reader;
}
catch (SqlException exception)
{
connection.Close();
throw new Exception(exception.Message);
}
finally
{
if (reader == null)
{
command.Dispose();
connection.Close();
}
}
return reader2;
}

public static SqlDataReader ExecuteReader(string sqlString, params SqlParameter[] cmdParms)
{
SqlDataReader reader2;
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
SqlDataReader reader = null;
try
{
PrepareCommand(cmd, conn, null, sqlString, cmdParms);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
reader2 = reader;
}
catch (SqlException exception)
{
conn.Close();
throw new Exception(exception.Message);
}
finally
{
if (reader == null)
{
cmd.Dispose();
conn.Close();
}
}
return reader2;
}

public static int ExecuteSql(string sqlString)
{
int num2 = 0;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sqlString, connection))
{
try
{
connection.Open();
num2 = command.ExecuteNonQuery();
}
catch (SqlException exception)
{
connection.Close();
throw new Exception(exception.Message);
}
finally
{
command.Dispose();
connection.Close();
}
}
}
return num2;
}

public static int ExecuteSql(string sqlString, string content)
{
int num2;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(sqlString, connection);
SqlParameter parameter = new SqlParameter("@content", SqlDbType.NText);
parameter.Value = content;
command.Parameters.Add(parameter);
try
{
connection.Open();
num2 = command.ExecuteNonQuery();
}
catch (SqlException exception)
{
throw new Exception(exception.Message);
}
finally
{
command.Dispose();
connection.Close();
}
}
return num2;
}

public static int ExecuteSql(string sqlString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand())
{
try
{
PrepareCommand(command, connection, null, sqlString, cmdParms);
int num = command.ExecuteNonQuery();
command.Parameters.Clear();
return num;
}
catch (SqlException exception)
{
throw new Exception(exception.Message);
}
finally
{
command.Dispose();
connection.Close();
}
}
}
}

public static int ExecuteSqlInsertImg(string sqlString, byte[] fs)
{
int num2;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(sqlString, connection);
SqlParameter parameter = new SqlParameter("@fs", SqlDbType.Image);
parameter.Value = fs;
command.Parameters.Add(parameter);
try
{
connection.Open();
num2 = command.ExecuteNonQuery();
}
catch (SqlException exception)
{
throw new Exception(exception.Message);
}
finally
{
command.Dispose();
connection.Close();
}
}
return num2;
}

public static void ExecuteSqlTran(ArrayList sqlStringList)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand();
command.Connection = connection;
SqlTransaction transaction = connection.BeginTransaction();
command.Transaction = transaction;
try
{
for (int i = 0; i < sqlStringList.Count; i++)
{
string str = sqlStringList[i].ToString();
if (str.Trim().Length > 1)
{
command.CommandText = str;
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch (SqlException exception)
{
transaction.Rollback();
throw new Exception(exception.Message);
}
finally
{
command.Dispose();
connection.Close();
}
}
}

[panzi667]

public static void ExecuteSqlTran(Hashtable sqlStringList)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
foreach (DictionaryEntry entry in sqlStringList)
{
string cmdText = entry.Key.ToString();
SqlParameter[] cmdParms = (SqlParameter[])entry.Value;
PrepareCommand(cmd, connection, transaction, cmdText, cmdParms);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
}


public static bool ExecuteSqlTran2(Hashtable sqlStringList)
{
bool isCommit = false;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
foreach (DictionaryEntry entry in sqlStringList)
{
string cmdText = entry.Key.ToString();
SqlParameter[] cmdParms = (SqlParameter[])entry.Value;
PrepareCommand(cmd, connection, transaction, cmdText, cmdParms);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
isCommit = true;
transaction.Commit();
}
catch
{
isCommit = false;
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
return isCommit;
}

public static bool Exists(string sqlString, params SqlParameter[] cmdParms)
{
int num;
object single = GetSingle(sqlString, cmdParms);
if (object.Equals(single, null) || object.Equals(single, DBNull.Value))
{
num = 0;
}
else
{
num = int.Parse(single.ToString());
}
if (num == 0)
{
return false;
}
return true;
}

public static int GetMaxID(string fieldName, string tableName)
{
object single = GetSingle("select max(" + fieldName + ")+1 from " + tableName);
if (single == null)
{
return 1;
}
return int.Parse(single.ToString());
}

public static object GetSingle(string sqlString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sqlString, connection))
{
try
{
connection.Open();
object objA = command.ExecuteScalar();
if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
{
return null;
}
return objA;
}
catch (SqlException exception)
{
connection.Close();
throw new Exception(exception.Message);
}
finally
{
command.Dispose();
connection.Close();
}
}
}
}

public static object GetSingle(string sqlString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand())
{
try
{
PrepareCommand(command, connection, null, sqlString, cmdParms);
object objA = command.ExecuteScalar();
command.Parameters.Clear();
if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
{
return null;
}
return objA;
}
catch (SqlException exception)
{
throw new Exception(exception.Message);
}
finally
{
command.Dispose();
connection.Close();
}
}
}
}

[panzi667]

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)
{
cmd.Parameters.Add(parameter);
}
}
}

public static DataSet Query(string sqlString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
try
{
connection.Open();
new SqlDataAdapter(sqlString, connection).Fill(dataSet, "ds");
}
catch (SqlException exception)
{
throw new Exception(exception.Message);
}
finally
{
connection.Close();
}
return dataSet;
}
}

public static DataSet Query(string sqlString,string strTable)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
try
{
connection.Open();
new SqlDataAdapter(sqlString, connection).Fill(dataSet, strTable);
}
catch (SqlException exception)
{
throw new Exception(exception.Message);
}
finally
{
connection.Close();
}
return dataSet;
}
}

public static DataSet Query(string sqlString, params SqlParameter[] cmdParms)
{
DataSet set2;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, sqlString, cmdParms);
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
DataSet dataSet = new DataSet();
try
{
adapter.Fill(dataSet, "ds");
cmd.Parameters.Clear();
}
catch (SqlException exception)
{
throw new Exception(exception.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
set2 = dataSet;
}
}
return set2;
}

public static DataSet Query(string sqlString,string strTable, params SqlParameter[] cmdParms)
{
DataSet set2;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, sqlString, cmdParms);
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
DataSet dataSet = new DataSet();
try
{
adapter.Fill(dataSet, strTable);
cmd.Parameters.Clear();
}
catch (SqlException exception)
{
throw new Exception(exception.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
set2 = dataSet;
}
}
return set2;
}

public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
SqlDataReader reader2;
SqlConnection connection = new SqlConnection(connectionString);
try
{
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
reader2 = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException exception)
{
throw new Exception(exception.Message);
}
finally
{
connection.Close();
}
return reader2;
}

public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
int num2;
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
int num = (int)command.Parameters["ReturnValue"].Value;
num2 = num;
}
catch (SqlException exception)
{
throw new Exception(exception.Message);
}
finally
{
connection.Close();
}
}
return num2;
}

public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
adapter.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}

public static SqlConnection Connection
{
get
{
SqlConnection connection = new SqlConnection(connectionString);
if (connection == null)
{
connection.Open();
return connection;
}
if (connection.State == ConnectionState.Closed)
{
connection.Open();
return connection;
}
if (connection.State == ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}

private static string connectionString
{
get
{
return conString;
}
}
}

[bdmh]

谁会费工夫给你看这个，很多人报错，先看看明白错误信息，然后就看看是不是多人同时操作了数据库，导致主键重复等问题

[ycproc]

换个DBhelper
或者把 错误报错 信息 贴出来

[天下如山]

娃 好长！

[天下如山]

按道理来说 应该不是这的问题
按照1楼讲的 很有可能。

[__天涯寻梦]

public static int ExecuteSql(string sqlString)
{
    int num2 = 0;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand(sqlString, connection))
        {
            try
            {
                connection.Open();
                num2 = command.ExecuteNonQuery();
            }
            catch (SqlException exception)
            {
                connection.Close();
                throw new Exception(exception.Message);
            }
            finally
            {
                command.Dispose();
                connection.Close();
            }
        }
    }
    return num2;
}

这种写法就很不好

public static int ExecuteSql(string sqlString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    using (SqlCommand command = new SqlCommand(sqlString, connection))
    {
        connection.Open();
        return command.ExecuteNonQuery();
    }
}

这样就行了

[__天涯寻梦]

有了 using 就不需要再 try finally 来关闭连接了，Dispose 包含了 Close 的功能

[手抓宝]

把你的数据库访问类做成单例模式。否则都是静态的东西，所有人一起用，很容易出现连接对象没关闭或者被其他人给关掉的情况

你的GetSingle这个方法不知道是不是为了实现单例，但是怎么看都不对。

[熙风]

楼主，你好歹把错误信息贴出来啊，，
这么多怎么看啊，，
你把别人当成VS编译器了？

[panzi667]

以下是报错提示，但是刷新页面后又表现为正常！！！



“/”应用程序中的服务器错误。
--------------------------------------------------------------------------------

未将对象引用设置到对象的实例。
说明: 执行当前 Web 请求期间，出现未处理的异常。请检查堆栈跟踪信息，以了解有关该错误以及代码中导致错误的出处的详细信息。

异常详细信息: System.NullReferenceException: 未将对象引用设置到对象的实例。

源错误:

执行当前 Web 请求期间生成了未处理的异常。可以使用下面的异常堆栈跟踪信息确定有关异常原因和发生位置的信息。

堆栈跟踪:


[NullReferenceException: 未将对象引用设置到对象的实例。]
AGu.SQLServerDAL.Member.MemberAccount.GetModel(String UserId) in C:\我的开发\程序代码\AGu.SQLServerDAL\Member\MemberAccount.cs:326
AGu.BLL.Member.MemberAccount.GetModel(String UserId) in C:\我的开发\程序代码\AGu.BLL\Member\MemberAccount.cs:113
AGu.Web.main_login.Setinfo() in C:\我的开发\程序代码\AGu.Web\main_login.aspx.cs:88
AGu.Web.main_login.Page_Load(Object sender, EventArgs e) in C:\我的开发\程序代码\AGu.Web\main_login.aspx.cs:54
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061




--------------------------------------------------------------------------------
版本信息: Microsoft .NET Framework 版本:2.0.50727.42; ASP.NET 版本:2.0.50727.42

[panzi667]

主要是奇怪于页面报错，但是刷新一下又变成正常的

[__天涯寻梦]

不一定是这个 SqlServerHelper 的问题，错误显示
AGu.SQLServerDAL.Member.MemberAccount.GetModel(String UserId)
这个方法里，有一个类型的实例为 null ，但是还是调用了自身的方法或属性，把这个方法贴出来看看吧

[panzi667]

/// <summary>
/// 得到一个对象实体
/// </summary>
/// <param name="UserId"></param>
/// <returns></returns>
public AGu.Model.Member.MemberAccount GetModel(string UserId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select UserID, UserState, UserRole, LoginTime, LoginIp, LoginTimes, UserName, PassWord, Tel, QYID, UserType, UserPost, isJXKH, isTJGZ ");
strSql.Append(" from " + Pre + "memberaccount ");
strSql.Append(" where UserID=@UserID ");
SqlParameter[] parameters = {
new SqlParameter("@UserID", SqlDbType.NVarChar,10) };
parameters[0].Value = UserId;


AGu.Model.Member.MemberAccount model = new AGu.Model.Member.MemberAccount();
DataSet ds = AGuFramework.DataBase.SQLServerHelper.Query(strSql.ToString(),"dsUser", parameters);
if (ds != null)
{
if (ds.Tables["dsUser"].Rows.Count > 0)
{
model.UserID = ds.Tables["dsUser"].Rows[0]["UserID"].ToString();
if (ds.Tables["dsUser"].Rows[0]["UserState"].ToString() != "")
{
model.UserState = int.Parse(ds.Tables["dsUser"].Rows[0]["UserState"].ToString());
}
model.UserRole = ds.Tables["dsUser"].Rows[0]["UserRole"].ToString();
if (ds.Tables["dsUser"].Rows[0]["LoginTime"].ToString() != "")
{
model.LoginTime = DateTime.Parse(ds.Tables["dsUser"].Rows[0]["LoginTime"].ToString());
}
model.LoginIp = ds.Tables["dsUser"].Rows[0]["LoginIp"].ToString();
if (ds.Tables["dsUser"].Rows[0]["LoginTimes"].ToString() != "")
{
model.LoginTimes = int.Parse(ds.Tables["dsUser"].Rows[0]["LoginTimes"].ToString());
}
model.UserName = ds.Tables["dsUser"].Rows[0]["UserName"].ToString();
model.PassWord = ds.Tables["dsUser"].Rows[0]["PassWord"].ToString();
model.Tel = ds.Tables["dsUser"].Rows[0]["Tel"].ToString();
model.QYID = ds.Tables["dsUser"].Rows[0]["QYID"].ToString();
if (ds.Tables["dsUser"].Rows[0]["UserType"].ToString() != "")
{
model.UserType = int.Parse(ds.Tables["dsUser"].Rows[0]["UserType"].ToString());
}
model.UserPost = ds.Tables["dsUser"].Rows[0]["UserPost"].ToString();
if (ds.Tables["dsUser"].Rows[0]["isJXKH"].ToString() != "")
{
model.isJXKH = int.Parse(ds.Tables["dsUser"].Rows[0]["isJXKH"].ToString());
}
if (ds.Tables["dsUser"].Rows[0]["isTJGZ"].ToString() != "")
{
model.isTJGZ = int.Parse(ds.Tables["dsUser"].Rows[0]["isTJGZ"].ToString());
}

return model;
}
else
{
return null;
}
}
else
{
return null;
}
}

[panzi667]

帮忙看看~~

[panzi667]

掉贴？？？

[qldsrx]

错误在这里，你只判断了ds是否为空，却没有判断是否存在那个dsUser表，当返回0行记录时，不是判断Rows.Count>0,而是判断那个Tables.Count>0，因为连表都不会给你创建。

  if (ds != null)
  {   
  if (ds.Tables["dsUser"].Rows.Count > 0)

另外你看过我这个帖子吗：http://topic.csdn.net/u/20120412/10/42835527-3690-4775-a56e-b98426ada728.html
用这个来代替你的那个Helper，效率将非常高，而且操作也比你这个简单得多了。

[__天涯寻梦]

19楼说的是，ds 根本不需要判断，它一定不会是 null，另外 DataAdapter.Fill 方法有重载直接填充 DataTable ，没必要用 DataSet

[panzi667]

主要郁闷的地方是，小部分人使用是正常的，但是很多人使用的时候就出现错误咯。