/*
 * 由SharpDevelop创建。
 * 用户： qinshichuan
 * 日期: 2011-12-18
 * 时间: 15:38
 * 
 * 要改变这种模板请点击 工具|选项|代码编写|编辑标准头文件
 */
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Web;

namespace Qin.Orm
{
    ///<summary>
    /// Description of MSSQL.
    ///</summary>
    public class MSSQL:DB
    {
        public MSSQL(string connectionString)
        {
            this.ConnectionString = connectionString;
            this.DBType= DatabasesType.MSSQL;
        }
        
        public override DbCommand GetProcDbCommand(string procName)
        {
            SqlConnection dbConnection = (SqlConnection) this.GetDbConnection();
            SqlCommand command = new SqlCommand();
            command.CommandText = procName;
            command.CommandType = CommandType.StoredProcedure;
            return command;
        }
        public override  List<T> GetPagedListDesc<T>(int startIndex,int endIndex,Field primaryKey,Field onlyMark)
        {
            string sqlSum="select count(*) from "+primaryKey.TableName+ "where "+WhereChecker.ToSafeString(onlyMark.WhereContent);
            long sum=Convert.ToInt64(DBFactory.DefaultDB.GetFirstData(sqlSum));
            long end=sum-startIndex+1;
            long start=sum-endIndex+1;
            string     cmdString="select top "+(endIndex-startIndex+1)+" * from (select top "+(end)+
                " * from "+primaryKey.TableName+" where "+WhereChecker.ToSafeString(onlyMark.WhereContent)+"  order by "+primaryKey.FieldName+" asc) as "+
                primaryKey.TableName+" order by "+primaryKey.FieldName+" DESC";
            
            
            
            DBFactory.CurrentSql=cmdString;
            DbDataReader reader=Qin.Orm.DBFactory.DefaultDB.GetDbDataReader(cmdString);
            List<T> list=new List<T>();
            while (reader.Read()) {
                T data=DynamicBuilder<T>.CreateBuilder(reader).Build(reader);
                list.Add(data);

            }
            reader.Close();
            return list;
            
        }
        public override    List<T> GetWebPagedListDesc<T>(Field primaryKey,Field onlyMark)
        {
            HttpRequest Request=System.Web.HttpContext.Current.Request;
            int p_start=int.Parse(Request.QueryString["p_start"]);
            int p_end=int.Parse(Request.QueryString["p_end"]);
            return GetPagedListDesc<T>(p_start,p_end,primaryKey,onlyMark);
        }
        
        public override object GetFirstData(string cmdString)
        {
            SqlCommand dbCommand = (SqlCommand) this.GetDbCommand(cmdString);
            dbCommand.Connection.Open();
            object firstData = dbCommand.ExecuteScalar();
            dbCommand.Connection.Close();
            return firstData;
        }
        ///<summary>
        /// 执行一条sql语句通过参数形式
        ///</summary>
        ///<param name="parameters">Dictionary形式的参数列表</param>
        ///<param name="sql">sql语句</param>
        ///<param name="startTransaction">是否开启事务</param>
        ///<param name="type">执行语句的类型 增删改查 1 2 3 4</param>
        ///<returns>增加则返回增加后的主键值,否则是受影响的函数</returns>
        public override object ExecuteSqlByParameters(Dictionary<string,object> parameters,string sql,bool startTransaction,int type){
            object newPrimaryKey=null;
            if (parameters==null||parameters.Count==0) {
                throw new ArgumentException("parameters 参数为空,方法InsertDataByParameters调用出错!");
            }
            else{
                //增加
                if (type==1) {
                    sql+="\r\n "+"select SCOPE_IDENTITY()";
                }
                SqlCommand procDbCommand = (SqlCommand) this.GetDbCommand(sql);
                int index=1;
                foreach (KeyValuePair<string,object> parameter in parameters)
                {
                    procDbCommand.Parameters.AddWithValue("@p"+index, parameter.Value);
                    index++;
                }
                
                if (procDbCommand.Connection.State == ConnectionState.Closed)
                {
                    procDbCommand.Connection.Open();
                }
                if (startTransaction==false) {
                    newPrimaryKey=procDbCommand.ExecuteScalar();
                    procDbCommand.Connection.Close();
                }
                else{
                    SqlTransaction dbTransaction=procDbCommand.Connection.BeginTransaction();
                    procDbCommand.Transaction=(SqlTransaction)dbTransaction;
                    try {
                        newPrimaryKey=procDbCommand.ExecuteScalar();
                        dbTransaction.Commit();
                        
                    } catch (InvalidOperationException) {
                        dbTransaction.Rollback();
                        throw new InvalidOperationException("InvalidOperationException ExecuteInsertSqlByParameters");
                    }
                    finally{
                        procDbCommand.Connection.Close();
                        
                    }
                }
                return newPrimaryKey;
            }
        }
        public override DbDataReader GetDbDataReader(string cmdString)
        {
            SqlCommand dbCommand = (SqlCommand) this.GetDbCommand(cmdString);
            dbCommand.Connection.Open();
            return dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
        
        public override DbConnection GetDbConnection()
        {
            return new SqlConnection(this.ConnectionString);
        }
        
        public override DbCommand GetDbCommand(string cmdString)
        {
            return new SqlCommand(cmdString, (SqlConnection) this.GetDbConnection());
        }
        
        public override DataView GetDataView(string cmdString, string RowFilter, string Sort, DataViewRowState RowState)
        {
            return new DataView(this.GetDataTable(cmdString), RowFilter, Sort, RowState);
        }
        
        public override DataTable GetDataTable(string cmdString)
        {
            return this.GetDataSet(cmdString).Tables[0];
        }
        
        public override DataSet GetDataSet(string cmdString)
        {
            SqlCommand dbCommand = (SqlCommand) this.GetDbCommand(cmdString);
            dbCommand.Connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(dbCommand);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet);
            dbCommand.Connection.Close();
            return dataSet;
        }
        
        public override int ExecuteProc(string procName,  Queue<SuperSqlParameter> superSqlParameters)
        {
            if (superSqlParameters.Count <= 0)
            {
                throw new Exception("SuperSqlParameter里面的参数为零");
            }
            SqlCommand procDbCommand = (SqlCommand) this.GetProcDbCommand(procName);
            int num = -1;
            foreach (SuperSqlParameter parameter in superSqlParameters)
            {
                procDbCommand.Parameters.Add(procName, (SqlDbType) parameter.ParameterDbType, parameter.Size);
            }
            if (procDbCommand.Connection.State == ConnectionState.Closed)
            {
                procDbCommand.Connection.Open();
            }
            num = procDbCommand.ExecuteNonQuery();
            procDbCommand.Connection.Close();
            return num;
        }
        
        public override int ExecuteOneSql(string cmdString)
        {
            SqlCommand dbCommand = (SqlCommand) this.GetDbCommand(cmdString);
            dbCommand.Connection.Open();
            int num = dbCommand.ExecuteNonQuery();
            dbCommand.Connection.Close();
            return num;
        }
        private string _connetionString;
        public override string ConnectionString {
            get {
                return _connetionString;
            }
            set {
                _connetionString=value;
            }
        }
    }
}