replace the index of array with table id in database

Question

0.00/5 (No votes)

See more:

C#

DataTable dt = new DataTable();

     public string GetCountries(string filter)
     {

         string[] countriesArray = new string[dt.Rows.Count];
         for (int i = 0; i < dt.Rows.Count; i++)
         {
             countriesArray[i] = dt.Rows[i]["country"].ToString();
         }

         var query =
             from c in
                 countriesArray.Select((item, index) => new { id = index, name = item })
                 .Where(p => p.name.IndexOf(filter, StringComparison.InvariantCultureIgnoreCase) >= 0)
             select new { id = Convert.ToString(c.id), name = c.name };


         return new JavaScriptSerializer().Serialize(query);
     }

     protected void Page_Load(object sender, EventArgs e)
     {
         da.SelectCommand = new SqlCommand("select * from Country", cn);
         da.Fill(dt);

         //Check if logged in
         string filter = this.Request.QueryString["q"];
         if(String.IsNullOrEmpty(filter))
             this.Response.Redirect("~/Default.aspx");

         string list = this.GetCountries(filter);
         this.Response.Clear();
         this.Response.ContentType = "text/plain";
         this.Response.Write(list);
         this.Response.End();
     }

the query variable in quick watch display like this:

{id=0,name=Canada}
{id=1,name=USA}

where id is the index of array
how can replace the index with the id of the country in the database ?

note : the dt (datatable) contain two columns id and country

Posted 4-Jun-15 1:03am

tranesyasd

Updated 4-Jun-15 1:16am

CHill60

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2015-06-04T01:35:00

Let SQL do the filtering for you:

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        string filter = Request.QueryString["q"];
        if (string.IsNullOrEmpty(filter))
        {
            Response.Redirect("~/Default.aspx");
            return;
        }
        
        DataTable list = GetCountries(filter);
        string json = SerializeCountries(list);
        
        Response.Clear();
        Response.ContentType = "text/plain"; // This should really be "application/json" or "application/javascript"
        Response.Write(json);
        Response.End();
    }
}

private DataTable GetCountries(string filter)
{
    using (var connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["YourConnectionString"].ConnectionString))
    using (var command = new SqlCommand("SELECT id, country FROM Country WHERE country Like @filter", connection))
    {
        command.Parameters.AddWithValue("@filter", "%" + filter + "%");
        
        var table = new DataTable();
        var da = new SqlDataAdapter(command);
        da.Fill(table);
        return table;
    }
}

private string SerializeCountries(DataTable countries)
{
    var query = from DataRow row in countries.Rows
                select new
                {
                    id = Convert.ToString(row["id"]),
                    name = Convert.ToString(row["country"])
                };
    
    return new JavaScriptSerializer().Serialize(query);
}