using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataLinkMesWork
{
    public partial class DataLink
    {
        /// <summary>
        /// 通过执行SQL语句，查询数据库
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ds"></param>
        /// <returns></returns>
        public static bool ExecuteDataset_Bakup(string sql, out DataSet ds)
        {
            string errorMessage;
            return SQLCommon.ExecuteDataset(sql, connectionStringBakup, out ds, out errorMessage);
        }
        /// <summary>
        /// 通过执行SQL语句，实现对表的增、删、改
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery_Bakup(string sql)
        {
            string errorMessage;
            return SQLCommon.ExecuteNonQuery( sql, connectionStringBakup, out  errorMessage);
        }


        /// <summary>
        /// 通过执行SQL语句，实现对表的增、删、改。返回后面Select 参数 行数或自动编号
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public static bool ExecuteOutNum_Bakup(string sql, out int count)
        {
            string errorMessage;
            return SQLCommon.ExecuteOutNum(sql, connectionStringBakup, out count, out errorMessage);

        }
        /// <summary>
        /// 执行带参数的SQL语句，完成增、删、改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramData"></param>
        /// <returns></returns>
        public static bool ExecuteSql_Bakup(string sql,  out string errorMessage)
        {
            return SQLCommon.ExecuteSql(sql, connectionStringBakup, out errorMessage);
        }
        /// <summary>
        /// 执行带参数的SQL语句，完成增、删、改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramData"></param>
        /// <returns></returns>
        public static bool ExecuteSql_Bakup(string sql,  ref ParamData[] paramData, out string errorMessage)
        {
            SqlParameter[] thisParms;
            GetSqlCmd(paramData, out thisParms);
            return SQLCommon.ExecuteSql(sql, connectionStringBakup, ref thisParms, out errorMessage);
        }
        /// <summary>
        /// 执行带参数的SQL语句，完成增、删、改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramData"></param>
        /// <returns></returns>
        public static bool ExecuteSql_Bakup(string sql,  out DataTable dt, out string errorMessage)
        {
            return SQLCommon.ExecuteSql(sql, connectionStringBakup, out dt, out errorMessage);
        }
        /// <summary>
        /// 执行带参数的SQL语句，完成增、删、改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramData"></param>
        /// <returns></returns>
        public static bool ExecuteSql_Bakup(string sql, ref ParamData[] paramData,out DataTable dt, out string errorMessage)
        {
            SqlParameter[] thisParms;
            GetSqlCmd(paramData, out thisParms);
            return SQLCommon.ExecuteSql(sql, ref thisParms, connectionStringBakup, out dt,out errorMessage);
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="tbname"></param>
        /// <param name="fieldkey"></param>
        /// <param name="where"></param>
        /// <param name="fieldshow"></param>
        /// <param name="fieldorder"></param>
        /// <param name="fieldgroup"></param>
        /// <param name="pagecurrent"></param>
        /// <param name="pagesize"></param>
        /// <param name="pagecount"></param>
        /// <param name="itemcount"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static bool ExecPageQueryMesWork_Bakup(string tbname, string fieldkey, string where, string fieldshow, string fieldorder, string fieldgroup, int pagecurrent, int pagesize, out int pagecount, out int itemcount, out DataTable dt)
        {
            string ErrorMessage;
            return DataAccess.ExecPageQueryMesWork(tbname, connectionStringBakup, fieldkey, where, fieldshow, fieldorder, fieldgroup, pagecurrent, pagesize, out pagecount, out itemcount, out dt, out ErrorMessage);
        }
        /// <summary>
        /// INSERT INTO [Test]([f1],f2) VALUES(@f1,@f2)
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="paramData"></param>
        /// <returns></returns>
        public static bool Insert_Bakup(string tableName, ParamData[] paramData,out string err)
        {
            //INSERT INTO [Test](f1,f2) VALUES(@f1,@f2)
            SqlParameter[] thisParms;
            string sqlCmd;
            sqlCmd = GetSqlCmd(tableName, paramData, out thisParms);

            return SQLCommon.ExecuteInsertMesWork(sqlCmd, ref thisParms, connectionStringBakup, out err);
        }
        /// <summary>
        /// 执行增、删、改存储过程
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="paramData"></param>
        /// <param name="errorMessage"></param>
        /// <returns></returns>
        public static bool ExecuteStoredProcedure_Bakup(string procedureName, ref ParamData[] paramData, out string errorMessage)
        {
            //INSERT INTO [Test](f1,f2) VALUES(@f1,@f2)
            SqlParameter[] thisParms=null;
            GetSqlCmd(paramData, out thisParms);
            if(SQLCommon.ExecuteStoredProcedure(procedureName, connectionStringBakup, ref thisParms, out errorMessage))
            {
                for(int i=0;i< thisParms.Length;i++)
                {
                    if(paramData[i].isOutPut)
                    {
                        paramData[i].value = thisParms[i].Value.ToString();
                    }
                }
                return true;
            }
            return false;
        }


        /// <summary>
        /// 插入数据，可以返回自动编号。存储过程增加 select @@identity
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="paramData"></param>
        /// <param name="index"></param>
        /// <param name="errorMessage"></param>
        /// <returns></returns>
        public static bool ExecuteStoredProcedure_Bakup(string procedureName, ref ParamData[] paramData, out long index,out string errorMessage)
        {
            //INSERT INTO [Test](f1,f2) VALUES(@f1,@f2)
            SqlParameter[] thisParms = null;
            GetSqlCmd(paramData, out thisParms);
            if (SQLCommon.ExecuteStoredProcedure(procedureName, connectionStringBakup, ref thisParms, out  index,out errorMessage))
            {
                for (int i = 0; i < thisParms.Length; i++)
                {
                    if (paramData[i].isOutPut)
                    {
                        paramData[i].value = thisParms[i].Value.ToString();
                    }
                }
                return true;
            }
            return false;
        }
        /// <summary>
        /// 用存储过程查询数据
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="paramData"></param>
        /// <param name="dt"></param>
        /// <param name="errorMessage"></param>
        /// <returns></returns>
        public static bool ExecuteStoredProcedure_Bakup(string procedureName, ref ParamData[] paramData, out DataTable dt, out string errorMessage)
        {
            //INSERT INTO [Test](f1,f2) VALUES(@f1,@f2)
            SqlParameter[] thisParms = null;
            GetSqlCmd(paramData, out thisParms);
            return SQLCommon.ExecuteStoredProcedure(procedureName, connectionStringBakup, ref thisParms, out dt, out errorMessage);

        }
        public static bool ExecuteStoredProcedure_Bakup(string procedureName, ref ParamData[] paramData, out DataSet ds, out string errorMessage)
        {
            //INSERT INTO [Test](f1,f2) VALUES(@f1,@f2)
            SqlParameter[] thisParms = null;
            GetSqlCmd(paramData, out thisParms);
            return SQLCommon.ExecuteStoredProcedure(procedureName, connectionStringBakup, ref thisParms, out ds, out errorMessage);

        }
        // ExecuteStoredProcedure(string procedureName, string connectionString, ref SqlParameter[] sqlParameters, out DataSet ds, out string errorMessage)
        /// <summary>
        /// 将数据插入数据库
        /// 输入表名
        /// 输入字段对应的值
        /// 返回自动编号
        /// </summary>
        /// <param name="tableName">输入表名</param>
        /// <param name="paramData">输入字段对应的值</param>
        /// <param name="id">返回自动编号</param>
        /// <param name="err">返回插入失败的原因</param>
        /// <returns></returns>
        public static bool Insert_Bakup(string tableName, ParamData[] paramData, out long id,out string err)
        {
            //INSERT INTO [Test]([f1],f2) VALUES(@f1,@f2)
            SqlParameter[] thisParms;
            string sqlCmd;
            sqlCmd = GetSqlCmd(tableName, paramData, out thisParms);
            return SQLCommon.ExecuteInsertMesWork(sqlCmd, ref thisParms, connectionStringBakup, out id,out err);

        }
        public static bool Insert_Bakup(System.Data.DataTable dt, out string err)
        {
            SqlParameter[] thisParms;
            string sqlCmd;
            sqlCmd = GetSqlCmd(dt, out thisParms);
            return SQLCommon.ExecuteSqlCmd(sqlCmd, connectionStringBakup, ref thisParms, dt, out err);
        }
        /// <summary>
        /// 根据条件，更新数据库
        /// </summary>
        /// <param name="tableName">被更新的表</param>
        /// <param name="paramSet">更改的字段与值对应表</param>
        /// <param name="paramWhere">更改的字段与值对应表的条件</param>
        /// <returns></returns>
        public static bool UpData_Bakup(string tableName, ParamData[] paramSet, ParamData[] paramWhere, out string err)
        {
            //UPDATE Test SET [f1] = @f1,[f2] = @f2,[f3] = @f3 WHERE f1=@ff1 and f2=@ff2
            SqlParameter[] thisParms;
            string sqlCmd;
            sqlCmd = GetSqlCmdUpdate( tableName, paramSet,  paramWhere, out thisParms);
            return SQLCommon.ExecuteInsertMesWork(sqlCmd, ref thisParms, connectionStringBakup, out err);
        }
        /// <summary>
        /// DELETE FROM TEST WHERE f1=@f1 
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="paramWhere">WHERE f1=@f1  @f1,value</param>
        /// <returns></returns>
        public static bool DeleteBakup(string tableName, ParamData[] paramWhere, out string err)
        {
            SqlParameter[] thisParms;
            string sqlCmd;
            sqlCmd = GetSqlCmdDelete(tableName, paramWhere, out thisParms);
            return SQLCommon.ExecuteInsertMesWork(sqlCmd, ref thisParms, connectionStringBakup, out err);
        }
        /// <summary>
        /// SELECT f1,f2 from test where f1=@f1 and f1=@f2
        /// </summary>
        /// <param name="selectCmd"></param>
        /// <param name="paramData">ParamData数组赋值(f1,value1),(f2,value2)</param>
        /// <param name="dt"></param>
        /// <param name="err"></param>
        /// <returns></returns>
        public static bool Select_Bakup(string selectCmd, ParamData[] paramData, out DataTable dt, out string err)
        {
            //SELECT f1,f2 from test where f1=@f1 and f1=@f2
            SqlParameter[] thisParms;
            GetSqlCmdSelect(paramData, out thisParms);
            return SQLCommon.ExecuteSelectMesWork(selectCmd, ref thisParms, connectionStringBakup, out dt,out err);
        }

        /// <summary>
        ///paramData: @tbname sysname, --要分页显示的表名
        ///           @fieldkey   sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
        ///           @fieldshow  nvarchar(1000), --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
        ///           @fieldOrder nvarchar(1000), ----以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC  --用于指定排序顺序
        ///           @PageCurrent    int=1,          --要显示的页码
        ///           @PageSize       int=30,         --每页的大小(记录数)
        ///paramDataWhere: @where      nvarchar(1000),	--查询条件
        ///@PageCount int OUTPUT,     --总页数
        ///@ItemCount  int output	--数据总数
        /// </summary>
        /// <param name="paramData[6]">tbname,fieldkey,fieldshow,fieldorder,pagecurrent,pagesize</param>
        /// <param name="paramDataWhere"></param>
        /// <param name="dt"></param>
        /// <param name="pagecount"></param>
        /// <param name="itemcount"></param>
        /// <param name="err"></param>
        public static bool SelectPage_Bakup(string tbname, string fieldkey, string where, string fieldshow, string fieldorder, string fieldgroup, int pagecurrent, int pagesize, out int pagecount, out int itemcount, out DataTable dt)
        {
            string err = "";
            dt = null;
            return DataAccess.ExecPageQueryMesWork(tbname, connectionStringBakup, fieldkey, where, fieldshow, fieldorder, fieldgroup,pagecurrent, pagesize, out pagecount, out itemcount, out  dt, out  err);
        }

    }
}