How to Execute sql script in the webserver in .net c# web application?

Question

I'm pretty new in .net, I hope this question will not sound stupid. How can I do the following sql script to the database in a webmethod in .net c#?

Under Web.config I have code the following code for connecting to the database:

<connectionStrings>
    <add name="TestConnectionString" connectionString="Data Source=TXT-TEST-SQL-02;Initial Catalog=Test;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

How can I do under test.asmx.cs - webmethod to run the following script from the database to retrieved the data from the table?

[WebMethod]
public string testSearch(int id)
{
    return result;
}

SELECT name
FROM Customer
WHERE customer_id = id

Bala R · Accepted Answer · 2011-04-06 23:35:28Z

3

Try

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

and

[WebMethod]
public string testSearch(int id)
{
    string connString = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(connString))
    {

        String sql = "SELECT name FROM Customer WHERE customer_id = @id";
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@id", SqlDbType.Int);
        cmd.Parameters["@id"].Value = id;
        String result = "";
        try
        {
            conn.Open();
            result = (string)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        return result;
    }
}

edited Apr 6, 2011 at 23:35

answered Apr 6, 2011 at 23:27

Bala R

109k23 gold badges203 silver badges212 bronze badges

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

8 Comments

Jin Yong Over a year ago

@Bala:It's show me error that said ' The name 'ConfigurationManager' does not exist in the current context

Bala R Over a year ago

@Jin Yong make sure you have all the required using.

Jin Yong Over a year ago

@Bala: cool... this is working for me now, but If I run the script will return more then 1 record, can I still using ExecuteScalar()? or I should use other function to retrieve those records and assign into result in array format?

Bala R Over a year ago

@Jin yes ExecuteScalar will pick the first record.

Jin Yong Over a year ago

@Bala: how about if I want to retrieve all records instead of the 1st record only? how can I do it? example if I run the following script [select name from customer order by customer name]

|

Collectives™ on Stack Overflow

How to Execute sql script in the webserver in .net c# web application?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related