Reputation: 183

Use of commercial @AT when dealing with data access in ASP.Net?

Two small issues, mostly concerning the @AT syntax when dealing with data in ASP.Net (C#). Most online tutorials show a lot of this following type of code but fail to mention (or I may have overlooked) the actual purpose of the ampersand although they do explain the general purpose of the code. In this example, just querying the database to get data pertaining to a certain month for a calender control.

protected DataSet GetCurrentMonthData(DateTime firstDate, 
    DateTime lastDate)
{
    DataSet dsMonth = new DataSet();
    ConnectionStringSettings cs;
    cs = ConfigurationManager.ConnectionStrings["ConnectionString1"];
    String connString = cs.ConnectionString;
    SqlConnection dbConnection = new SqlConnection(connString);
    String query;
    query = "SELECT HolidayDate FROM Holidays " + _
    " WHERE HolidayDate >= @firstDate AND HolidayDate < @lastDate";
    SqlCommand dbCommand = new SqlCommand(query, dbConnection);
    dbCommand.Parameters.Add(new SqlParameter("@firstDate", 
    firstDate));
    dbCommand.Parameters.Add(new SqlParameter("@lastDate", lastDate));

    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(dbCommand);
    try
    {
        sqlDataAdapter.Fill(dsMonth);
    }
    catch {}
    return dsMonth;
}

First issue: What do @firstDate and @lastDate mean or reference in the database string query? Is that referencing the parameters being passed in GetCurrentMonthData method or the actual column name in the database table ?

 query = "SELECT HolidayDate FROM Holidays " + _
    " WHERE HolidayDate >= @firstDate AND HolidayDate < @lastDate";

Second issue: What is the difference between @firstDate and firstDate? Am I correct in saying firstDate is the actual parameter variable itself?

dbCommand.Parameters.Add(new SqlParameter("@firstDate", 
    firstDate));

Upvotes: 0

Answers (4)

sriman reddy

Reputation: 773

The main use of @inputvalue in query statement is to avoid sql injection attacks. If you use normal concatenation method in building query statement, Hackers can easily bypass the statements with sql injection.

Eg:

"Select * from user where username ='" + username.text + "' and password ='" + password.text + "'"

If you use the above statement to validate the user login, think what will happen if the user types a' = 'a') or 'sometext in username textbox and sometext in password box. It will returns more than one record on execution and enters into account area, if you checks with no of return records in user validation.

To avoid this, Microsoft introduced @parameter to build sql query statements. Whatever value you pass in the @parameter is considered as input parameter value and you can't inject sql statements in it.

Answer for your second question

dbCommand.Parameters.Add(new SqlParameter("@firstDate", 
    firstDate));

The above method is used to replace the @parameter(first argument in Add method) with parameter value(second argument in Add method). It uses @ as the delimiter. it checks if there is a word with prefix '@' in query statement, it will be marked as a parameter and it is replaced by the actual value which is passed as the second argument in the Add method.

Upvotes: 1

Rian

Reputation: 201

I agree with @har07. That is not a ampersand. An ampersand, as far as I know, looks like this -> &. To answer the question, the 'at' sign (@) is used to indicate variables in a parameterized query in c#. In the code

dbCommand.Parameters.Add(new SqlParameter("@firstDate",firstDate));

you are assigning the value of the DateTime variable firstDate to the @firstDate variable in your query.

Here's an example that's a bit less confusing(I hope): Let's say I have a string variable called myName and I want to pass that to my query select * from students where name = @name. To pass the value of myName to @name in my query, I would do

dbCommand.Parameters.Add(new SqlParameter("@name",myName));

I hope that helps.

Upvotes: 1

har07

Reputation: 89295

First, that isn't ampersand but at sign or commercial at. It is used in this particular context to indicates an SQL parameter name.

And this part showing how you pass the actual value (contained in the firstDate C# variable) to the SQL parameter (@firstDate) :

dbCommand.Parameters.Add(new SqlParameter("@firstDate", 
    firstDate));

You can read your parameterized SQL query statement like string concatenation but with big advantages (the former save you from SQL injection, arbitrary data type to string conversion with correct formatting^*, etc) :

query = "SELECT HolidayDate FROM Holidays " + _
    " WHERE HolidayDate >= " + firstDate + " AND HolidayDate < " + lastDate;

_*) See that in the string concatenation version above you need to convert firstDate and lastDate to string with correct format according to your RDBMS local settings to make it work.

Upvotes: 1