How to use created connection string of Web.Config in my C# asp.net 4.0 project?

Question

Actually I am new in this topic so required some help.

I have added connection string in Web.Config

    <connectionStrings>
    <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
    </connectionStrings>

and know that, to use it I have to put this statement in my C# code behind

string connStr = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;

That's all I know.

My Question is

What should I do if I want to execute some query for my aspnetdb.mdf dataabase (Built in db of ASP.NET built in login contols in Visual Studio 2010)

Earlier, I was doing this to accomplish my task
1) No connection string in Web.Config. and
2) Hard code in codebehind

SqlConnection con = new SqlConnection("data source=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true");
SqlCommand cmd = new SqlCommand();

protected void btnnameedit_Click(object sender, EventArgs e)
    {
        try
        {
            con.Open();
            cmd.CommandText = "update tamhankarnikhil set fname = '" + fname.Text + "'";
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
            con.Close();
            fname.Text = "";
        }

        catch (Exception a)
        {
              Response.Write(a.Message);
        }
    }

Greg Bray · Accepted Answer · 2013-08-09 14:57:08Z

4

Here's what you could do:

protected void btnnameedit_Click(object sender, EventArgs e)
{
    try
    {
        string connStr = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
        using (var conn = new SqlConnection(connStr))
        using (var cmd = conn.CreateCommand())
        {
            conn.Open();
            cmd.CommandText = "UPDATE tamhankarnikhil SET fname = @fname";
            cmd.Parameters.AddWithValue("@fname", fname.Text); 
            cmd.ExecuteNonQuery();
            fname.Text = "";
        }
    }
    catch (Exception a)
    {
        Response.Write(a.Message);
    }
}

You will notice the usage of parametrized queries to avoid SQL injection to which your code was vulnerable to due to the string concatenations you were using when constructing the SQL query.

You will also notice that the SqlConnection and SqlCommand are wrapped in using statements to ensure their proper disposal even in the event of an exception.

edited Aug 9, 2013 at 14:57

Greg Bray

15.9k13 gold badges86 silver badges107 bronze badges

answered Sep 25, 2011 at 8:46

Darin Dimitrov

1.0m275 gold badges3.3k silver badges3k bronze badges

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

2 Comments

Nikhil Tamhankar Over a year ago

cmd.Parameters.AddWithValue("@fname", fname.Text); is not working

Greg Bray Over a year ago

fixed con typo. fname.Text is where he set the value for the @fname parameter in the SQL statement. Replace fname.Text with the source value in your application.

Collectives™ on Stack Overflow

How to use created connection string of Web.Config in my C# asp.net 4.0 project?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related