You must Sign In to post a response.
  • Category: ASP.NET

    Elastic Search using asp.net, c# and SqlServer

    Hi
    I am very much new to Elastic Search.

    I have got a requirement to search records from database and display it on a Webpage using ElasticSearch2.3.2

    Currently I am using asp.net , c# and SqlServer

    The description of the requirement is as follows.

    For Example
    I have a table in sqlserver database. Let us assume the name is orders.
    ---------------------------------------------------------
    Cutomer id ---- Customer Name --- Location
    ---------------------------------------------------
    1--- Abc --- Mumbai
    2--- Xyz --- Delhi
    3--- Pqr --- Bangalore.
    ------------------------------------------------------

    Now by giving the CustomerID as input from a Textbox, the application must display the details of the concerned CustomerID and details related to it

    For example if I give "1", it must give the below results

    ---------------------------------------------------------
    Cutomer id Customer Name Location
    ---------------------------------------------------------
    1--- Abc--- Mumbai



    Please help me in a step by step procedure how can I achieve this using Elastic Search
    So as per my understanding and analysis , I have downloaded the software for Elastic search and installed it. Also I have installed Java in my machine (Elastic search requires Java to be runned in the machine). Please help me by giving any sample code or else any links where I can understand correctly. I have verified couple of links but were not that clear.

    I am totally frustrated searching the websites but not able to find out any resolutions for my problem.

    Thnks
    vasu
  • #767223
    Hi
    Mvasu

    What do you mean Elastic Search can you explain. But I given solution which you post in your issue.


    Step1

    Create Table


    create table TempDST
    (
    Cutomerid int primary key identity(1,1),
    CustomerName varchar(50),
    Location varchar(50)
    )


    Step2

    Inserted Records


    --Insert into TempDST values ('Abc','Mumbai')
    --Insert into TempDST values ('Xyz','Delhi')
    --Insert into TempDST values ('Pqr','Bangalore')


    Step 3

    Output



    Cutomer id ---- Customer Name --- Location
    ---------------------------------------------------
    1--- Abc --- Mumbai
    2--- Xyz --- Delhi
    3--- Pqr --- Bangalore.



    Select * from TempDST

    Step 4

    Create Procedures


    create proc sp_GetCustomer(@CustId int)
    as
    begin
    Select Cutomerid,CustomerName,Location from TempDST where Cutomerid=@CustId
    END


    Step 5

    run Procedure


    exec sp_GetCustomer 1




    output



    1 Abc Mumbai


    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #767224
    Hi

    If you need call from c# code means try this code



    SqlConnection myConnection = new SqlConnection(dsn);
    SqlCommand myCommand = new SqlCommand("exec sp_GetCustomer @CustId ", myConnection);
    myCommand.Parameters.Add(new SqlParameter("@CustId", SqlDbType.Int));
    myCommand.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter sqlAdapter = new SqlDataAdapter(myCommand);
    //create a new dataset to hold our data
    DataSet ds = new DataSet();
    //fill the dataset with the result of our query from the specified command
    sqlAdapter.Fill(ds);

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #767225
    Elasticsearch is a search engine based on Lucene. It provides a distributed, multitenant-capable full-text search engine with an HTTP web interface and schema-free JSON documents
    To use it in c#, Install-Package Elasticsearch.Net
    go through below link for more details
    https://www.elastic.co/guide/en/elasticsearch/client/net-api/1.x/elasticsearch-net-quick-start.html

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #767229
    HI,
    Use 'filter' clause and specify the column name as 'term':

    {
    "sql":"select Cutomer_id, Customer_Name, Location from orders",
    "index":"orders",
    "filter" : {
    "term" : {
    "id" : "1"
    }
    },
    }

    You may find some details about 'How to use Elasticsearch with SQL Server' over here:
    https://www.pluralsight.com/blog/it-ops/elasticsearch-and-sql-server


Sign In to post your comments