CODEWITHSUNDEEP
c#sql
Lawraoke
Lawraoke

Reputation: 556

C# SQL pass variable to query

I am currently writing a Search function that bring down a value Name

Here is my query:

"SELECT Company.Name, Company.Reg FROM Company WHERE Name LIKE '%''" + Name + "''%'";

Here is the function:

public object CompanySearch(string Name)
        {
            using (PCE)
            {
                SqlConnection con = new SqlConnection(constr);
                try
                {
                    List<CompanySearch> cm = new List<CompanySearch>();
                    SqlCommand command = new SqlCommand();
                    command.Connection = con;
                    "SELECT Company.Name, Company.Reg FROM Company WHERE Name LIKE '%''" + Name + "''%'";
                    con.Open();
                    //process the sql execute etc
                }
            }
        }

Is the way I reading Name correctly?

I tested without ' ' , however I get an exception message as follow:

"ExceptionMessage": "Incorrect syntax near 'Mysearch'.",

UPDATE

SELECT Company.Name, Company.Reg
FROM Company
WHERE CompanyName LIKE '%MySearch%';

This is the code that I execute in SSMS, and it went sucess. However it doesnt work on my C#

Upvotes: 0

Views: 925

Answers (2)

Pitming Sir
Pitming Sir

Reputation: 77

First of all, you should always avoid to "manually build" your own query. This is the best way to have SQL Injection (https://en.wikipedia.org/wiki/SQL_injection)

Secondary, you should used Parameter in your query

SqlCommand cmd = new SqlCommand("SELECT Company.Name, Company.Reg WHERE Name LIKE @companyName", connection);
cmd.Parameters.Add("@companyName", SqlDbType.NVarChar).Value = Name;

But in 2020, you should use an ORM instead of building your own query. This is far better to save time and avoid bugs. Have a look at EF Core (https://learn.microsoft.com/fr-fr/ef/) or Dapper (https://stackexchange.github.io/Dapper/), ...

Upvotes: 3

StackNeverFlow
StackNeverFlow

Reputation: 105

This is what I try, it worked for me...

 public object CompanySearch(string Name)
        {
            SqlConnection con = new SqlConnection(constr);
            try
            {
                List<CompanySearch> cs = new List<CompanySearch>();
                SqlCommand command = new SqlCommand();
                command.Connection = con;
                command.CommandText = "SELECT Name, Reg, FROM Company WHERE Name LIKE '%" + Name + "%'";
                con.Open();
                //process the sql execute etc
            }
        }
    }

However, a good practice is to parameterize your query that mentioned by @HoneyBadger

Upvotes: 1

Related Questions