mvcPager2.0_51aspx

于 2007-10-31 20:28:00 发布
阅读量614 收藏
点赞数
文章标签： parameters dataset string null exception object

 using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Web;
using System.Web.Caching;

namespace BLL
{
    public class Caches
    {
        /// <summary>
        /// 建立缓存
        /// </summary>
        public static object TryAddCache(string key, object value, CacheItemPriority priority)
        {
            if (HttpRuntime.Cache[key] == null && value != null)
                return HttpRuntime.Cache.Add(key, value, null, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, priority, null);
            else
                return null;
        }

        /// <summary>
        /// 建立定时不访问便移除的缓存
        /// </summary>
        public static object TryAddCache(string key, object value, TimeSpan slidingExpiration, CacheItemPriority priority)
        {
            if (HttpRuntime.Cache[key] == null && value != null)
                return HttpRuntime.Cache.Add(key, value, null, Cache.NoAbsoluteExpiration, slidingExpiration, priority, null);
            else
                return null;
        }

        /// <summary>
        /// 建立缓存，并在移除时执行事件
        /// </summary>
        public static object TryAddCache(string key, object value, DateTime absoluteExpiration, TimeSpan slidingExpiration, CacheItemPriority priority, CacheItemRemovedCallback onRemovedCallback)
        {
            if (HttpRuntime.Cache[key] == null && value != null)
                return HttpRuntime.Cache.Add(key, value, null, absoluteExpiration, slidingExpiration, priority, onRemovedCallback);
            else
                return null;
        }


        /// <summary>
        /// 移除缓存
        /// </summary>
        public static object TryRemoveCache(string key)
        {
            if (HttpRuntime.Cache[key] != null)
                return HttpRuntime.Cache.Remove(key);
            else
                return null;
        }

        /// <summary>
        /// 移除键中带某关键字的缓存
        /// </summary>
        public static void RemoveMultiCache(string keyInclude)
        {
            IDictionaryEnumerator CacheEnum = HttpRuntime.Cache.GetEnumerator();
            while (CacheEnum.MoveNext())
            {
                if (CacheEnum.Key.ToString().IndexOf(keyInclude.ToString()) >= 0)
                    HttpRuntime.Cache.Remove(CacheEnum.Key.ToString());
            }
        }

        /// <summary>
        /// 移除所有缓存
        /// </summary>
        public static void RemoveAllCache()
        {
            IDictionaryEnumerator CacheEnum = HttpRuntime.Cache.GetEnumerator();
            while (CacheEnum.MoveNext())
            {
                HttpRuntime.Cache.Remove(CacheEnum.Key.ToString());
            }
        }
    }
}

 

using System;
using System.Data;
using DALFactory;
using IDAL;
using Model;
using System.Collections.Generic;
using System.Text;
using System.Web;

namespace BLL
{
 /// <summary>
 /// 业务逻辑类 Category
 /// </summary>
 public class Category
 {
  private readonly ICategory dal = DataAccess.CreateCategory();
  private readonly string basicKey = "Category_";
  public Category()
  { }
  #region  成员方法

  /// <summary>
  /// 增加一条数据
  /// </summary>
  public Guid Add(Model.Category model)
  {
   Guid data = dal.Add(model);
   RemoveCountCache();
   RemoveMaxCache();
   RemoveListCache();
   return data;
  }

  /// <summary>
  /// 增加多条数据
  /// </summary>
  public void Add(List<Model.Category> l)
  {
   foreach (Model.Category model in l)
   {
    dal.Add(model);
   }
   RemoveCountCache();
   RemoveMaxCache();
   RemoveListCache();
  }

  /// <summary>
  /// 更新一条数据
  /// </summary>
  public void Update(Model.Category model)
  {
   dal.Update(model);
   RemoveModelCache(model.Id);
   RemoveListCache();
  }

  /// <summary>
  /// 更新多条数据
  /// </summary>
  public void Update(List<Model.Category> l)
  {
   foreach (Model.Category model in l)
   {
    dal.Update(model);
    RemoveModelCache(model.Id);
   }
   RemoveListCache();
  }

  /// <summary>
  /// 删除一条数据
  /// </summary>
  public void Delete(Guid Id)
  {
   dal.Delete(Id);
   RemoveCountCache();
   RemoveExistsCache(Id);
   RemoveMaxCache();
   RemoveModelCache(Id);
   RemoveListCache();
  }

