how to populate list from sql server database in asp.net using json and ajax

Question

i want to populate a list from database i am using jquery mobile , asp.net, C#, and sql server database

i wrote a service to get the data from the database as a dtataset but i couldn't convert the dataset to something json can understand

so this is my service

[System.Web.Services.WebMethod]
    public static DataSet GetProducts()
    {
        string query = "SELECT [product] ,[img1] ,[descr] FROM [ELQ].[dbo].[products]";
        SqlCommand cmd = new SqlCommand(query);

        return GetData(cmd);

    }
    private static DataSet GetData(SqlCommand cmd)
    {
        string connString = "Data Source=GHOST-PC\\STC;Initial Catalog=ELQ_z;Integrated Security=True";
        using (SqlConnection con = new SqlConnection(connString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);
                    return ds;
                }
            }
        }
    }

and this is my list

 <ul data-role="listview" data-inset="true" data-filter="true">
                <li><a href="ray.html">
                    <img src="pic/ip5.jpg">
                    Iphone </a></li>
                <li><a href="scott.html">
                    <img src="pic/s2.jpg">
                    S2</a></li>
                <li><a href="todd.html">
                    <img src="pic/s3.jpg">
                    S3</a></li>
                <li><a href="dave.html">
                    <img src="pic/nt2.jpg">
                    note2</a></li>
            </ul>

how can i convert this to json and populate the list with ajax

Take a look at this west-wind.com/weblog/posts/2008/Sep/03/… — Marcus Vinicius
– Marcus Vinicius, Commented Mar 14, 2013 at 17:35

PilotBob · Accepted Answer · 2013-03-14 22:42:53Z

1

What API are you using in ASP.Net? Are you using web forms, or mvc or webapi?

If you want to keep your code as above, you will want to create a DTO (data transfer object) class and populate it from your data set. You can then serialize it using Json.Net which you can easily install using NuGet.

Probably a better option is to use WebAPI and Entity Framework. WebAPI will do the serialization for you. It is a great way to create an API that you jQuery Mobile app can access to GET, POST, PUT and Delete data.

I'm sure if you google getting started with WebAPI you will find many getting started tutorials in both print and video form.

answered Mar 14, 2013 at 22:42

PilotBob

3,1177 gold badges38 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Seif sammain Over a year ago

Thanx pilotBob it helped me allot

PilotBob Over a year ago

Thanks, glad to help. Any change you could mark this as the answer?

PilotBob Over a year ago

and then you changed it to your own answer. Ok.

Seif sammain Over a year ago

well i didn't want to take credit but i did it for beginners like me who want a solution with an example, so if u think i shouldn't do that i ll mark your answer as the correct answer because u really deserve it body

PilotBob Over a year ago

no problem... I'm not that concerned about my rep points. Glad you got a solution that works for you.

Seif sammain · Accepted Answer · 2013-03-15 03:22:09Z

i used the DTO (data transfer object) way and here is my code

first i wrote a class product

public class product
{
    //[product] ,[img1] ,[descr]
    public string name;
    public string img1;
    public string descr;

}

and then i altered the getdata() method to be like this

[WebMethod]
    public List<product> getdata()
    {
        List<product> productt = new List<product> {};
        string query = "SELECT [product] ,[img1] ,[descr] FROM [ELQ].[dbo].[products]";
        SqlCommand cmd = new SqlCommand(query);
        DataSet ds = GetData(cmd);
        DataTable dt = ds.Tables[0];
        foreach(DataRow item in ds.Tables[0].Rows)
        {
            product pro = new product();
            pro.name = item["product"].ToString();
            pro.img1 = item["img1"].ToString();
            pro.descr = item["descr"].ToString();
            productt.Add(pro);
        }

        return productt; 
    }

Collectives™ on Stack Overflow

how to populate list from sql server database in asp.net using json and ajax

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related