大家帮忙看看这个SQLHelper是不是很垃圾！该怎么解决

2012-04-15

大家帮忙看看这个SQLHelper是不是很垃圾！！哪些需要改进哪里有什么问题C# codeusing Systemusing System.

大家帮忙看看这个SQLHelper是不是很垃圾！！
哪些需要改进哪里有什么问题

C# code

using System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient;using System.Data;using System.Configuration;using DataProvider;namespace DataProvider{    public static class DBHelper    {                private static SqlConnection connection;        public static SqlConnection Connection        {            get             {               // string connectionString = "Data Source=.;Initial Catalog=blogDB;Integrated Security=True";               string connectionString = ConfigurationManager.ConnectionStrings["gmwl"].ConnectionString;                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, params SqlParameter[] values)        {            SqlCommand cmd = new SqlCommand(sql, Connection);            cmd.Parameters.AddRange(values);            return cmd.ExecuteNonQuery();        }        public static bool EXECommand(string sql, params SqlParameter[] values)        {            SqlCommand cmd = new SqlCommand(sql, Connection);            cmd.Parameters.AddRange(values);            cmd.ExecuteNonQuery();            return true;        }        public static int GetScalar(string safeSql)        {            SqlCommand cmd = new SqlCommand(safeSql, Connection);            int result = Convert.ToInt32(cmd.ExecuteScalar());            return result;        }        public static int GetScalar(params SqlParameter[] values)        {            SqlCommand cmd = new SqlCommand();            cmd.Connection = Connection;            cmd.CommandText = "Pro_InsertOrder";            cmd.CommandType = CommandType.StoredProcedure;            cmd.Parameters.AddRange(values);            int result = Convert.ToInt32(cmd.ExecuteScalar());            return result;        }        public static int GetScalar(string sql, params SqlParameter[] values)        {            SqlCommand cmd = new SqlCommand(sql, Connection);            cmd.Parameters.AddRange(values);            int result = Convert.ToInt32(cmd.ExecuteScalar());            return result;        }        public static SqlDataReader GetReader(string safeSql)        {            try            {                SqlCommand cmd = new SqlCommand(safeSql, Connection);                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);                                return reader;                           }            catch (Exception)            {                connection.Close();                throw;            }                                     }        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)        {            try            {                SqlCommand cmd = new SqlCommand(sql, Connection);                cmd.Parameters.AddRange(values);                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);                                return reader;                           }            catch (Exception)            {                connection.Close();                throw;            }        }        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 DataTable GetDataSet(string sql, params SqlParameter[] values)        {            DataSet ds = new DataSet();            SqlCommand cmd = new SqlCommand(sql, Connection);            cmd.Parameters.AddRange(values);            SqlDataAdapter da = new SqlDataAdapter(cmd);            da.Fill(ds);            return ds.Tables[0];        }    }}

[解决办法]

探讨
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using DataProvider;
namespace DataProvider
{
public static class DBHelper
{

private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
// string connectionString = "Data Source=.;Initial Catalog=blogDB;Integrated Security=True";
string connectionString = ConfigurationManager.ConnectionStrings["gmwl"].ConnectionString;
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 sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}

public static bool EXECommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
cmd.ExecuteNonQuery();
return true;
}

public static int GetScalar(params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = "Pro_InsertOrder";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());

return result;
}

public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{

try
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

return reader;

}
catch (Exception)
{
connection.Close();
throw;
}
}

public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
}
}

热点排行

asp.net

大家帮忙看看这个SQLHelper是不是很垃圾！该怎么解决