C# SqlDataSource.DataBind方法代码示例

        public void LoadFilteredDRDetails(SqlDataSource DRDetailsDataSource, int DRID_S, int DRID_E, int CustomerNumber)
        {
            StringBuilder search_command = new StringBuilder();
            search_command.Append(" SELECT DR.DRNo, DRDtl.SKU, DRDtl.StyleNo, DR.ItemStatus, DRDtl.Quantity, DRDtl.UnitPrice FROM DR INNER JOIN DRDtl ON DR.ID = DRDtl.DRID ");
            search_command.Append(" where DR.ID between @ID_S and @ID_E and custno [email protected]_Number ");
            Parameter prmDRIDS = new Parameter
            {
                 DbType = System.Data.DbType.Int32 ,
                 Name = "ID_S",
                  DefaultValue = DRID_S.ToString(),
            };

            Parameter prmDRIDE = new Parameter
            {
                DbType = System.Data.DbType.Int32,
                Name = "ID_E",
                DefaultValue = DRID_E.ToString(),
            };
            Parameter prmCustomerNumber = new Parameter
            {
                DbType = System.Data.DbType.Int32,
                Name = "Customer_Number",
                DefaultValue = CustomerNumber.ToString(),
            };
            DRDetailsDataSource.SelectParameters.Add(prmDRIDS);
            DRDetailsDataSource.SelectParameters.Add(prmDRIDE);
            DRDetailsDataSource.SelectParameters.Add(prmCustomerNumber);
            DRDetailsDataSource.SelectCommand = search_command.ToString();
            DRDetailsDataSource.DataBind();
            //       SELECT DR.DRNo, DRDtl.SKU, DRDtl.StyleNo, DR.ItemStatus, DRDtl.Quantity, DRDtl.UnitPrice FROM DR
            //INNER JOIN DRDtl ON DR.ID = DRDtl.DRID
            //where DR.ID between @IDS and @IDE and custno [email protected]
        }

 public SqlDataSource SearchOutletDataSource(SqlDataSource sql_data_source, string search_parameter, string Brand)
 {
     //if (Brand != "ALL")
     //{
         //if (search_parameter != string.Empty)
     //    {
     //        sql_data_source.SelectCommand = "SELECT [CustNo], [CompName], [brand] FROM [CustInfo] where brand ='"+ Brand +"' and CompName Like '%" +
     //            search_parameter + "%'";
     //    }
     //    else
     //    {
     //        sql_data_source.SelectCommand = "SELECT [CustNo], [CompName], [brand] FROM [CustInfo] where brand ='" + Brand + "' ";
     //    }
     //}
     //else
     //{
         if (search_parameter != string.Empty)
         {
             sql_data_source.SelectCommand = "SELECT [CustNo], [CompName], [brand] FROM [CustInfo] where CompName Like '%" +
                 search_parameter + "%'";
         }
         else
         {
             sql_data_source.SelectCommand = "SELECT [CustNo], [CompName], [brand] FROM [CustInfo] ";
         }
     //}
     sql_data_source.DataBind();
     return sql_data_source;
 }

 public void FilterSalesInvoiceList(SqlDataSource datasource, string filter_field_type)
 {
     switch (filter_field_type)
     {
         case "POSTED":
             datasource.SelectCommand = SearchCommand(filter_field_type);
             datasource.DataBind();
             break;
         case "UNPOSTED":
             datasource.SelectCommand = SearchCommand(filter_field_type);
             datasource.DataBind();
             break;
         case "CANCELLED":
             datasource.SelectCommand = SearchCommand(filter_field_type);
             datasource.DataBind();
             break;
     }
 }

 public void SearchCustomers(SqlDataSource CustomerDatasource, string search_parameter, string Brand)
 {
     StringBuilder commandText = new StringBuilder();
     commandText.Append("SELECT [CustNo], [CompName],[PGNo] FROM [CustInfo] WHERE ([brand] = '" + Brand + "')");
     if (search_parameter != "")
     {
         commandText.Append(" and CompName LIKE'%"+search_parameter+"%'");
     }
     CustomerDatasource.SelectCommand = commandText.ToString();
     CustomerDatasource.DataBind();
 }

 public void SeachCustomers(SqlDataSource CustomerDataSource, string search_parameter)
 {
     StringBuilder search_command = new StringBuilder();
     search_command.Append("SELECT [CustNo], [CompName] FROM [CustInfo] ");
     if (!string.IsNullOrEmpty(search_parameter))
     {
         search_command.Append(" WHERE CompName LIKE '%"+search_parameter+"%' ");
     }
     CustomerDataSource.SelectCommand = search_command.ToString();
     CustomerDataSource.DataBind();
 }

        public void SearchGeneralMemo(SqlDataSource data_source, string search_parameter)
        {
            StringBuilder command_text = new StringBuilder();
            command_text.Append("SELECT [ID], [MemoNo], [MemoDate], [BrandName], [GrpNo], [PriceType], [FromDate], [ToDate], [Status] FROM [GenMemo] WHERE ");
            if (search_parameter != "")
            {
                command_text.Append(" MemoNo  LIKE '%" + search_parameter + "%' AND Status = 'Pending' ");
            }

            data_source.SelectCommand = command_text.ToString();
            data_source.DataBind();
        }

 public SqlDataSource SearchUsers(SqlDataSource UsersDataSource, string search_parameter)
 {
     StringBuilder CommandText = new StringBuilder();
     CommandText.Append("SELECT Users.ID, Users.UserName, Users.FullName, Users.Email, lstDept.ListDesc, lstUserLevel.ListDesc AS UserLevel FROM Users INNER JOIN lstDept ON Users.DeptID = lstDept.ID INNER JOIN lstUserLevel ON Users.UserLevelID = lstUserLevel.ID WHERE (lstDept.ID = 4) ");
     if (search_parameter != "")
     {
         CommandText.Append(" AND Users.UserName LIKE '%" + search_parameter + "%'");
     }
     UsersDataSource.SelectCommand = CommandText.ToString();
     UsersDataSource.DataBind();
     return UsersDataSource;
 }

 public void AdvanceSearch(SqlDataSource datasource, string search_parameter, string filter_type, DateTime date_from, DateTime date_to, string CompanyName)
 {
     if (filter_type != string.Empty)
     {
         switch (filter_type)
         {
             case "POSTED":
                 datasource.SelectCommand = AdvanceSearchCommand(search_parameter, "1", filter_type, date_from, date_to, CompanyName);
                 datasource.DataBind();
                 break;
             case "UNPOSTED":
                 datasource.SelectCommand = AdvanceSearchCommand(search_parameter, "0", filter_type, date_from, date_to, CompanyName);
                 datasource.DataBind();
                 break;
             case "CANCELLED":
                 datasource.SelectCommand = AdvanceSearchCommand(search_parameter, "1", filter_type, date_from, date_to, CompanyName);
                 datasource.DataBind();
                 break;
             //default:
             //    string CommandText;
             //    CommandText = "SELECT [ID], [CRSID], [CRSNo], [CRSDate], [Customer], [TotalAmt], [PUSNo], [PkgNo], [ItemStatus] FROM CRS";
             //    CommandText += "WHERE CRSDate BETWEEN '" + date_from + "' AND '" + date_to + "'";
             //    if (search_parameter != string.Empty)
             //    {
             //        CommandText += " AND CRSNo LIKE '%" + search_parameter + "%' ";
             //        //CommandText += " OR ID LIKE '%" + search_parameter + "%' ";
             //    }
             //    if (!CompanyName.Equals(string.Empty))
             //    {
             //        CommandText += " AND Customer LIKE '%" + CompanyName + "%'";
             //    }
             //    datasource.SelectCommand = CommandText;
             //    datasource.DataBind();
                 //break;
         }
     }
     else
     {
     }
 }

        public void FilterStyles(SqlDataSource StylesDataSource, string Brand="")
        {
            StringBuilder commandText = new StringBuilder();
            commandText.Append("SELECT DISTINCT(Style.StyleNo) AS STYLE_NUMBER, [StyleID] AS RECORD_NO, Style.[StyleDesc] AS DESCRIPTION, Cost AS COST_PRICE,Style.BrandName,Product.AP_Type FROM [STYLE] INNER JOIN Product on Product.StyleNo=Style.StyleNo ");
            if (Brand != "")
            {
                commandText.Append(" WHERE Style.BrandName ='" + Brand + "' ");
            }
            commandText.Append(" ORDER BY StyleID DESC");

            StylesDataSource.SelectCommand = commandText.ToString();
            StylesDataSource.SelectCommandType = SqlDataSourceCommandType.Text;
            StylesDataSource.DataBind();
        }

        public void SeachOutRightmrkDownMemoIncludeDateRange(SqlDataSource OutRightDataSource, string search_parameter, DateTime date_from, DateTime date_to)
        {
            string CommandText = string.Empty;
            if(search_parameter != string.Empty)
            {
                CommandText = "SELECT MDMemo.ID, MDMemo.MemoNo, MDMemo.MemoDate, MDMemo.Header, MDMemo.Intro, CustInfo.CompName, MDMemo.RemInvDate, MDMemo.GenMemoNo, MDMemo.Message, MDMemo.Footer FROM MDMemo INNER JOIN CustInfo ON MDMemo.CustNo = CustInfo.CustNo WHERE  "
                    + "  MDMemo.MemoNo LIKE '%" +
                    search_parameter + "%' OR CustInfo.CompName LIKE '%" +
                    search_parameter + "%' AND (MDMemo.MemoType = 'Outright') AND MDMemo.MemoDate BETWEEN '"+date_from +"' AND '"+ date_to +"'";
            }else {
                CommandText = "SELECT MDMemo.ID, MDMemo.MemoNo, MDMemo.MemoDate, MDMemo.Header, MDMemo.Intro, CustInfo.CompName, MDMemo.RemInvDate, MDMemo.GenMemoNo, MDMemo.Message, MDMemo.Footer FROM MDMemo INNER JOIN CustInfo ON MDMemo.CustNo = CustInfo.CustNo WHERE  "
                      + "  MDMemo.MemoDate BETWEEN '" + date_from + "' AND '" + date_to + "' AND (MDMemo.MemoType = 'Outright')";
            }

            OutRightDataSource.SelectCommand = CommandText;
            OutRightDataSource.DataBind();
        }

 public SqlDataSource SearchOutletDataSource(SqlDataSource sql_data_source, string search_parameter)
 {
     if (search_parameter != string.Empty)
         {
             sql_data_source.SelectCommand = "SELECT [CustNo], [CompName], [CustCode], [CustType], [brand], [BrandNameNo], [ArrangeType], [Addr1], [BRAND_CODE] FROM [CustInfo] a inner join [BRANDS] b on a.[brand]=b.[BRAND_DESCRIPTION] where CompName Like '%" + search_parameter + "%'";
         }
         else
         {
             sql_data_source.SelectCommand = "SELECT [CustNo], [CompName], [CustCode], [CustType], [brand], [BrandNameNo], [ArrangeType], [Addr1] FROM [CustInfo]";
         }
         sql_data_source.DataBind();
         return sql_data_source;
 }

 public SqlDataSource SearchStyleDataSource(SqlDataSource sql_data_source, string search_parameter)
 {
     if (search_parameter != string.Empty)
         {
             sql_data_source.SelectCommand = "SELECT [StyleNo], [BrandName], [StyleDesc], [AP_Type], [BRAND_CODE] FROM [Style] a inner join [BRANDS] b on a.BrandName=b.BRAND_DESCRIPTION where StyleNo Like '%" + search_parameter + "%'";
         }
         else
         {
             sql_data_source.SelectCommand = "SELECT [StyleNo], [BrandName], [StyleDesc], [AP_Type], [BRAND_CODE] FROM [Style] a inner join [BRANDS] b on a.BrandName=b.BRAND_DESCRIPTION";
         }
         sql_data_source.DataBind();
         return sql_data_source;
 }

 public void SearchConcessionMarkDownMemo(SqlDataSource OutRightDataSource, string search_parameter)
 {
     OutRightDataSource.SelectCommand = "SELECT MDMemo.ID, MDMemo.MemoNo, MDMemo.MemoDate, MDMemo.Header, MDMemo.Intro, CustInfo.CompName, MDMemo.RemInvDate, MDMemo.GenMemoNo, MDMemo.Message, MDMemo.Footer FROM MDMemo INNER JOIN CustInfo ON MDMemo.CustNo = CustInfo.CustNo WHERE  "
         + "  MDMemo.MemoNo LIKE '%" + search_parameter + "%' OR CustInfo.CompName LIKE '%" + search_parameter + "%' AND (MDMemo.MemoType = 'Concession')";
     OutRightDataSource.DataBind();
 }

 public void SearchCustomerReturnSlipWithDateRange(SqlDataSource DataSource, string PostedStatus, string search_parameter, DateTime date_from, DateTime date_to)
 {
     string CommandText = string.Empty;
     CommandText = "SELECT [ID], [CRSID], [CRSNo], [CRSDate], [Customer], [PUSNo], [TotalAmt], [PkgNo], [ItemStatus] FROM [CRS] ";
     CommandText += " WHERE CRSDate BETWEEN '" + date_from + "' AND '" + date_to + "' ";
     if (PostedStatus != "CONFIRMED")
     {
         if (search_parameter != string.Empty)
         {
             CommandText += " AND CRSNo LIKE '%" + search_parameter + "%' OR  Customer LIKE '%" + search_parameter + "%' ";
             if (PostedStatus == "POSTED")
             {
                 CommandText += " AND ynPosted=1 AND ynCancelled=0";
             }
             else
             {
                 CommandText += " AND ynPosted=0 AND ynCancelled=0 ";
             }
         }
         else
         {
             if (PostedStatus == "POSTED")
             {
                 CommandText += " AND ynPosted=1 AND ynCancelled=0 ";
             }
             else
             {
                 CommandText += " AND ynPosted=0 AND ynCancelled=0 ";
             }
         }
     }
     else
     {
         if (search_parameter != string.Empty)
         {
             CommandText += " AND CRSNo LIKE '%" + search_parameter + "%' OR  Customer LIKE '%" + search_parameter + "%' ";
             CommandText += " AND ynCancelled=1";
         }
         else
         {
             CommandText += " AND ynCancelled=1";
         }
     }
     DataSource.SelectCommand = CommandText;
     DataSource.DataBind();
 }

 public void SearchDeliveryReceiptList(SqlDataSource datasource, string search_parameter)
 {
     string CommandText = string.Empty;
     CommandText = "SELECT [ID], [DRNo], [DRDate], [PLNo], [CustNo], [DeliveredTo], [CustAddr], [TotalQty], [TotalAmt], [ItemStatus] FROM [DR] ";
     CommandText += " WHERE DRNo LIKE '%" + search_parameter + "%' ORDER BY DRDate DESC ";//OR ID LIKE '%"+ search_parameter+"%'";
     datasource.SelectCommand = CommandText;
     datasource.DataBind();
 }