private StoredProcedure BaoCaoSoTruc(DateTime? pFromDate, DateTime? pToDate)
 {
     var sp = new StoredProcedure("GTVT_BAOCAO_SOTRUC", DataService.GetInstance("ORM"), "dbo");
     sp.Command.AddParameter("@FromDate", pFromDate, DbType.DateTime, null, null);
     sp.Command.AddParameter("@ToDate", pToDate, DbType.DateTime, null, null);
     return sp;
 }

 public StoredProcedure DeletePin(int BIMID,int UserID)
 {
     StoredProcedure sp=new StoredProcedure("DeletePin",this.Provider);
     sp.Command.AddParameter("BIMID",BIMID,DbType.Int32);
     sp.Command.AddParameter("UserID",UserID,DbType.Int32);
     return sp;
 }

    /// <summary>
    /// Execute Sql Query with Parameters
    /// </summary>
    /// <param name="sData">StoredProcedure Class</param>
    /// <returns></returns>
    public static DataSet SqlQueryDataSet(StoredProcedure sData)
    {
        DataSet sqlDataView = new DataSet();

        try
        {
            SqlConnection conn = new SqlConnection(connect());
            SqlCommand sqlcomm = new SqlCommand(sData.ProcName, conn);
            sqlcomm.CommandType = CommandType.StoredProcedure;

            int i = 0;
            IEnumerator myEnumerator = sData.GetParams().GetEnumerator();
            while (myEnumerator.MoveNext())
            {
                ParamData pData = (ParamData)myEnumerator.Current;
                sqlcomm.Parameters.Add(pData.pName, pData.pDataType);
                sqlcomm.Parameters[i].Value = pData.pValue;
                i = i + 1;
            }

            SqlDataAdapter sqlAdapterView = new SqlDataAdapter(sqlcomm);

            sqlAdapterView.Fill(sqlDataView);

            return sqlDataView;
        }
        catch (Exception ex)
        {
            HttpContext.Current.Response.Write(ex);
            HttpContext.Current.Response.End();

            return sqlDataView;
        }
    }

    public void TestBasicParsing()
    {
        string proc1 = "CREATE PROC mysp @arg int=1 AS select 1\n";
        var sp = new StoredProcedure(proc1);
        WVPASSEQ(sp.name, "mysp");
        var args = sp.args.ToArray();
        WVPASSEQ(args[0].name, "arg");
        WVPASSEQ(args[0].type, "int");
        WVPASSEQ(args[0].defval, "1");

        string proc2 = "CREATE PROC [mysp2]\[email protected] int output ,\n" + 
            "  @arg2 varchar(10) = 'asdf',@arg3 money AS ";

        sp = new StoredProcedure(proc2);
        WVPASSEQ(sp.name, "[mysp2]");
        args = sp.args.ToArray();
        WVPASSEQ(args[0].name, "arg1");
        WVPASSEQ(args[0].type, "int");
        WVPASSEQ(args[0].defval, "");
        WVPASSEQ(args[1].name, "arg2");
        WVPASSEQ(args[1].type, "varchar(10)");
        WVPASSEQ(args[1].defval, "'asdf'");
        WVPASSEQ(args[2].name, "arg3");
        WVPASSEQ(args[2].type, "money");
        WVPASSEQ(args[2].defval, "");
    }

 public StoredProcedure aspnet_Membership_GetNumberOfUsersOnline(string ApplicationName,int MinutesSinceLastInActive,DateTime CurrentTimeUtc){
     StoredProcedure sp=new StoredProcedure("aspnet_Membership_GetNumberOfUsersOnline",this.Provider);
     sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String);
     sp.Command.AddParameter("MinutesSinceLastInActive",MinutesSinceLastInActive,DbType.Int32);
     sp.Command.AddParameter("CurrentTimeUtc",CurrentTimeUtc,DbType.DateTime);
     return sp;
 }

 public StoredProcedure UpdatePrize(int UserID,int RWPointDed)
 {
     StoredProcedure sp=new StoredProcedure("UpdatePrize",this.Provider);
     sp.Command.AddParameter("UserID",UserID,DbType.Int32);
     sp.Command.AddParameter("RWPointDed",RWPointDed,DbType.Int32);
     return sp;
 }

 public StoredProcedure GetUser(string Email,string Password)
 {
     StoredProcedure sp=new StoredProcedure("GetUser",this.Provider);
     sp.Command.AddParameter("Email",Email,DbType.AnsiString);
     sp.Command.AddParameter("Password",Password,DbType.AnsiString);
     return sp;
 }

 public StoredProcedure aspnet_Membership_GetAllUsers(string ApplicationName,int PageIndex,int PageSize){
     StoredProcedure sp=new StoredProcedure("aspnet_Membership_GetAllUsers",this.Provider);
     sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String);
     sp.Command.AddParameter("PageIndex",PageIndex,DbType.Int32);
     sp.Command.AddParameter("PageSize",PageSize,DbType.Int32);
     return sp;
 }

        public static DataTable GetCategory(int p_CategoryID)
        {
            StoredProcedure sp = new StoredProcedure("p_Categories_s");
            sp["@CategoryID"].Value = p_CategoryID;

            return sp.ExecuteDataTable();
        }

 public StoredProcedure UpdatePoints(int UserID,string PointsName)
 {
     StoredProcedure sp=new StoredProcedure("UpdatePoints",this.Provider);
     sp.Command.AddParameter("UserID",UserID,DbType.Int32);
     sp.Command.AddParameter("PointsName",PointsName,DbType.AnsiString);
     return sp;
 }

 public static DataTable AllByParent(int? p_ParentCategoryID, bool p_ShowDisable)
 {
     StoredProcedure sp = new StoredProcedure("p_Categories_sl_ByParent");
     sp["@ParentCategoryID"].Value = p_ParentCategoryID==null?DBNull.Value:(object)p_ParentCategoryID;
     sp["@ShowDisable"].Value = p_ShowDisable;
     return sp.ExecuteDataTable();
 }

 public static DataTable AllCategoriesDrugsByParent(int? p_ParentCategoryID, bool p_isShowAll)
 {
     StoredProcedure sp = new StoredProcedure("p_CategoriesDrugs_sl_ByParent");
     sp["@CategoryID"].Value = p_ParentCategoryID == null ? DBNull.Value : (object)p_ParentCategoryID;
     sp["@ShowAll"].Value = p_isShowAll;
     return sp.ExecuteDataTable();
 }

 private StoredProcedure BaocaoSoghichep(DateTime? pFromDate, DateTime? pToDate)
 {
     SubSonic.StoredProcedure sp = new StoredProcedure("spSoGhiChep", DataService.GetInstance("ORM"), "dbo");
       sp.Command.AddParameter("@pFromDate",pFromDate,DbType.DateTime,null,null);
       sp.Command.AddParameter("@pToDate",pToDate,DbType.DateTime,null,null);
       return sp;
 }

 internal static StoredProcedure GenerateDocumentCode(string transactionType)
 {
     //TODO : XML config for SP name
     StoredProcedure sp = new StoredProcedure("GenerateDocumentCode");
     sp.AddParameter("@TransactionType", StoredProcedure.ParameterType.String, 30, transactionType);
     return sp;
 }

 public StoredProcedure AddBoardContributor(string User,int BoardID)
 {
     StoredProcedure sp=new StoredProcedure("AddBoardContributor",this.Provider);
     sp.Command.AddParameter("User",User,DbType.AnsiString);
     sp.Command.AddParameter("BoardID",BoardID,DbType.Int32);
     return sp;
 }

 public StoredProcedure DeleteBoardContributor(string Email,int BoardID)
 {
     StoredProcedure sp=new StoredProcedure("DeleteBoardContributor",this.Provider);
     sp.Command.AddParameter("Email",Email,DbType.AnsiString);
     sp.Command.AddParameter("BoardID",BoardID,DbType.Int32);
     return sp;
 }

 public StoredProcedure aspnet_Membership_GetUserByName(string ApplicationName,string UserName,DateTime CurrentTimeUtc,bool UpdateLastActivity){
     StoredProcedure sp=new StoredProcedure("aspnet_Membership_GetUserByName",this.Provider);
     sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String);
     sp.Command.AddParameter("UserName",UserName,DbType.String);
     sp.Command.AddParameter("CurrentTimeUtc",CurrentTimeUtc,DbType.DateTime);
     sp.Command.AddParameter("UpdateLastActivity",UpdateLastActivity,DbType.Boolean);
     return sp;
 }

        public static StoredProcedure P_Branch_GetMaxBranchCode(int Depth,int ParentId)
        {
            StoredProcedure sp=new StoredProcedure("P_Branch_GetMaxBranchCode");

            sp.Command.AddParameter("Depth",Depth,DbType.Int32);
            sp.Command.AddParameter("ParentId",ParentId,DbType.Int32);
            return sp;
        }

 private StoredProcedure BaocaoDSBNtheoTestType(DateTime? pFromDate, DateTime? pToDate, string testType)
 {
     SubSonic.StoredProcedure sp = new StoredProcedure("spBaoCao_DSBN_TestType", DataService.GetInstance("ORM"),"dbo");
     sp.Command.AddParameter("@pFromDate", pFromDate,DbType.DateTime,null,null);
     sp.Command.AddParameter("@pToDate", pToDate,DbType.DateTime,null,null);
     sp.Command.AddParameter("@testType",testType,DbType.String,null,null);
     return sp;
 }

 public StoredProcedure aspnet_Membership_FindUsersByName(string ApplicationName,string UserNameToMatch,int PageIndex,int PageSize){
     StoredProcedure sp=new StoredProcedure("aspnet_Membership_FindUsersByName",this.Provider);
     sp.Command.AddParameter("ApplicationName",ApplicationName,DbType.String);
     sp.Command.AddParameter("UserNameToMatch",UserNameToMatch,DbType.String);
     sp.Command.AddParameter("PageIndex",PageIndex,DbType.Int32);
     sp.Command.AddParameter("PageSize",PageSize,DbType.Int32);
     return sp;
 }