/*
'' ============================================================================================
'' 项目名称：{fyp_title}
'' 项目版本：{fyp_version}
'' 项目描述：{fyp_description}
'' 文件名称：DAL_{table_source_name}.cs
'' 文件描述：数据访问层 {table_source_name}{value_match_check value1="{table_description}" value2="" extend_match="unequal"} （{table_description}）{/value_match_check}
'' 公司名称：{fyp_company}
'' 开发人员：{fyp_designer}
'' 创建日期：{fyp_created_date}
'' 修订日期：{system_date_time format="yyyy-MM-dd hh:mm:ss"}
'' 版权信息：{fyp_copyright}

'' 项目升级时需读取以下生成工具的版权信息
'' 生成工具：{fysoft_name}
'' 当前版本：{fysoft_version}
'' 官方网站：{fysoft_website}
'' ============================================================================================
*/

using System;
using System.Text;
using System.Data;
{db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="false"}
using System.Data.OleDb;
{/db_type_check}
{db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="true"}
using System.Data.SqlClient;
{/db_type_check}
using FypSystem.DBManager;

namespace {fyp_namespace}.DAL
{
    /// <summary>
    /// 数据访问类{table_source_name}。
    /// </summary>
    public class DAL_{table_source_name}
    {
        public DAL_{table_source_name}()
        {
        }

        #region  记录是否存在
        /// <summary>
        /// 记录是否存在
        /// </summary>{fields_loop type="3" separator=", "}
        /// <param name="{field_source_name}">{field_caption translate="auto"}</param>{/fields_loop}
        /// <returns></returns>
        public bool Exists({fields_loop type="3" separator=", "}{field_code_datatype_name code="cs" type="3" compatible="false"}{value_match_check value1='{field_code_datatype_name code="cs" type="3" compatible="false"}' value2="string|byte[]" extend_match="notin2"}?{/value_match_check} {field_source_name}{/fields_loop})
        {
            {data_transmit_type_check value="使用存储过程" match="false"}
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) from [{table_name}] ");
            strSql.Append("where {fields_loop type="3" separator=' and '}[{field_name}] = @{field_source_name}{/fields_loop}");
            {/data_transmit_type_check}

            {remark}使用OleDb参数{/remark}
            {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="false"}
            OleDbParameter[] arrParameters = new OleDbParameter[{fields_count type="3"}];
            {fields_loop type="3"}
            arrParameters[{i}] = new OleDbParameter("@{field_source_name}", {field_oledbtype_name type="2"}, {field_maxlength});
            arrParameters[{i}].Value = {field_source_name};
            {/fields_loop}
            {/db_type_check}

            {remark}使用sqlserver参数{/remark}
            {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="true"}
            SqlParameter[] arrParameters = new SqlParameter[{fields_count type="3"}];
            {fields_loop type="3"}
            arrParameters[{i}] = new SqlParameter("@{field_source_name}", {field_sqldbtype_name type="2"}, {field_maxlength});
            arrParameters[{i}].Value = {field_source_name};
            {/fields_loop}
            {/db_type_check}

    {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="false"}
            using (FypSystem.DBManager.Access dbManager = new FypSystem.DBManager.Access())
    {/db_type_check}
    {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="true"}
            using (FypSystem.DBManager.SqlServer dbManager = new FypSystem.DBManager.SqlServer())
    {/db_type_check}
            {
                {data_transmit_type_check value="使用存储过程" match="true"}
                dbManager.ExecStoredProcedure("SP_{table_source_name}_Exists", arrParameters);
                return Convert.ToBoolean(dbManager.GetStoredProcedureOutput("@return_value"));
                {/data_transmit_type_check}

                {data_transmit_type_check value="使用存储过程" match="false"}
                return Convert.ToBoolean(dbManager.ExecScalar(strSql.ToString(), arrParameters));
                {/data_transmit_type_check}
            }
        }
        #endregion

