Insert values of a String array into SQL database

Question

I have a Web Service(.ASMX) code that takes a String Array as a parameter and then inserts each individual value in the database

For example let's say I have array like:

            String arr[]={"john","annie"};

Now I want to put "john" and "annie" in a column in my database

I have implemented a code for it but it's not working and the values are not inserted. Also is there any way to test my webservice application on localhost in my browser.

Here is the web service code:

      public class Service1 : System.Web.Services.WebService
     {
    [WebMethod]

    public String getnames(String[] values)
    {

        try
        {

            using (SqlConnection myConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=student;User ID=sa;Password=123"))
            {
                int count = 1;
                int rows = 0;

                myConnection.Open();
                foreach (string student in arr)
                {
                    count++;
                    using (SqlCommand myCommand = new SqlCommand())
                    {
                        myCommand.Connection = myConnection;
                        myCommand.CommandText = "insert into record values(@pCount, @pStudent)";
                        SqlParameter param = myCommand.CreateParameter();
                        param.ParameterName = "@pCount";
                        param.Value = count;
                        myCommand.Parameters.Add(param);

                        param = myCommand.CreateParameter();
                        param.ParameterName = "@pSudent";
                        param.Value = student;

                        rows = myCommand.ExecuteNonQuery();
                    }
                }
            }
        }

        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return "an error occured";
        }


        return "success";
        }
     }
   }

When you say not working... what error are you getting? What does the table look like? — CodeRedick
– CodeRedick, Commented Nov 10, 2011 at 15:26

Rico · Accepted Answer · 2011-11-10 16:50:53Z

YOu can debug your service by making the asmx the startpage and clicking run to debug. You should be able to fill in a value into the text box if you are accepting paramaters.

I would do it different I would create a Stored proc to handle the work that looks like this The code assumes a delimited string so its not an array scenario.. but its awesome code..

create Proc [dbo].[InsertINtoRecords] (@sep VARCHAR(32), @s VARCHAR(MAX))
AS

    BEGIN
        Declare @t TABLE
        (
            val VARCHAR(MAX)
        )   


        DECLARE @xml XML
        SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'

        INSERT INTO @t(val)
        SELECT r.value('.','VARCHAR(100)') as Item
        FROM @xml.nodes('//root/r') AS RECORDS(r)

        insert into record(Count, Student) Select ROW_NUMBER() OVER (ORDER BY val) AS Count, Val

    END

Then your code would only have to do this

                Names = "Johny,Anne,sally"
                myCommand.Connection = myConnection;
                myCommand.CommandText = "InsertINtoRecords ',', @Names";
                rows = myCommand.ExecuteNonQuery();

Good luck

Orn Kristjansson · Accepted Answer · 2011-11-10 16:03:00Z

2

You looped arr instead of values. arr might not have any values at all.

If this is a C# webservice, you can open the url to your webservice in a browser, something like localhost/yourservice.asmx and it will show you the functions in the webservice click a function and it will give you a form to trigger the function.

answered Nov 10, 2011 at 16:03

Orn Kristjansson

3,5055 gold badges28 silver badges42 bronze badges

Comments

Srikanth · Accepted Answer · 2011-11-10 16:36:02Z

2

This code should throw an compilation error as 'arr' is not in the context. Try looping through 'values'.

answered Nov 10, 2011 at 16:36

Srikanth

3091 silver badge2 bronze badges

Collectives™ on Stack Overflow

Insert values of a String array into SQL database

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related