Call Business Layer class method inside Web Service in ASP.Net using C# and VB.Net

Last Reply 9 months ago By dharmendr

Posted 9 months ago

by using below code i am successfully return names in autocomplete textbox and i am selected hiddenfield value. but my requirement is, i dont want to write sqlcommands in Webservices, i want return List values from DAL (Data Access Layes- write sqlcommands  to BAL (Businness Access Layer- return data from DAL) to .ASMX webservice. Please explain with example how to return from DAL To BAL to .ASMX.

below is my code

<script type="text/javascript">
 function SelectUserId(sender, e) {
        $get("<%=hfUsrId.ClientID %>").value = e.get_value();
    }
</script>
<asp:scriptmanager id="ScriptManager1" runat="server" enablepartialrendering="true" enablepagemethods="true" scriptmode="Release">
 <Services>
 <asp:ServiceReference Path="~/Autocomplete.asmx" />
 </Services>
</asp:scriptmanager>
 <asp:TextBox ID="TxtUsrId" runat="server" placeholder="Select User name" Width="300px"></asp:TextBox>
     <asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" ServiceMethod="SearchUsrId" MinimumPrefixLength="0" ServicePath="~/Autocomplete.asmx"
   CompletionInterval="100" EnableCaching="false" CompletionSetCount="10" TargetControlID="TxtUsrId"
                        FirstRowSelected="false" OnClientItemSelected="SelectUserId">
  </asp:AutoCompleteExtender>
  <asp:HiddenField ID="hfUsrId" runat="server" />
  <asp:button id="BtnSubmit" runat="server" Text="Submit" cssclass="Btns" onclick="BtnSubmit_Click" />

Code Behind 

protected void BtnSubmit_Click(object sender, EventArgs e)
{           
    string Id=hfUsrId.Value;
}		

 Under Autocomplete.asmx: 

        [WebMethod]
        public List<string> SearchUsrId(string prefixText)
        {
  
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CONNECTION"].ToString());

            SqlCommand cmd = new SqlCommand("USP_SEARCH_USERNAME", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Usr_Name", prefixText);
           
            con.Open();
            List<string> Usr_Id = new List<string>();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    string item = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(sdr["Usr_Nm"].ToString(), sdr["User_Id"].ToString());
                    Usr_Id.Add(item);
                }
                sdr.Close();
            }
            con.Close();
            return Usr_Id;  
        }

Thanks in advance

Posted 9 months ago

Hi nagaraju60,

You just create a class and return List from the class, then call the class in Web Service.

Check this example. Now please take its reference and correct your code.

DataAccess.cs

public List<string> GetCustomerIds(string prefixText)
{
    System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString());
    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("select ContactName,CustomerId from Customers where ContactName like @SearchText + '%'", con);
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.Parameters.AddWithValue("@SearchText", prefixText);
    con.Open();
    List<string> customerIds = new List<string>();
    using (System.Data.SqlClient.SqlDataReader sdr = cmd.ExecuteReader())
    {
        while (sdr.Read())
        {
            string item = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(sdr["ContactName"].ToString(), sdr["CustomerId"].ToString());
            customerIds.Add(item);
        }
        sdr.Close();
    }
    con.Close();
    return customerIds;
}

DataAccess.vb

Public Function GetCustomerIds(ByVal prefixText As String) As List(Of String)
    Dim con As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("constr").ToString())
    Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("select ContactName,CustomerId from Customers where ContactName like @SearchText + '%'", con)
    cmd.CommandType = System.Data.CommandType.Text
    cmd.Parameters.AddWithValue("@SearchText", prefixText)
    con.Open()
    Dim customerIds As List(Of String) = New List(Of String)()
    Using sdr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
        While sdr.Read()
            Dim item As String = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(sdr("ContactName").ToString(), sdr("CustomerId").ToString())
            customerIds.Add(item)
        End While

        sdr.Close()
    End Using

    con.Close()
    Return customerIds
End Function

asmx webservice

CS

[WebMethod]
public List<string> SearchUsrId(string prefixText, int count)
{
    DataAccess data = new DataAccess();
    List<string> Usr_Id = data.GetCustomerIds(prefixText);
    return Usr_Id;
}

VB.Net

<WebMethod>
Public Function SearchUsrId(ByVal prefixText As String, ByVal count As Integer) As List(Of String)
    Dim data As DataAccess = New DataAccess()
    Dim Usr_Id As List(Of String) = data.GetCustomerIds(prefixText)
    Return Usr_Id
End Function

Then call the web service in aspx page.