Calling a stored procedure with asp.net

Question

If I have a connection string defined in my web.config file, how do I create a connection to the SQL db from C# code (sorry forgot to specify) and then call a stored procedure. I would then like to eventually use this data in some way as my DataSource for a GridView.

Here is how the connection string is defined in the web.config:

<connectionStrings>
 <add name="db.Name" connectionString="Data Source=db;Initial Catalog=dbCat;User ID=userId;Password=userPass;" providerName="System.Data.SqlClient" />
 </connectionStrings>

The db server is a Microsoft SQL server.

Here is what I was looking for:

ConnectionStringSettings conSet = ConfigurationManager.ConnectionStrings["db.Name"];
SqlConnection con = new SqlConnection(conSet.ConnectionString);

The code to get the data is fairly trivial. I was more interested in accessing it from a connectionString variable in the web.config file.

Solution 1

If it's a resource file like so:

private static readonly string connString = Resource1.connString;

Where connString is the name of the key. If it is a web.config file

Something like so:

private static readonly string connString = System.Configuration.ConfigurationManager.AppSettings["strConn"]; where conn is defined in your web config file.

<add key="strConn" value="User ID=test;Password=test;Initial Catalog=TestDB;Data Source=NameOfServer;"/>

Then call the sproc:

  //connString = the string of our database app found in the resource file
                using (SqlConnection con = new SqlConnection(connString))
                {
                    using (SqlCommand cmd = new SqlCommand("EMPDLL_selClientByClientID", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@ClientID", SqlDbType.VarChar).Value = cID;
                        con.Open();

                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                if (reader.Read())
                                {
                                       //more code
                                }
                             }
                        }
                     }
                  }

That's if you are coding in C#, VB.net its the same deal just a bit more wordier :), here's a small sample:

 Public Sub DeleteEmployee(ByVal lVID As Long)
        Dim conMyData As SqlConnection
        Dim cmdDelete As SqlCommand

        Try
            conMyData = New SqlConnection(connString)
            cmdDelete = New SqlCommand("delEmployee", conMyData)

            With cmdDelete
                .CommandType = CommandType.StoredProcedure
                'add the parameters
                .Parameters.Add("@LoginID", SqlDbType.BigInt).Value = lVID    'the request
                conMyData.Open()    'open a connection
                .ExecuteNonQuery()  'execute it
            End With

        Catch ex As Exception
            Throw ex
        Finally
            cmdDelete = Nothing
            conMyData.Close()
            conMyData = Nothing
        End Try
    End Sub

Of course you should use a using statement instead of try/catch/finally to ensure you clean up your resources that are being used.

Solution 2

Something like this...

using (var con = new SqlConnection(_connectionString))
{
    using (var cmd = new SqlCommand(_storedProcedureName, con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@pMyParamater", myParamaterValue);
        con.Open();

        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                 // do something with the row
            }
        }
    }
}

This is all pretty simple stuff to be honest, you should be able to find everything you need from the ADO.NET documentation

Comments

[LoganS]

Ack if you know the type why var ?

[onit]

I realize this is not complicated stuff. I was just wondering if someone could point me in the right direction since I literally just started .NET programming.

[onit]

I don't want to add a key to my web.config (otherwise I have to edit multiple web.config files) or type it as a static variable. Is it possible to access the connection string if it is defined in my web.config as in my post.

[LoganS]

@onit that is inside of a web config file.

[fearofawhackplanet]

@onit Yes I realise that, it wasn't meant as criticism. If you are new to something, it's good to get into the habit of reading the documentation as a first point. Then ask a more specific question on SO if you still get stuck. You will learn more that way, and you will avoid some of the bad advice that is sometimes given on this site.

[LoganS]

@onit - if you want just that specific section you can do _SQLDBConnString = System.Configuration.ConfigurationManager.ConnectionStrings(‌​1).ConnectionString(‌​) and use the ConnectionStrings property rather then appsettings section.

[LoganS]

@onit - if you dont want app settings use the connection string property: string test = ConfigurationManager.ConnectionStrings[0].ConnectionString;

[Allan Chua]

@JonH this wont work... App settings could not be queried if you are trying to use ConfigurationManager.appSettings() it should go this way ConfigurationManager.appSettings[]

[LoganS]

@Allan chua - That was a typo as I work in both C# and vb.net. You should be able to edit my posts as stack overflow allows for that.

[onit]

I gave you a +1 for effort, though not exactly what I was looking for. I think Allan Chua had the right answer in his post but it got deleted.

[LoganS]

Don't really care for the points, here to help. If its not what you were looking for then rephrase your question and add more details. A bad question gets a bad / semi bad answer. You asked how to get the conn string from a web config file. I think I gave you more then that.