Mars 博客

public partial class _Default : System.Web.UI.Page
{
   
    protected void Page_Load(object sender, EventArgs e)
    {
    }
}

public class SQLDAL:IDAL
{
   public IList<Model> GetCategories()
    {
        IList<Model> categories = new List<Model>();
        using (SqlDataReader rdr = DBUtility.ExecuteReader(DBUtility.ConnectStr, CommandType.Text, "select * from Category", null))
        {
            while (rdr.Read())
            {
                Model cat = new Model(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2));
                categories.Add(cat);
            }
        }
        return categories;
    }

    public Model GetCategory(string categoryId)
    {
        Model category = null;
        SqlParameter parm = new SqlParameter("@categoryid", SqlDbType.VarChar, 10);
        parm.Value = categoryId;
        using (SqlDataReader rdr = DBUtility.ExecuteReader(DBUtility.ConnectStr, CommandType.Text, "select * from Category where categoryid=@categoryid", parm))
        {
            if (rdr.Read())
                category = new Model(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2));
            else
                category = new Model();
        }
        return category;
    }
}

public interface IDAL
{
    IList<Model> GetCategories();
    Model GetCategory(string categoryId);
}

public class Model
{
    private string categoryid;
    private string categoryname;
    private string categorytemp;

    public Model()
    {}

    public Model(string categoryid,string categoryname,string categorytemp) {
        this.categoryid = categoryid;
        this.categoryname = categoryname;
        this.categorytemp = categorytemp;
    }

    public string CategoryId
    {
        get { return this.categoryid; }
    }

    public string CategoryName
    {
        get { return this.categoryname; }
    }

    public string CategoryTemp
    {
        get { return this.categorytemp; }
    }
 
}

public abstract class DBUtility
{  
        public static readonly string ConnectStr = "server=.;uid=sa;pwd=";

        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
 
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) {

            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(connectionString)) {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }
 
        public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) {

            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
 
        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
        
        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);
 
            try {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch {
                conn.Close();
                throw;
            }
        }
 
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString)) {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }
 
        public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) {
            parmCache[cacheKey] = commandParameters;
        }
 
        public static SqlParameter[] GetCachedParameters(string cacheKey) {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }
 
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, 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 = cmdType;