How to create a .NET web service to insert data in SQL Server 2008

Question

I am finding it difficult to implement a .NET web service to insert data in my database created in SQL Server 2008 . I am stuck after implementing the following code :

namespace DataService
{
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]

    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {
        [WebMethod]
        public string HelloWorld(String entity)
        {
            String firstName = "";
            SqlConnection myConnection = new SqlConnection(
                @"Data Source=.\SQLEXPRESS;" + 
                @"Initial Catalog=student;User ID=sa;Password=123");
            try
            {
                myConnection.Open();
                SqlCommand myCommand = new SqlCommand();
                myCommand.Connection = myConnection;
                myCommand.CommandText = "insert into stud values " +
                    "stud_name = '" + firstName + "'";

                SqlDataReader myReader = myCommand.ExecuteReader();

                //while
                if (myReader.Read())
                {
                    firstName = myReader["stud_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            { 
                myConnection.Close();
            }

            return firstName;
        }
    }
}

Here entity is the JSONArray i get in the form:

[{"key0":"john","key2":"ann","key1":"joe"}]

I need to insert each value for eg "john" in the database table.

Whats your exception? Does your aspnet Worker have all required privilegues to actually access the db? Also you are executing a reader, why not execute a non query. Also, wrap that connection in a using. — user604613
– user604613, Commented Aug 30, 2011 at 14:46
Yeah there's many things that could be happening here. Debug it and find out what the exception is. It's probably as @UrbanEsc said that you either have invalid permissions as a service to access the DB, or you could be exhausting the DB connection pool by not Dispose()-ing the connections to free them in the pool faster. — James Michael Hare
– James Michael Hare, Commented Aug 30, 2011 at 14:50
I am getting an exception for the insert query . I don't knw how to execute a non query . Can u tell me how to do that ? Btw , I have the access privileges for the database in SQL Server . I am working on localhost. The insertion part is giving me a problem — Parth Doshi
– Parth Doshi, Commented Aug 30, 2011 at 14:50
You should be serializing the data you receive with something like Newtonsoft.Json — bevacqua
– bevacqua, Commented Aug 30, 2011 at 14:52
@Nico : what is Newtonsoft.Json ? How can I use it ? What will happen if I serialize the JSONArray ? — Parth Doshi
– Parth Doshi, Commented Aug 30, 2011 at 14:54

Icarus · Accepted Answer · 2011-08-30 16:01:25Z

3

Parth_90:

First, if you are getting a JSONArray from the client side, I suggest you Deserialize it first in a List

JavascripSerializer js = new JavascriptSerializer<List<sring>>();
List<string> recordsToInsert= js.Deserialize(entity);

Then you can insert every record in the table by either looping through them and enclosing everything in a Transaction.

foreach(name in recordsToInsert)
{
 //perform the table insert here... 
 //Your SQL code seems correct to me. 
}

Improvement: Enclose the insert statements in a SQLTransaction.

EDIT: Adding some code demonstrating how to Deserialize the example JSON string provided in the question:

private static void DoSerializationTest()
    {
        string entity="{\"key0\":\"john\",\"key2\":\"ann\",\"key1\":\"joe\"}";
        JavaScriptSerializer js = new JavaScriptSerializer();

        Dictionary<string,string> result= js.Deserialize<Dictionary<string,string>>(entity);
        foreach (var item in result)
        {
            Console.WriteLine("Value: "+item.Value);
            Console.WriteLine("Key :"+item.Key);
        }
    }

edited Aug 30, 2011 at 16:01

answered Aug 30, 2011 at 15:10

Icarus

64k14 gold badges102 silver badges116 bronze badges

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

7 Comments

Parth Doshi Over a year ago

Where should I write the serializer code , should I write it in my Android app ?

Icarus Over a year ago

@Parth_90: No, the JavascriptSerializer is part of the .NET Framework, you don't need to write it. See the documentation here: msdn.microsoft.com/en-us/library/…

Parth Doshi Over a year ago

After serializing what will be my output ? I am new to all this in .NET please can u help give me some more idea regarding the code ?

Icarus Over a year ago

@Parth_90: The C# code is there also, simply click on the tabs with the C# name on them to see the examples in C#.

Parth Doshi Over a year ago

After serializing what will be my output ? I am new to all this in .NET please can u help give me some more idea regarding the code ?

|

daniloquio · Accepted Answer · 2011-08-30 15:03:03Z

1

You need to execute your command with ExecuteNonQuery(); ExecuteReader is for when you execute a select to get data from the database.

  SqlCommand myCommand = new SqlCommand();
                    myCommand.Connection = myConnection;
                    myCommand.CommandText = "insert into stud values " +
                        "stud_name = '" + firstName + "'";
     if(myCommand.ExecuteNonQuery() > 0)
     {
         //The command executed with affected rows > 0, OK.
     }
     else
     {
         //No rows affected by the execution. Error management.
     }

answered Aug 30, 2011 at 15:03

daniloquio

3,9232 gold badges41 silver badges57 bronze badges

Collectives™ on Stack Overflow

How to create a .NET web service to insert data in SQL Server 2008

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related