CODEWITHSUNDEEP
asp.netsql-serverc#-4.0database-connectionconnection-string
Nikhil Tamhankar
Nikhil Tamhankar

Reputation: 439

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

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);
        }
    }

Upvotes: 0

Views: 14293

Answers (1)

Darin Dimitrov
Darin Dimitrov

Reputation: 1038810

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.

Upvotes: 4

Related Questions