  /// <summary>
  /// 删除多条数据
  /// </summary>
  public void Delete(List<Guid> l)
  {
   foreach (Guid Id in l)
   {
    dal.Delete(Id);
    RemoveExistsCache(Id);
    RemoveModelCache(Id);
   }
   RemoveMaxCache();
   RemoveListCache();
  }

  /// <summary>
  /// 是否存在该记录
  /// </summary>
  public bool Exists(Guid Id)
  {
   string key = basicKey + "Exists_" + Id.ToString();
   if (HttpRuntime.Cache[key] != null)
    return (bool)HttpRuntime.Cache[key];
   else
   {
    bool data = dal.Exists(Id);
    Caches.TryAddCache(key, data, TimeSpan.FromMinutes(20), System.Web.Caching.CacheItemPriority.High);
    return data;
   }
  }

  /// <summary>
  /// 得到一个对象实体
  /// </summary>
  public Model.Category GetModel(Guid Id)
  {
   string key = basicKey + "Model_" + Id.ToString();
   if (HttpRuntime.Cache[key] != null)
    return (Model.Category)HttpRuntime.Cache[key];
   else
   {
    Model.Category data = dal.GetModel(Id);
    Caches.TryAddCache(key, data, TimeSpan.FromMinutes(20), System.Web.Caching.CacheItemPriority.Normal);
    return data;
   }
  }

  /// <summary>
  /// 得到数据总条数
  /// </summary>
  public int GetCount()
  {
   string key = basicKey + "Counts_All";
   if (HttpRuntime.Cache[key] != null)
    return (int)HttpRuntime.Cache[key];
   else
   {
    DataSet ds = dal.GetList("count(*)", null, null);
    int data = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
    Caches.TryAddCache(key, data, System.Web.Caching.CacheItemPriority.Normal);
    return data;
   }
  }

  /// <summary>
  /// 获得数据列表，不建议直接使用此方法，请根据业务逻辑重写
  /// </summary>
  public List<Model.Category> GetList(string strWhere, string strOrderby)
  {
   string key = basicKey + "List_" + strWhere.ToString();
   if (HttpRuntime.Cache[key] != null)
    return (List<Model.Category>)HttpRuntime.Cache[key];
   else
   {
    List<Model.Category> data = dal.GetList(strWhere, strOrderby) as List<Model.Category>;
    Caches.TryAddCache(key, data, System.Web.Caching.CacheItemPriority.Normal);
    return data;
   }
  }

  /// <summary>
  /// 获得数据列表
  /// </summary>
  public List<Model.Category> GetAllList()
  {
   string key = basicKey + "List_All";
   if (HttpRuntime.Cache[key] != null)
    return (List<Model.Category>)HttpRuntime.Cache[key];
   else
   {
    List<Model.Category> data = dal.GetList(null, null) as List<Model.Category>;
    Caches.TryAddCache(key, data, System.Web.Caching.CacheItemPriority.Normal);
    return data;
   }
  }

  /// <summary>
  /// 分页获取数据，不建议直接使用此方法，请根据业务逻辑重写
  /// </summary>
  public List<Model.Category> GetList(int pageSize, int pageIndex, string fldSort, bool Sort, string strCondition, out int pageCount, out int Counts)
  {
   string m_basicKey = basicKey + "List_" + fldSort + "_" + Sort.ToString() + "_" + strCondition + "_" + pageSize.ToString() + "_";
   string key = m_basicKey + pageIndex.ToString();
   string pageCountKey = m_basicKey + "_pageCount";
   string countKey = m_basicKey + "_Counts";

   if (HttpRuntime.Cache[key] != null && HttpRuntime.Cache[pageCountKey] != null && HttpRuntime.Cache[countKey] != null)
   {
    pageCount = (int)HttpRuntime.Cache[pageCountKey];
    Counts = (int)HttpRuntime.Cache[countKey];
    return (List<Model.Category>)HttpRuntime.Cache[key];
   }
   else
   {
    List<Model.Category> data = dal.GetList(pageSize, pageIndex, fldSort, Sort, strCondition, out pageCount, out Counts) as List<Model.Category>;
    if (pageIndex <= 3)
    {
     Caches.TryAddCache(key, data, System.Web.Caching.CacheItemPriority.High);
     Caches.TryAddCache(pageCountKey, pageCount, System.Web.Caching.CacheItemPriority.High);
     Caches.TryAddCache(countKey, Counts, System.Web.Caching.CacheItemPriority.High);
    }
    else
    {
     Caches.TryAddCache(key, data, System.Web.Caching.CacheItemPriority.Normal);
     Caches.TryAddCache(pageCountKey, pageCount, System.Web.Caching.CacheItemPriority.Normal);
     Caches.TryAddCache(countKey, Counts, System.Web.Caching.CacheItemPriority.Normal);
    }
    return data;
   }
  }

