使用ASP.NET C＃on 4.5 Framework从SQL DB进行AJAX自动完成 [英] AJAX AutoComplete from SQL DB using ASP.NET C# on 4.5 Framework

问题描述

我一直在寻找适合的解决方案的几天，但是大部分内容似乎都很旧或者没有提供完整的教程。我之前从未使用过AJAX，但是我需要尝试使用它在表单中的文本框上实现自动完成功能，以便可以对我们的sql数据库进行动态查询，而无需重新加载页面，加载所有数据等。



asp.net的工作方式有时候我想知道它只是一个简单的语法或缺少参考，但我不知道是什么寻找。



这是我正在使用的：



hub.aspx
AutoComplete.asmx



两者当然都有他们的cs页面。



web.config读取这些重要位（其他漏洞省略）

I've been looking for several days for a solution that fit, but the majority of content out there seems to be really old or does not provide a complete tutorial. I've never worked with AJAX before, but I need to try and use it to implement a autocomplete feature on a textbox in a form in a way so that a dynamic query can be made against our sql database without having to reload the page, load all data, etc.

The way asp.net works sometimes I wonder if it's just a simple syntax thing or a missing reference, but I don't know what to look for.

Here's what I'm working with:

hub.aspx
AutoComplete.asmx

Both, of course have their cs pages.

The web.config reads these important bits (other fluff omitted)

<sectionGroup name="system.web">
  <section name="sanitizer" requirePermission="false"
           type="AjaxControlToolkit.Sanitizer.ProviderSanitizerSection, AjaxControlToolkit" />
</sectionGroup>


<sanitizer defaultProvider="HtmlAgilityPackSanitizerProvider">
  <providers>
    <add name="HtmlAgilityPackSanitizerProvider" type="AjaxControlToolkit.Sanitizer.HtmlAgilityPackSanitizerProvider"></add>
  </providers>
</sanitizer>

  <controls>
    <add assembly="Microsoft.AspNet.Web.Optimization.WebForms" namespace="Microsoft.AspNet.Web.Optimization.WebForms" tagPrefix="webopt" />
    <add tagPrefix="ajaxToolkit" assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" />
  </controls>

这是带控件的aspx页面中的块：

This is the chunk out of the aspx page with the control:

<div class="large-4 columns">
    <label>Company:</label>
    <asp:TextBox ID="CompanyName" runat="server"></asp:TextBox>
    <asp:AutoCompleteExtender
        ID="AutoCompleteExtender2"
        TargetControlID="CompanyName"
        runat="server"
        UseContextKey="True"
        ServiceMethod="GetCompletionList"
        ServicePath="~/AutoComplete.asmx" />
</div>

这是整个asmx服务：

This is the entirety of the asmx service:

using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using AjaxControlToolkit;

namespace Omitted_For_Reason
{
    /// <summary>
    /// Summary description for AutoComplete
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, 
    // uncomment the following line.
    [System.Web.Script.Services.ScriptService]
    public class AutoComplete : System.Web.Services.WebService
    {

        public AutoComplete()
        {

            //Uncomment the following line if using designed components
            //InitializeComponent();
        }

        [WebMethod]
        [System.Web.Services.WebMethod]
        [System.Web.Script.Services.ScriptMethod]
        public static string[] GetCompletionList(string prefixText, int count, string contextKey)
        {
            //ADO.Net
            SqlConnection cn = new SqlConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            String strCn = "data source=data012;Initial Catalog=Archive;Integrated Security=True";
            cn.ConnectionString = strCn;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            //Compare String From Textbox(prefixText) AND String From 
            //Column in DataBase(CompanyName)
            //If String from DataBase is equal to String from TextBox(prefixText) 
            //then add it to return ItemList
            //-----I defined a parameter instead of passing value directly to 
            //prevent SQL injection--------//
            cmd.CommandText = "SELECT * FROM [Archive].[dbo].[SH_Customers] WHERE CardName LIKE @myParameter";
            cmd.Parameters.AddWithValue("@myParameter", "%" + prefixText + "%");

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
            }
            catch
            {
            }
            finally
            {
                cn.Close();
            }
            dt = ds.Tables[0];

            //Then return List of string(txtItems) as result
            List<string> txtItems = new List<string>();
            String dbValues;

            foreach (DataRow row in dt.Rows)
            {
                //String From DataBase(dbValues)
                dbValues = row["CardName"].ToString();
                dbValues = dbValues.ToLower();
                txtItems.Add(dbValues);
            }

            return txtItems.ToArray();
        }
    }
}

使用此代码时会发生什么事情我开始在文本框中键入任何应该与已知数据匹配的内容。抛出没有错误或异常，它根本不起作用。再说一次，因为我还没有看到一个完整的教程，这是一个适用于其他人的各种解决方案的大杂烩，但由于没有一个来源代表一个完整的解决方案，我觉得有些东西我错过了。

What happens when I use this code is that nothing happens as I start to type anything in the textbox which should match known data. There are no errors or exceptions thrown, it simply does not work. Again, because I have yet to come across a thorough tutorial, this is a hodgepodge of various solutions that worked for other people, but since no one source represented a complete solution, I feel like there was something I missed.

The

using AjaxControlKit;

