using System;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using WIP_Models;

namespace WIP_DAL
{
    /// 

    /// $ChinaComment$Êý¾Ý·ÃÎÊÀà
    /// 
    public class $FilePrefixName$DAL
    {
        #region Add

        /// 

        /// Ôö¼ÓÒ»Ìõ$ChinaComment$Êý¾Ý
        /// 
        /// Òª²åÈëµÄ$ChinaComment$ʵÌå
		/// ÊÂÎñÀà
        /// ²åÈëÉú³ÉµÄ×îÐÂÖ÷¼üÖµ
        public int Add($EntityName$ model,TransactionModel transModel = null)
        {
		    SqlParameter param_id = new SqlParameter();
            param_id.ParameterName = "@id";
            param_id.SqlDbType = SqlDbType.Int;
            param_id.Direction = ParameterDirection.Output;

            $AddSqlParameter$ 
          
			int rowsAffected;
			if (transModel != null)
            {
                DbHelperSQL.RunProcedure(transModel.conn,transModel.trans, "$AddProcName$", parameters, out rowsAffected);
			}
			else
			{
                DbHelperSQL.RunProcedure("$AddProcName$", parameters, out rowsAffected);
			}
            return (int)parameters[parameters.Length - 1].Value;
        }

        #endregion

        #region Edit

        /// 

        /// ¸üÐÂÒ»Ìõ$ChinaComment$Êý¾Ý
        /// 
        /// Òª¸üеÄ$ChinaComment$ʵÌå
		/// ÊÂÎñÀà
        /// ÊÇ·ñ¸üгɹ¦
        public bool Update($EntityName$ model,TransactionModel transModel = null)
        {
		    $UpdateSqlParameter$

            int rowsAffected = 0;
			if (transModel != null)
            {
                DbHelperSQL.RunProcedure(transModel.conn,transModel.trans, "$UpdateProcName$", parameters, out rowsAffected);
			}
			else
			{
                DbHelperSQL.RunProcedure("$UpdateProcName$", parameters, out rowsAffected);
			}
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion

        #region Del

        /// 

        /// ɾ³ýÒ»Ìõ$ChinaComment$Êý¾Ý
        /// 
        /// $PrimaryKeyDesc$
		/// ÊÂÎñÀà
        /// ÊÇ·ñɾ³ý³É¹¦
        public bool Delete(string $PrimaryKey$,TransactionModel transModel = null)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM $TableName$ ");
            strSql.Append(" WHERE $PrimaryKey$=@$PrimaryKey$");
            SqlParameter[] parameters = {
                    new SqlParameter("@$PrimaryKey$", SqlDbType.NVarChar,50)
            };
            parameters[0].Value = $PrimaryKey$;

            int rows = 0;
			if (transModel != null)
            {
                rows = DbHelperSQL.ExecuteSql(transModel.conn, transModel.trans, strSql.ToString(), parameters);
			}
			else
			{
                rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
			}
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion

		#region Enable

        /// 

        /// ½ûÆôÓÃ$ChinaComment$Êý¾Ý
        /// 
        /// $PrimaryKeyDesc$
		/// ½ûÓÃ1/ÆôÓÃ0
		/// ×îºóÐÞ¸ÄÈË
		/// ÊÂÎñÀà
        /// ÊÇ·ñ½ûÆôÓóɹ¦
        public bool Enable(string $PrimaryKey$, string delFlag, string lastModifier, TransactionModel transModel = null)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" UPDATE $TableName$  SET delflag=@delFlag ");
            strSql.Append(" ,lastModifier=@lastModifier ");
            strSql.Append(" ,lastModifyTime=@lastModifyTime ");
            strSql.Append(" WHERE $PrimaryKey$=@$PrimaryKey$");
            SqlParameter[] parameters = { 
                    new SqlParameter("@delFlag", SqlDbType.NVarChar,10) ,
                    new SqlParameter("@lastModifier", SqlDbType.NVarChar,20) ,
                    new SqlParameter("@lastModifyTime", SqlDbType.DateTime) ,
                    new SqlParameter("@$PrimaryKey$", SqlDbType.NVarChar,50) 
            };
            parameters[0].Value = delFlag;
            parameters[1].Value = lastModifier;
            parameters[2].Value = DateTime.Now;
            parameters[3].Value = $PrimaryKey$;

            int rows = 0;
            if (transModel != null)
            {
                rows = DbHelperSQL.ExecuteSql(transModel.conn, transModel.trans, strSql.ToString(), parameters);
            }
            else
            {
                rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            }
            return rows > 0 ? true : false;
        }

        #endregion

        #region QueryList(Page)

        /// 

        /// »ñµÃ$ChinaComment$Êý¾ÝÁбí(·ÖÒ³)
        /// 
        /// ·ÖҳʵÌåÊý¾Ý
        /// $ChinaComment$ÁбíDataSet
        public DataSet GetModelListForPage(Query$Modulelogo$Param pageParam)
        {
            $QueryPageSqlParameter$

            return DbHelperSQL.RunProcedure("$GetPageListProcName$", parameters, "pagetable");
        }

        #endregion

		#region QueryList

        /// 

        /// »ñµÃ$ChinaComment$Êý¾ÝÁбí
        /// 
        /// ²éѯÌõ¼þ
        /// $ChinaComment$ÁбíDataSet
        public DataSet GetList(string strWhere)
        {
            SqlParameter[] parameters = {
                        new SqlParameter("@strWhere", SqlDbType.NVarChar,500) 
            };
            parameters[0].Value = strWhere;
            return DbHelperSQL.RunProcedure("$GetListProcName$", parameters, "table");
        }

        #endregion

        #region Query(Single)

        /// 

        /// µÃµ½Ò»¸ö$ChinaComment$ʵÌå
        /// 
        /// $PrimaryKeyDesc$
        /// $ChinaComment$ʵÌå
        public $EntityName$ GetModel(string $PrimaryKey$)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@$PrimaryKey$", SqlDbType.VarChar,50)
            };
            parameters[0].Value = $PrimaryKey$;

            DataSet ds = DbHelperSQL.RunProcedure("$GetSingleProcName$", parameters, "pagetable");

            $EntityName$ model = new $EntityName$();

            if (ds.Tables[0].Rows.Count == 1)
            {
                var dataRow = ds.Tables[0].Rows[0];

                $ToSingleModel$

                return model;
            }
            else
            {
                return null;
            }
        }

        #endregion

        #region Other

        /// 

        /// ÊÇ·ñ´æÔÚ¸Ã$ChinaComment$¼Ç¼
        /// 
        /// $PrimaryKeyDesc$
        /// ÊÇ·ñ´æÔÚ¸Ã$ChinaComment$¼Ç¼
        public bool Exists(string $PrimaryKey$, int? id = null)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) FROM $TableName$");
            strSql.Append(" WHERE ");
            strSql.Append("  $PrimaryKey$ = @$PrimaryKey$  ");
			if (id != null)
            {
                strSql.Append("  AND  id <> " + id.ToString());
            }
            SqlParameter[] parameters = {
                    new SqlParameter("@$PrimaryKey$", SqlDbType.NVarChar,50)           };
            parameters[0].Value = $PrimaryKey$;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        #endregion

    }
}