        /// <summary>
        /// 分页获取数据
        /// </summary>
        public List<Model.Category> GetList(int pageSize, int pageIndex, out int pageCount, out int Counts)
        {
            string m_basicKey = basicKey + "List_" + pageSize.ToString() + "_";
            string key = m_basicKey + pageIndex.ToString();
            string pageCountKey = m_basicKey + "_pageCount";
            string countKey = m_basicKey + "_Counts";

            if (HttpRuntime.Cache[key] != null && HttpRuntime.Cache[pageCountKey] != null && HttpRuntime.Cache[countKey] != null)
            {
                pageCount = (int)HttpRuntime.Cache[pageCountKey];
                Counts = (int)HttpRuntime.Cache[countKey];
                return (List<Model.Category>)HttpRuntime.Cache[key];
            }
            else
            {
                List<Model.Category> data = dal.GetList(pageSize, pageIndex, "CreateTime", true, string.Empty, out pageCount, out Counts) as List<Model.Category>;
                if (pageIndex <= 3)
                {
                    Caches.TryAddCache(key, data, System.Web.Caching.CacheItemPriority.High);
                    Caches.TryAddCache(pageCountKey, pageCount, System.Web.Caching.CacheItemPriority.High);
                    Caches.TryAddCache(countKey, Counts, System.Web.Caching.CacheItemPriority.High);
                }
                else
                {
                    Caches.TryAddCache(key, data, System.Web.Caching.CacheItemPriority.Normal);
                    Caches.TryAddCache(pageCountKey, pageCount, System.Web.Caching.CacheItemPriority.Normal);
                    Caches.TryAddCache(countKey, Counts, System.Web.Caching.CacheItemPriority.Normal);
                }
                return data;
            }
        }
  #endregion  成员方法

  #region 缓存操作

  private void RemoveMaxCache()
  {
   Caches.TryRemoveCache(basicKey + "Max");
  }

  private void RemoveExistsCache(Guid Id)
  {
   Caches.TryRemoveCache(basicKey + "Exists_" + Id.ToString());
  }

  private void RemoveModelCache(Guid Id)
  {
   Caches.TryRemoveCache(basicKey + "Model_" + Id.ToString());
  }

  private void RemoveCountCache()
  {
   Caches.RemoveMultiCache(basicKey + "Counts_");
  }

  private void RemoveListCache()
  {
   Caches.RemoveMultiCache(basicKey + "List_");
  }

  #endregion

 }
}

 

using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using IDAL;

namespace DALFactory
{
 public class DataAccess
 {
  private static readonly string path = ConfigurationManager.AppSettings["WebDAL"];

  public static ICategory CreateCategory()
  {
   string className = path + ".Category";
   return (ICategory)Assembly.Load(path).CreateInstance(className);
  }
 }
}


