Easiest Way to Run a SQL Command in ASP.NET C# Code Behind

Well… its Monday, so my post today will be exactly how I’d like my Mondays to be: Short and Sweet.

I came across this method of running a SQL command in the code behind of a ASP.NET in C# application and I loved the simplicity. Since I can’t remember where I found it, I am going to claim it as mine and take credit for it from this day forward.

Historically, this is how I’ve structured my SQL queries in my code behind.

//a bajillion lines of code to run sql query
SqlConnection sqlConnection1 = new SqlConnection("blahblahblah");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT COUNT(ID) FROM myTable WHERE (USER = @USER)";
cmd.CommandType = CommandType.Text;
SqlParameter UserID = new SqlParameter("@USER", Request.QueryString["usr"]);
UserID.Direction = ParameterDirection.Input;
cmd.Parameters.Add(UserID);
cmd.Connection = sqlConnection1;

sqlConnection1.Open();
int returnValue;
returnValue = Convert.ToInt32(cmd.ExecuteScalar());
sqlConnection1.Close();

Doesnt look too bad, right? Thats because that command is easy like Sunday morning. Damn, I just reminded myself its Monday…

Anyways, check out this new hottness:

//way less than a bajillion lines to do the same thing!
string sql;
sql = "SELECT COUNT(ID) FROM myTable WHERE (USER ='" + Request.QueryString["usr"].ToString() + "')";

SqlConnection sqlConnection1 = new SqlConnection("blahblahblah");
SqlCommand cmd = new SqlCommand(sql, sqlConnection1);

sqlConnection1.Open();
int returnValue;
returnValue = Convert.ToInt32(cmd.ExecuteScalar());
sqlConnection1.Close();

Whoa! That took hardly any lines at all, not to mention its a lot less to remember.

Well folks, that’s all I got. If you were hoping for more, I’m sorry. Here’s a picture of some Pandas taking a nap to make it up to you.

pandas

Advertisements

2 responses to “Easiest Way to Run a SQL Command in ASP.NET C# Code Behind

  1. Hi Roman, thanks for the comment. You make a great point. I am typically contracted by companies to develop applications for their internal use (CRM, Portals, etc), so building in security measures to block malicious users is not always a priority.

    I would *not* use the 2nd method if I were developing a website or an application intended to be used by the public.

    That is something I should have mentioned in the post, thank you for pointing that out.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s