标签也被放入aspx.cs很好。



我正在使用Visual Studio 2012.我无法使用工具包对话框为扩展程序创建页面方法，因为它告诉我那里虽然我不知道它在寻找什么代码隐藏，但我在网上找到的解决方案是如此模糊，我不知道在哪里编码。


$ b $不是getcompletionlist的代码隐藏b请帮助，一旦解决并理解，我将写一篇文章，这是多年来的第一次，最新的，有史以来第一次完成。





更新＃1：



这是工具scriptmanager东西 - 它在Site.Master

tag was also placed into the aspx.cs for good measure.

I am using Visual Studio 2012. I cannot use the toolkit dialog to create a page method for the extender, because it tells me there is no codebehind for the getcompletionlist although I don't know what codebehind it's looking for and the one solution I found online was so vague I had no idea where to code.

Please help, and once this is solved and understood I'll write an article that is, for the first time in years, up to date, and for the first time ever, COMPLETE.


UPDATE #1:

Here is the toolscriptmanager stuff - it's in the Site.Master

<ajaxToolkit:ToolkitScriptManager  runat="server" ID="ScriptManager1" EnablePageMethods="true">
        <Scripts>
            <%--To learn more about bundling scripts in ScriptManager see http://go.microsoft.com/fwlink/?LinkID=272931&clcid=0x409 --%>
            <%--Framework Scripts--%>
            
            <asp:ScriptReference Name="MsAjaxBundle" />
            <asp:ScriptReference Name="jquery" />
            <asp:ScriptReference Name="jquery.ui.combined" />
            <asp:ScriptReference Name="WebForms.js" Assembly="System.Web" Path="~/Scripts/WebForms/WebForms.js" />
            <asp:ScriptReference Name="WebUIValidation.js" Assembly="System.Web" Path="~/Scripts/WebForms/WebUIValidation.js" />
            <asp:ScriptReference Name="MenuStandards.js" Assembly="System.Web" Path="~/Scripts/WebForms/MenuStandards.js" />
            <asp:ScriptReference Name="GridView.js" Assembly="System.Web" Path="~/Scripts/WebForms/GridView.js" />
            <asp:ScriptReference Name="DetailsView.js" Assembly="System.Web" Path="~/Scripts/WebForms/DetailsView.js" />
            <asp:ScriptReference Name="TreeView.js" Assembly="System.Web" Path="~/Scripts/WebForms/TreeView.js" />
            <asp:ScriptReference Name="WebParts.js" Assembly="System.Web" Path="~/Scripts/WebForms/WebParts.js" />
            <asp:ScriptReference Name="Focus.js" Assembly="System.Web" Path="~/Scripts/WebForms/Focus.js" />
            <asp:ScriptReference Name="WebFormsBundle" />
            <%--Site Scripts--%>
            <asp:ScriptReference Path="~/js/foundation.min.js" />
            <asp:ScriptReference Path="~/js/vendor/custom.modernizr.js" />
            <asp:ScriptReference Path="~/js/gauge.coffee" />
            
        </Scripts>
    </ajaxToolkit:ToolkitScriptManager>

推荐答案

只需检查您是否使用

Simply check if you are using

<asp:ScriptManager ID="ScriptManager2" runat="server"></asp:ScriptManager>

请更改以下脚本管理器以获取ajaxversion 4.5

Please make change to below script manager for ajaxversion 4.5

<ajaxToolkit:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server"></ajaxToolkit:ToolkitScriptManager>

我希望这是问题。

I hope this is the problem.

再看一件事

See one more thing

By default, the AutoCompleteExtender will not display suggestions until after you have typed at least 3 characters. You can change this default behavior by modifying the AutoCompleteExtender MinimumPrefixLength property.

或者您可以在
$中再添加一个属性b $ b

Or you can add one more properties in your

MinimumPrefixLength="1"

    <asp:AutoCompleteExtender 
                        ID="AutoCompleteExtender2"
                        TargetControlID="CompanyName"
                        runat="server" 
                        UseContextKey="True"
                        ServiceMethod="GetCompletionList"
                        ServicePath="~/AutoComplete.asmx" 
                        MinimumPrefixLength="1"  /> 

我运行了一个jquery脚本，它使用ajax作为控件，它工作正常。



我建议您阅读本页以获取更多信息。我刚接过它并用它跑了。我强烈建议你使用它，你也可以在建议之前将最小长度设置为略高于默认值，比如说3个字符，因为你想要的最后一件事就是不断对数据库征税。帮助用户获取正确的数据。



验证脚本工作后，我创建了一个新的javascript文件并导入它，以便aspx的代码隐藏不会变得难以驾驭。



感谢您的帮助！

I ran a jquery script that used ajax for the control and it worked fine.

I recommend reading This Page for more information. I just took it and ran with it. I highly recommend if you use this that you also set the minimum length before the suggestions to a little bit higher than the default, say, 3 characters, because the last thing you want is to be constantly taxing the DB with queries that don't help the user get the right data.

After verifying the script worked, I made a new javascript file and imported it so that the codebehind of the aspx does not get unruly.

Thanks for the help!

这篇关于使用ASP.NET C＃on 4.5 Framework从SQL DB进行AJAX自动完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！