using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace DBUtility
{
    /// <summary>
    /// 数据访问基础类(基于SQLServer)
    /// </summary>
    public abstract class SqlHelper
    {
        //数据库连接字符串(web.config来配置)
        private static string connectionString = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;

        public SqlHelper()
        { }

        /// <summary>
        /// 通用分页存储过程
        /// </summary>
        /// <param name="tblName">要显示的表或多个表的连接</param>
        /// <param name="fldName">要显示的字段列表,可为Null,表示*</param>
        /// <param name="pageSize">每页显示的记录个数</param>
        /// <param name="page">要显示那一页的记录</param>
        /// <param name="fldSort">排序字段列表或条件</param>
        /// <param name="Sort">排序方法，False为升序，True为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')</param>
        /// <param name="strCondition">查询条件,不需where,以And开始,可为Null,表示""</param>
        /// <param name="ID">主表的主键</param>
        /// <param name="Disk">是否添加查询字段的 DISTINCT 默认False不添加/True添加</param>
        /// <param name="pageCount">查询结果分页后的总页数</param>
        /// <param name="Count">查询到的记录数</param>
        /// <param name="strSql">最后返回的SQL语句</param>
        /// <returns>查询当前页的数据集</returns>
        public static DataSet PageList(string tblName, string fldName, int pageSize, int pageIndex,
            string fldSort, bool Sort, string strCondition, string ID, bool Dist,
            out int pageCount, out int Counts, out string strSql)
        {
            SqlParameter[] parameters ={ new SqlParameter("@tblName",SqlDbType.NVarChar,200),
                new SqlParameter("@fldName",SqlDbType.NVarChar,500),
                new SqlParameter("@pageSize",SqlDbType.Int),
                new SqlParameter("@page",SqlDbType.Int),
                new SqlParameter("@fldSort",SqlDbType.NVarChar,200),
                new SqlParameter("@Sort",SqlDbType.Bit),
                new SqlParameter("@strCondition",SqlDbType.NVarChar,1000),
                new SqlParameter("@ID",SqlDbType.NVarChar,150),
                new SqlParameter("@Dist",SqlDbType.Bit),
                new SqlParameter("@pageCount",SqlDbType.Int),
                new SqlParameter("@Counts",SqlDbType.Int),
                new SqlParameter("@strSql",SqlDbType.NVarChar,1000)};

            parameters[0].Value = tblName;
            parameters[1].Value = (fldName == null) ? "*" : fldName;
            parameters[2].Value = (pageSize == 0) ? int.Parse(ConfigurationManager.AppSettings["PageSize"]) : pageSize;
            parameters[3].Value = pageIndex;
            parameters[4].Value = fldSort;
            parameters[5].Value = Sort;
            parameters[6].Value = strCondition == null ? "" : strCondition;
            parameters[7].Value = ID;
            parameters[8].Value = Dist;
            parameters[9].Direction = ParameterDirection.Output;
            parameters[10].Direction = ParameterDirection.Output;
            parameters[11].Direction = ParameterDirection.Output;

            DataSet ds = RunProcedure("PageList", parameters, "ds");

            pageCount = (int)parameters[9].Value;
            Counts = (int)parameters[10].Value;
            strSql = parameters[11].Value.ToString();
            return ds;
        }

        #region 执行简单SQL语句

        /// <summary>
        /// 取得INT型表主键的最大值
        /// </summary>
        /// <param name="p"></param>
        /// <param name="p_2"></param>
        /// <returns></returns>
        public static int GetMaxID(string field, string table)
        {
            string strSql = "select max(" + field + ") from " + table + "";
            DataSet ds = Query(strSql);
            if (ds.Tables[0].Rows.Count > 0 && !string.IsNullOrEmpty(ds.Tables[0].Rows[0][0].ToString()))
            {
                return int.Parse(ds.Tables[0].Rows[0][0].ToString());
            }
            else
                return 0;
        }

        /// <summary>
        ///  检测一个记录是否存在(SqlParameter语句方式)
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            DataSet ds = Query(strSql, cmdParms);
            return int.Parse(ds.Tables[0].Rows[0][0].ToString()) > 0;
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>  
        public static void ExecuteSqlTran(List<string> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, string content)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(SQLString, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(strSQL, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
                myParameter.Value = fs;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString)
        {
            if (SQLString != null && SQLString.Trim() != "")
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        connection.Open();
                        SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                        command.Fill(ds, "ds");
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
            else
            {
                return null;
            }
        }

        #endregion 执行简单SQL语句

        #region 执行带参数的SQL语句

        /// <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 new Exception(E.Message);
                    }
                }
            }
        }


        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                            trans.Commit();
                        }
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SqlDataReader myReader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }

        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }


        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;//cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        #endregion 执行带参数的SQL语句

        #region 存储过程操作

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader();
            return returnReader;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数  
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                connection.Close();
                return result;
            }
        }

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值) 
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        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;
        }

        #endregion 存储过程操作

        #region 由Object取值

        /// <summary>
        /// 取得Int值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static int GetInt(object obj)
        {
            if (obj.ToString() != "")
                return int.Parse(obj.ToString());
            else
                return 0;
        }

        /// <summary>
        /// 取得Decimal值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static decimal GetDecimal(object obj)
        {
            if (obj.ToString() != "")
                return decimal.Parse(obj.ToString());
            else
                return 0;
        }

        /// <summary>
        /// 取得Guid值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static Guid GetGuid(object obj)
        {
            if (obj.ToString() != "")
                return new Guid(obj.ToString());
            else
                return Guid.Empty;
        }

        /// <summary>
        /// 取得DateTime值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static DateTime GetDateTime(object obj)
        {
            if (obj.ToString() != "")
                return DateTime.Parse(obj.ToString());
            else
                return DateTime.MinValue;
        }

        /// <summary>
        /// 取得bool值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static bool GetBool(object obj)
        {
            if (obj.ToString() == "1" || obj.ToString().ToLower() == "true")
                return true;
            else
                return false;
        }

        /// <summary>
        /// 取得byte[]
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static Byte[] GetByte(object obj)
        {
            if (obj.ToString() != "")
            {
                return (Byte[])obj;
            }
            else
                return null;
        }

        /// <summary>
        /// 取得string值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string GetString(object obj)
        {
            return obj.ToString();
        }

        #endregion
    }
}

