CODEWITHSUNDEEP
c#sql-server
user2375245
user2375245

Reputation: 45

C# SQL Server query

This is code segment that I have written in C#. Mobile and Name are columns in my table. The problem is that there is something wrong with format of my query. Is the syntax correct if we want to connect two queries in C # using OR?

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [Contact Management] WHERE
         Mobile='"+Convert.ToInt32(txtSearch.Text)+"' OR Name='"+txtSearch.Text+"'",con);

Upvotes: 0

Views: 514

Answers (2)

Steve
Steve

Reputation: 216243

As usual, never use string concatenation to build sql command. Use parametrized queries

string query = "SELECT * FROM [Contact Management] WHERE Mobile=@mobile OR Name=@name";
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@mobile", Convert.ToInt32(txtSearch.Text));
cmd.Parameters.AddWithValue("@name", txtSearch.Text);
SqlDataAdapter da= new SqlDataAdapter (cmd);

The parametrized query will save your database from Sql Injection Attacks, but also from problems in parsing your input text. What if in the search text you have a single quote? You will get a syntax error with concatenation.

However, let me say that your code will fail before this. If you have a number in your txtSearch, then everything will work, but if you have a string. converting to a number with Convert.ToInt32 will fail. Better to use

SqlCommand cmd = new SqlCommand();
string query;
int numSearch;
if(Int32.TryParse(txtSearch.Text, out numSearch))
{
   query = "SELECT * FROM [Contact Management] WHERE Mobile=@p1";
   cmd.Parameters.AddWithValue("@p1", numSearch);
}
else
{
   query = "SELECT * FROM [Contact Management] WHERE Name=@p1";
   cmd.Parameters.AddWithValue("@p1", txtSearch.Text);
}
cmd.CommandText = query;
....

Upvotes: 5

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

No, that syntax is not correct. It's vulnerable to sql injection attacks. You need to build it like this:

SqlCommand cmd = new SqlCommand("SELECT * FROM [Contact Management] WHERE
     Mobile= @Search OR Name= @Search")
SqlDataAdapter = new SqlDataAdapter(cmd);
cmd.Parameters.Add("@Search", SqlDbType.NVarChar, 50).Value = txtSearch.Text;

You could also write the query this way:

SELECT * FROM [Contact Management] WHERE @Search IN (Mobile, Name)

Upvotes: 9

Related Questions