return data in JSON format using restful web service

Question

I am creating one web service which will fetch the data from database and return into json.but i amnot getting data.when i am running the code than in result i am getting "\"System.Data.SqlClient.SqlDataReader\"" so what have tried now is below.can you help me where i have to correct the code.

Iservice.cs

namespace SearchService
{

    [ServiceContract]
    public interface IService1
    {

        [OperationContract]

        string Search(string keyword);


    }

}

service1.cs

namespace SearchService
{

    public class Service1 : IService1
    {
        public string Search(string keyword)
        {

            var json = "";
            var data = "";

            SqlConnection sql_Search;


            sql_Search = new SqlConnection("Data Source=.;Initial Catalog=student_info;Integrated Security=True");
            sql_Search.Open();

            SqlCommand SelectCommand = new SqlCommand("SELECT Student_ID,First_Name,Last_Name FROM record Where (First_Name Like '%" + keyword + "%')", sql_Search);


            SelectCommand.ExecuteNonQuery();

            SqlDataReader read = SelectCommand.ExecuteReader();

            while (read.Read())
            {

                data = read.ToString();
            }

            JavaScriptSerializer JavaSerialzer = new JavaScriptSerializer();
            json = JavaSerialzer.Serialize(data);
            return json;
        }
    }
}

so what is the answer – rajeev tejapuriya Mar 11 '15 at 11:58 — rajeev tejapuriya, Mar 11 '15 at 11:58

Soner Gönül · Answer 1 · 2015-03-11T11:59:41.643

Since you write it as;

read.ToString()

it will return the full name of that class obviously.

If you wanna read your column values, you can access them as read[0], read[1] and read[2] etc. Or you can use SqlDataReader's GetXXX methods as well.

By the way, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks. Use using statement to dispose your connection, command and reader as well.

score 0 · Accepted Answer · edited May 23 '17 at 11:50

0

read.ToString() will return the type of read, which is "System.Data.SqlClient.SqlDataReader".

You need to read the data as an object and serialize that. See Convert rows from a data reader into typed results how to do that.

Easier even is to use an ORM to generate classes and read statically-typed objects from your database instead of building a query from a string (use parameterized queries) and then going to build an object from that reader.

But there's a lot more, fundamentally wrong with your code. You shouldn't be returning a JSON string wrapped in a (presumably) SOAP object from a WCF service, use the proper binding and just return the object itself, or use WebAPI.

edited May 23 '17 at 11:50

Community

1
1

answered Mar 11 '15 at 11:53

CodeCaster

147,647
23
218
272

Pretty big call to say someone should be using an ORM. – CRice Mar 11 '15 at 11:56
i am not getting the exact answer,so can you write that code. – rajeev tejapuriya Mar 11 '15 at 11:57
1

@CodeCaster I would have to agree with CRice. I would use the DataContractJsonSerializer to build JSON before I would ever recommend using an ORM – Matthew Frontino Mar 17 '15 at 20:23
@Matthew that's nice, but I don't really see a compelling argument why. – CodeCaster Mar 17 '15 at 20:24
@CodeCaster because the minute you run into a situation where your code is no longer performant, you've abstracted out the layer where you can do the most tuning. You can potentially end up with a bad data model, should your application grow to the point that you need more than just container tables. So to recommend ORM to a beginner would be MORE risky as they do not know the repercussions of using it or how to actually write a good data model. – Matthew Frontino Mar 17 '15 at 20:35
@Matthew not following you nor agreeing with that. You can create an equally bad data model while reading with `SqlDataReader` as opposed to using an ORM. ORM does not necessarily mean "code first". It is (in terms of code) easier to get data out of a database with an ORM, hence it's very fitting for beginners. Beginners also don't often run into performance problems. – CodeCaster Mar 17 '15 at 20:37
@CodeCaster Hm, perhaps you don't work on applications that scale very large. Regardless, the value using an ORM is fast code creation and deployment. If OP is a beginner, then ORM is a shortcut to get his application running. For the same reason we learn Assembly and C in school long before we become masters at the high level languages we use today. It would be like having a student dictate his book report to dragon voice recognition than actually learning to type himself – Matthew Frontino Mar 17 '15 at 20:49

return data in JSON format using restful web service

2 Answers2