using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;

namespace IDAL
{
 /// <summary>
 /// 接口层 ICategory
 /// </summary>
 public interface ICategory
 {
  #region  成员方法
  /// <summary>
  /// 增加一条数据
  /// </summary>
  Guid Add(Model.Category model);

  /// <summary>
  /// 更新一条数据
  /// </summary>
  void Update(Model.Category model);

  /// <summary>
  /// 删除一条数据
  /// </summary>
  void Delete(Guid Id);

  /// <summary>
  /// 是否存在该记录
  /// </summary>
  bool Exists(Guid Id);

  /// <summary>
  /// 得到一个对象实体
  /// </summary>
  Model.Category GetModel(Guid Id);

  /// <summary>
  /// 通用的得到DataSet的方法
  /// </summary>
  DataSet GetList(string strWhat, string strWhere, string strOrderby);

  /// <summary>
  /// 获得数据列表
  /// </summary>
  IList<Model.Category> GetList(string strWhere, string strOrderby);

  /// <summary>
  /// 根据分页获得数据列表
  /// </summary>
  IList<Model.Category> GetList(int pageSize, int pageIndex, string fldSort, bool Sort, string strCondition, out int pageCount, out int Counts);
  #endregion  成员方法
 }
}

using System;

namespace Model
{
 /// <summary>
 /// 实体类 Category
 /// </summary>
 public class Category
 {
  public Category()
  { }
  #region Model
  private Guid _id;
  private string _name;
  private DateTime _createtime;
  /// <summary>
  /// Id
  /// </summary>
  public Guid Id
  {
   set { _id = value; }
   get { return _id; }
  }
  /// <summary>
  /// 名称
  /// </summary>
  public string Name
  {
   set { _name = value; }
   get { return _name; }
  }
  /// <summary>
  /// 创建时间
  /// </summary>
  public DateTime CreateTime
  {
   set { _createtime = value; }
   get { return _createtime; }
  }
  #endregion Model
 }
}

 

using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using IDAL;
using DBUtility;
using System.Collections;
using System.Collections.Generic;

 

namespace SQLServerDAL
{
 /// <summary>
 /// 数据访问类 Category
 /// </summary>
 public class Category : ICategory
 {
  public Category()
  { }
  #region  成员方法

  /// <summary>
  /// 增加一条数据
  /// </summary>
  public Guid Add(Model.Category model)
  {
   model.Id = Guid.NewGuid();
   StringBuilder strSql = new StringBuilder();
   strSql.Append("insert into [Category](");
   strSql.Append("[Id],[Name])");
   strSql.Append(" values (");
   strSql.Append("@Id,@Name)");
   SqlParameter[] parameters = {
     new SqlParameter("@Id", SqlDbType.UniqueIdentifier),
     new SqlParameter("@Name", SqlDbType.NVarChar)
   };
   parameters[0].Value = model.Id;
   parameters[1].Value = model.Name;
   SqlHelper.ExecuteSql(strSql.ToString(), parameters);
   return model.Id;
  }

  /// <summary>
  /// 更新一条数据
  /// </summary>
  public void Update(Model.Category model)
  {
   StringBuilder strSql = new StringBuilder();
   strSql.Append("update [Category] set ");
   strSql.Append("[Name]=@Name,");
   strSql.Append("[CreateTime]=@CreateTime");
   strSql.Append(" where [Id]=@Id");
   SqlParameter[] parameters = {
     new SqlParameter("@Id", SqlDbType.UniqueIdentifier),
     new SqlParameter("@Name", SqlDbType.NVarChar),
     new SqlParameter("@CreateTime", SqlDbType.DateTime)
   };
   parameters[0].Value = model.Id;
   parameters[1].Value = model.Name;
   if (model.CreateTime != DateTime.MinValue)
    parameters[2].Value = model.CreateTime;
   else
    parameters[2].Value = DBNull.Value;
   SqlHelper.ExecuteSql(strSql.ToString(), parameters);
  }