        #region 增加数据
        /// <summary>
        /// 增加数据
        /// </summary>
        /// <param name="model">{table_source_name}实体对象</param>
        public void Add({fyp_namespace}.Model.Model_{table_source_name} model)
        {
            {data_transmit_type_check value="使用存储过程" match="false"}
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [{table_name}] (");
            strSql.Append("{fields_loop type="11" separator=", "}{field_name}{/fields_loop}");
            strSql.Append(") values (");
            strSql.Append("{fields_loop type="11" separator=", "}@{field_name}{/fields_loop}");
            strSql.Append(")");
            {/data_transmit_type_check}

            {remark}使用OleDb参数{/remark}
            {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="false"}
            OleDbParameter[] arrParameters = new OleDbParameter[{fields_count type="11"}];
            {fields_loop type="11"}
            arrParameters[{i}] = new OleDbParameter("@{field_source_name}", {field_oledbtype_name type="2"}, {field_maxlength});
            arrParameters[{i}].Value = model.{field_variable};
            {/fields_loop}
            {/db_type_check}

            {remark}使用sqlserver参数{/remark}
            {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="true"}
            SqlParameter[] arrParameters = new SqlParameter[{fields_count type="11"}];
            {fields_loop type="11"}
            arrParameters[{i}] = new SqlParameter("@{field_source_name}", {field_sqldbtype_name type="2"}, {field_maxlength});
            arrParameters[{i}].Value = model.{field_variable};
            {/fields_loop}
            {/db_type_check}

    {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="false"}
            using (FypSystem.DBManager.Access dbManager = new FypSystem.DBManager.Access())
    {/db_type_check}
    {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="true"}
            using (FypSystem.DBManager.SqlServer dbManager = new FypSystem.DBManager.SqlServer())
    {/db_type_check}
            {
                {data_transmit_type_check value="使用存储过程" match="true"}
                dbManager.ExecStoredProcedure("SP_{table_source_name}_Add", arrParameters);
                {/data_transmit_type_check}

                {data_transmit_type_check value="使用存储过程" match="false"}
                dbManager.ExecNonQuery(strSql.ToString(), arrParameters);
                {/data_transmit_type_check}
            }
        }
        #endregion

        #region 更新数据
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="model">{table_source_name}实体对象</param>
        public void Update({fyp_namespace}.Model.Model_{table_source_name} model)
        {
            {data_transmit_type_check value="使用存储过程" match="false"}
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [{table_name}] set ");
            {fields_loop type="11"}
            strSql.Append("{field_name}=@{field_source_name}");{/fields_loop}
            strSql.Append(" where {fields_loop type="3" separator=' and '}[{field_name}] = @{field_source_name}{/fields_loop}");
            {/data_transmit_type_check}

            {remark}使用OleDb参数{/remark}
            {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="false"}
            OleDbParameter[] arrParameters = new OleDbParameter[{fields_count type="-1"}];
            {fields_loop type="-1"}
            arrParameters[{i}] = new OleDbParameter("@{field_source_name}", {field_oledbtype_name type="2"}, {field_maxlength});
            arrParameters[{i}].Value = model.{field_variable};
            {/fields_loop}
            {/db_type_check}

            {remark}使用sqlserver参数{/remark}
            {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="true"}
            SqlParameter[] arrParameters = new SqlParameter[{fields_count type="-1"}];
            {fields_loop type="-1"}
            arrParameters[{i}] = new SqlParameter("@{field_source_name}", {field_sqldbtype_name type="2"}, {field_maxlength});
            arrParameters[{i}].Value = model.{field_variable};
            {/fields_loop}
            {/db_type_check}

    {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="false"}
            using (FypSystem.DBManager.Access dbManager = new FypSystem.DBManager.Access())
    {/db_type_check}
    {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="true"}
            using (FypSystem.DBManager.SqlServer dbManager = new FypSystem.DBManager.SqlServer())
    {/db_type_check}
            {
                {data_transmit_type_check value="使用存储过程" match="true"}
                dbManager.ExecStoredProcedure("SP_{table_source_name}_Update", arrParameters);
                {/data_transmit_type_check}

                {data_transmit_type_check value="使用存储过程" match="false"}
                dbManager.ExecNonQuery(strSql.ToString(), arrParameters);
                {/data_transmit_type_check}
            }
        }
        #endregion

        #region 删除数据
        /// <summary>
        /// 删除数据
        /// </summary>{fields_loop type="3" separator=", "}
        /// <param name="{field_source_name}">{field_caption translate="auto"}</param>{/fields_loop}
        public void Delete({fields_loop type="3" separator=", "}{field_code_datatype_name code="cs" type="3" compatible="false"}{value_match_check value1='{field_code_datatype_name code="cs" type="3" compatible="false"}' value2="string|byte[]" extend_match="notin2"}?{/value_match_check} {field_source_name}{/fields_loop})
        {
            {data_transmit_type_check value="使用存储过程" match="false"}
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [{table_name}] ");
            strSql.Append(" where {fields_loop type="3" separator=' and '}[{field_name}] = @{field_source_name}{/fields_loop}");
            {/data_transmit_type_check}

            {remark}使用OleDb参数{/remark}
            {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="false"}
            OleDbParameter[] arrParameters = new OleDbParameter[{fields_count type="3"}];
            {fields_loop type="3"}
            arrParameters[{i}] = new OleDbParameter("@{field_source_name}", {field_oledbtype_name type="2"}, {field_maxlength});
            arrParameters[{i}].Value = {field_source_name};
            {/fields_loop}
            {/db_type_check}

            {remark}使用sqlserver参数{/remark}
            {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="true"}
            SqlParameter[] arrParameters = new SqlParameter[{fields_count type="3"}];
            {fields_loop type="3"}
            arrParameters[{i}] = new SqlParameter("@{field_source_name}", {field_sqldbtype_name type="2"}, {field_maxlength});
            arrParameters[{i}].Value = {field_source_name};
            {/fields_loop}
            {/db_type_check}

    {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="false"}
            using (FypSystem.DBManager.Access dbManager = new FypSystem.DBManager.Access())
    {/db_type_check}
    {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="true"}
            using (FypSystem.DBManager.SqlServer dbManager = new FypSystem.DBManager.SqlServer())
    {/db_type_check}
            {
                {data_transmit_type_check value="使用存储过程" match="true"}
                dbManager.ExecStoredProcedure("SP_{table_source_name}_Delete", arrParameters);
                {/data_transmit_type_check}

                {data_transmit_type_check value="使用存储过程" match="false"}
                dbManager.ExecNonQuery(strSql.ToString(), arrParameters);
                {/data_transmit_type_check}
            }
        }
        #endregion

        #region 获取对象实体
        /// <summary>
        /// 获取对象实体
        /// </summary>{fields_loop type="3" separator=", "}
        /// <param name="{field_source_name}">{field_caption translate="auto"}</param>{/fields_loop}
        /// <returns></returns>
        public {fyp_namespace}.Model.Model_{table_source_name} GetModel({fields_loop type="3" separator=", "}{field_code_datatype_name code="cs" type="3" compatible="false"}{value_match_check value1='{field_code_datatype_name code="cs" type="3" compatible="false"}' value2="string|byte[]" extend_match="notin2"}?{/value_match_check} {field_source_name}{/fields_loop})
        {
            {data_transmit_type_check value="使用存储过程" match="false"}
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select {fields_loop type="-1" separator=", "}{field_name}{/fields_loop} from [{table_name}]");
            strSql.Append(" where {fields_loop type="3" separator=' and '}[{field_name}] = @{field_source_name}{/fields_loop}");
            {/data_transmit_type_check}

            {remark}使用OleDb参数{/remark}
            {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="false"}
            OleDbParameter[] arrParameters = new OleDbParameter[{fields_count type="3"}];
            {fields_loop type="3"}
            arrParameters[{i}] = new OleDbParameter("@{field_source_name}", {field_oledbtype_name type="2"}, {field_maxlength});
            arrParameters[{i}].Value = {field_source_name};
            {/fields_loop}
            {/db_type_check}

            {remark}使用sqlserver参数{/remark}
            {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="true"}
            SqlParameter[] arrParameters = new SqlParameter[{fields_count type="3"}];
            {fields_loop type="3"}
            arrParameters[{i}] = new SqlParameter("@{field_source_name}", {field_sqldbtype_name type="2"}, {field_maxlength});
            arrParameters[{i}].Value = {field_source_name};
            {/fields_loop}
            {/db_type_check}

            {fyp_namespace}.Model.Model_{table_source_name} model = new {fyp_namespace}.Model.Model_{table_source_name}();

            DataSet ds = new DataSet();;
    {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="false"}
            using (FypSystem.DBManager.Access dbManager = new FypSystem.DBManager.Access())
    {/db_type_check}
    {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="true"}
            using (FypSystem.DBManager.SqlServer dbManager = new FypSystem.DBManager.SqlServer())
    {/db_type_check}
            {
                {data_transmit_type_check value="使用存储过程" match="true"}
                dbManager.ExecStoredProcedure("SP_{table_source_name}_GetModel", arrParameters, ds);
                {/data_transmit_type_check}

                {data_transmit_type_check value="使用存储过程" match="false"}
                ds = dbManager.GetDataSet(strSql.ToString(), arrParameters);
                {/data_transmit_type_check}
            }

            if(ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];

            {fields_loop type="-1"}
                {value_match_check value1="{field_code_datatype_name type='3' compatible='false'}" value2="string" extend_match="equal"} 
                model.{field_variable} = dr["{field_name}"].ToString();
                {/value_match_check}

                {value_match_check value1="{field_code_datatype_name type='3' compatible='false'}" value2="string|byte[]" extend_match="notin2"} 
                if (dr["{field_name}"].ToString().Length > 0)
                    model.{field_variable} = {field_code_datatype_name type='3' compatible='false'}.Parse(dr["{field_name}"].ToString());
                {/value_match_check}

                {value_match_check value1="{field_code_datatype_name type='3' compatible='false'}" value2="byte[]" extend_match="equal"} 
                if (dr["{field_name}"].ToString().Length > 0)
                    model.{field_variable} = (byte[])dr["{field_name}"];
                {/value_match_check}
            {/fields_loop}

                return model;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 获取数据列表
        /// <summary>
        /// 获取数据列表 DataSet
        /// </summary>
        /// <param name="strWhere">指定查询条件的SQL语句，不包含where</param>
        /// <returns></returns>
        public DataSet GetDataSet(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select {fields_loop type="-1" separator=", "}{field_name}{/fields_loop} from [{table_name}]");
            if(strWhere.Trim().Length > 0)
            {
                strSql.Append(" where " + strWhere);
            }
    {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="false"}
            using (FypSystem.DBManager.Access dbManager = new FypSystem.DBManager.Access())
    {/db_type_check}
    {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="true"}
            using (FypSystem.DBManager.SqlServer dbManager = new FypSystem.DBManager.SqlServer())
    {/db_type_check}
            {
                return dbManager.GetDataSet(strSql.ToString());
            }
        }

        /// <summary>
        /// 获取数据列表 DataTable
        /// </summary>
        /// <param name="strWhere">指定查询条件的SQL语句，不包含where</param>
        /// <returns></returns>
        public DataTable GetDataTable(string strWhere)
        {
            return GetDataTable(strWhere, "");
        }

        /// <summary>
        /// 获取数据列表 DataTable
        /// </summary>
        /// <param name="strWhere">指定查询条件的SQL语句，不包含where</param>
        /// <param name="strOrder">排序字段，如id desc，不包含order by</param>
        /// <returns></returns>
        public DataTable GetDataTable(string strWhere, string strOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select {fields_loop type="-1" separator=", "}{field_name}{/fields_loop} from [{table_name}]");
            if(strWhere.Trim().Length > 0)
                strSql.Append(" where " + strWhere);

            if (strOrder.Trim().Length > 0)
                strSql.Append(" order by " + strOrder);

    {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="false"}
            using (FypSystem.DBManager.Access dbManager = new FypSystem.DBManager.Access())
    {/db_type_check}
    {db_type_check value="sqlserver|sqlserver2005|sqlserver2008" match="true"}
            using (FypSystem.DBManager.SqlServer dbManager = new FypSystem.DBManager.SqlServer())
    {/db_type_check}
            {
                return dbManager.GetDataTable(strSql.ToString());
            }
        }
        #endregion 
    }
}