ADO.NET: Check if a name already exists in database using id

Question

I have a method to check if a name already exists in DB or not. The way I am doing it now is first I am getting the company name by company id then I am checking the name with the name I am receiving from the UI as a param.

Now the current code is tremendous, is there any easiest way to do it or I can improve the current code.

 public BaseResponse CheckDupliateCompany(string companyName, string companyId)
    {
        BaseResponse response = new BaseResponse();
        string existingCompanyName = null;

        using (SqlConnection con = new SqlConnection(connectionString))
        {
            if (companyId != null)
            {
                string sqlQuery = "SELECT * FROM CompanyInformation where CompanyID= " + companyId;
                SqlCommand cmd = new SqlCommand(sqlQuery, con);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    existingCompanyName = rdr["CompanyName"].ToString();
                }

                if (string.Equals(existingCompanyName, companyName))
                {
                    response.Status = (int)Status.Failed;
                } else
                {
                    response.Status = (int)Status.Success;
                }
                con.Close();
            }
            else
            {
                string sqlQuery = "SELECT * FROM CompanyInformation where CompanyName= '" + companyName + "'";
                SqlCommand cmd = new SqlCommand(sqlQuery, con);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    response.Status = (int)Status.Failed;
                }
                con.Close();
            }
        }
        return response;
    }

score 1 · Answer 1 · answered Apr 30 '20 at 08:11

I would go with something like that. Debug it since i have not entirely tested it

public BaseResponse CheckDupliateCompany(string companyName, string companyId)
        {
            BaseResponse response = new BaseResponse() { Status = (int)Status.Success};

            string sqlQuery = "SELECT Count(*) FROM CompanyInformation where ";
            SqlParameter param;

            if (companyId != null)
            {
                param = new SqlParameter("@companyId", companyId);
                sqlQuery += "CompanyId = @companyId";
            }
            else
            {
                param = new SqlParameter("@companyName", companyName);
                sqlQuery += "CompanyName = @companyName";
            }

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery, con);
                cmd.Parameters.Add(param);
                con.Open();

                int count = (int)cmd.ExecuteScalar();
                if (count > 0)
                {
                    response.Status = (int)Status.Failed;
                }
                con.Close();
            }
            return response;
        }

Thanks will try this but you forgot to match the strings – C.Das Apr 30 '20 at 09:07 — C.Das, Apr 30 '20 at 09:07

score 1 · Accepted Answer · answered Apr 30 '20 at 08:20

I guess there are a couple of ways at least to make it easier or to improve your code. But let me suggest a couple of considerations which I believe are important:

¿ Do you need to fetch all the fields from the CompanyInformation table? Normally doing a SELECT * is not quite a good practice as you may be fetching fields that you may not need. In addition, if in the future more columns are added to that table, also those fields will be fetched as well because of the * (star). Here you have a complete answer to that topic on StackOverflow.

On the other hand, based on what you said about: receiving from the UI as a param.

A SQL Injection could be made if you don't validate what is being passed from the UI and then you put it directly into your Query. If this is production code you should avoid it by all means. More about SQL Injection in here.

Finally, my suggestion to improve your code is to use an OR in your SQL statement. Here you can find how to do that

It will be something like (take it only as a guideline, not as a complete solution):

SELECT column1, column2 -- the Columns that you really need to fetch
FROM CompanyInformation
WHERE CompanyID= companyId OR CompanyName= 'companyName'  --you need to pass this properly

I hope it provides you some hints on how to improve it.

ADO.NET: Check if a name already exists in database using id

2 Answers2