  /// <summary>
  /// 删除一条数据
  /// </summary>
  public void Delete(Guid Id)
  {
   StringBuilder strSql = new StringBuilder();
   strSql.Append("delete [Category] ");
   strSql.Append(" where [Id]=@Id");
   SqlParameter[] parameters = {
     new SqlParameter("@Id", SqlDbType.UniqueIdentifier)};
   parameters[0].Value = Id;
   SqlHelper.ExecuteSql(strSql.ToString(), parameters);
  }

  /// <summary>
  /// 是否存在该记录
  /// </summary>
  public bool Exists(Guid Id)
  {
   StringBuilder strSql = new StringBuilder();
   strSql.Append("select count(1) from [Category]");
   strSql.Append(" where [Id]= @Id");
   SqlParameter[] parameters = {
     new SqlParameter("@Id", SqlDbType.UniqueIdentifier)};
   parameters[0].Value = Id;
   return SqlHelper.Exists(strSql.ToString(), parameters);
  }

  /// <summary>
  /// 得到一个对象实体
  /// </summary>
  public Model.Category GetModel(Guid Id)
  {
   StringBuilder strSql = new StringBuilder();
   strSql.Append("select * from [Category] ");
   strSql.Append(" where [Id]=@Id");
   SqlParameter[] parameters = {
     new SqlParameter("@Id", SqlDbType.UniqueIdentifier)};
   parameters[0].Value = Id;
   DataSet ds = SqlHelper.Query(strSql.ToString(), parameters);
   if (ds.Tables[0].Rows.Count > 0)
   {
    DataRow r = ds.Tables[0].Rows[0];
    return GetModel(r);
   }
   else
   {
    return null;
   }
  }

  /// <summary>
  /// 通用的得到DataSet的方法
  /// </summary>
  public DataSet GetList(string strWhat, string strWhere, string strOrderby)
  {
   StringBuilder strSql = new StringBuilder("select " + strWhat + " from [Category]");
   if (string.IsNullOrEmpty(strWhat))
    strWhat = "*";
   if (!string.IsNullOrEmpty(strWhere))
    strSql.Append(" where " + strWhere);
   if (!string.IsNullOrEmpty(strOrderby))
    strSql.Append(" order by " + strOrderby);
   return SqlHelper.Query(strSql.ToString());
  }

  /// <summary>
  /// 获得数据列表
  /// </summary>
  public IList<Model.Category> GetList(string strWhere, string strOrderby)
  {
   StringBuilder strSql = new StringBuilder();
   strSql.Append("select *  FROM [Category] ");
   if (!string.IsNullOrEmpty(strWhere))
    strSql.Append(" where " + strWhere);
   if (!string.IsNullOrEmpty(strOrderby))
    strSql.Append(" order by " + strOrderby);
   DataSet ds = SqlHelper.Query(strSql.ToString());
   return GetList(ds);
  }

  /// <summary>
  /// 分页获取数据
  /// </summary>
  public IList<Model.Category> GetList(int pageSize, int pageIndex, string fldSort, bool Sort, string strCondition, out int pageCount, out int Counts)
  {
   string strSql;
   DataSet ds = SqlHelper.PageList("[Category]", null, pageSize, pageIndex, fldSort, Sort, strCondition, "Id", false, out pageCount, out Counts, out strSql);
   return GetList(ds);
  }

  #endregion  成员方法

  /// <summary>
  /// 由DataRow得到一个实体
  /// </summary>
  private Model.Category GetModel(DataRow r)
  {
   Model.Category model = new Model.Category();
   model.Id = SqlHelper.GetGuid(r["Id"]);
   model.Name = SqlHelper.GetString(r["Name"]);
   model.CreateTime = SqlHelper.GetDateTime(r["CreateTime"]);
   return model;
  }

  /// <summary>
  /// 由DataSet得到数据列表
  /// </summary>
  private IList<Model.Category> GetList(DataSet ds)
  {
   List<Model.Category> l = new List<Model.Category>();
   foreach (DataRow r in ds.Tables[0].Rows)
   {
    l.Add(GetModel(r));
   }
   return l;
  }
 }
}

assureo
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值