大家帮我看下这个DBHelper类写对了没有???

Question

/// <summary>
        /// 增加  更新  删除方法
        /// </summary>
        /// <param name="sql">SQL语句 或 存储过程</param>
        /// <param name="sqlpar">参数</param>
        /// <param name="type">操作命令类型</param>
        /// <param name="index">输出参数的索引</param>
        /// <returns></returns>
        public static object InsertOrUpdateOrDelete(string sql,List<SqlParameter> sqlpar,CommandType type,int index)
        {
            using (con=new SqlConnection(conStr))
            {
                try
                {
                    com = new SqlCommand(sql,con);
                    com.CommandType = type;
                    com.Parameters.AddRange(sqlpar.ToArray());
                    com.Parameters[index].Direction = ParameterDirection.Output;
                    if (con.State != ConnectionState.Open)
                        con.Open();
                    com.ExecuteNonQuery();
                    if (index != -1)
                        return com.Parameters[index].Value;
                    else
                        return null;
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    con.Close();
                }
            }
        }

Answer 1

删除，更新，增加 是可以写在一起的 他们都涉及到了数据的改动 执行的时候用 cmd.ExcuteNoQuery();

Answer 2

/// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="sqlparams"></param>
        public void UpdateData(string spName, List<SqlParameter> sqlparams)
        {            try
            {
                using (dbConnection = new SqlConnection(connectionString))
                {
                    dbConnection.Open();
                    dbCommand = new SqlCommand(spName, dbConnection);
                    dbCommand.CommandType = CommandType.StoredProcedure;                    foreach (SqlParameter sqlparam in sqlparams)
                    {
                        dbCommand.Parameters.Add(sqlparam);
                    }
                    dbCommand.ExecuteNonQuery();                    dbCommand.Dispose();
                    dbConnection.Close();
                    dbConnection.Dispose();;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
可以看看这个方法....

Answer 3

上面这个方法改动一下是不是更好一些啊!
如如再传一个CommandType参数进来是不是更灵活一些,那样就既可以执行SQL语句也可以执行存储过程了呀
还有一个地方是
foreach (SqlParameter sqlparam in sqlparams)
{
    dbCommand.Parameters.Add(sqlparam);
}
不是直接可以用dbCommand.Parameters.AddRange(sqlparam.ToArray())就可以了吗

Answer 4

我想写一个可以执行SQL语句而且还可以执行存储过程（并且还可以带输出参数）的增，删，改的方法啊
我觉得写在一个方法里方便一点

Answer 5

 /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        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;
                    }
                }
            }
        }
这个本来就支持增加修改删除了

Answer 6

代码有问题
using (dbConnection = new SqlConnection(connectionString))
                {
                    dbConnection.Open();
                    dbCommand = new SqlCommand(spName, dbConnection);
                    dbCommand.CommandType = CommandType.StoredProcedure;                    foreach (SqlParameter sqlparam in sqlparams)
                    {
                        dbCommand.Parameters.Add(sqlparam);
                    }
                    dbCommand.ExecuteNonQuery();                    dbCommand.Dispose();
                    dbConnection.Close();
                    dbConnection.Dispose();
                }
using block语句结束之前，会自动调用SqlConnection.Dispose方法。
没必要手动调用close和dispose方法；事实上，在dispose方法中已经调用了close方法。
using block中可以嵌套使用using block
using(dbCommand = new SqlCommand(spName, dbConnection))
{}