Return a string to a SQL server query about searching

Question

I am trying to find out if the file that is being uploaded already exists in the server. And one way for me to do this is to check if the same file name exists. However, somethig seems to not be correct in the code. I have run the query using SSMS and it works, but when I put it into Visual Studio, my return type is SQL.Data.Command and not the actual string itself. Can someone please point me in the right direction?

if (coda_file_upload.HasFile)
{
    coda = Path.GetFileName(filePath);  //gets the file name
    using (connection = new SqlConnection(connection_string))
    {
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;

        cmd.CommandText = "SELECT * FROM name_table WHERE file_name LIKE '%"+coda+"%' ";

        cmd.Connection = connection;
        connection.Open();
        cmd.ExecuteNonQuery();
        connection.Close();

        string sample = Convert.ToString(cmd);

        if (cmd.ToString().Equals(String.Empty))
        {
            coda_file_upload.SaveAs(Server.MapPath("~/") + coda);
            input_data = System.IO.File.ReadAllText(Server.MapPath("~/") + coda);
            parse_CODA(input_data, coda);

            testing.Text = "Success";
        }

        else
            testing.Text = "File exists, please try another file.";

    }

My 'else' gets executed every time instead of the if. To double check, I printed out the query to 'string sample' and thats when I see that the value being returned is SQL.Data.Command

It sounds like you want `SqlDataReader` to read the result of the query. Alternatively just count the rows returned from `cmd.ExecuteNonQuery();` — Equalsk, Oct 02 '17 at 12:43
@Steve Yeah, sorry, should have explicitly stated to use `ExecuteScalar`, counting was an afterthought I hastily edited on. Thanks. — Equalsk, Oct 02 '17 at 13:16

Steve · Accepted Answer · 2017-10-02T13:12:54.233

There are two big problems in your code:

Do not concatenate strings to make sql command texts.
Using ExecuteNonQuery on a select query is wrong. You need ExecuteReader or ExecuteScalar

Let's see how I fix the code

string input_data = string.Empty;
using (connection = new SqlConnection(connection_string))
{
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.Text;

    cmd.CommandText = "SELECT * FROM name_table WHERE file_name LIKE @name";
    cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = "%" + filename + "%";
    cmd.Connection = connection;
    connection.Open();

    // Try to execute the command and read back the results.
    SqlDataReader reader = cmd.ExecuteReader();

    // If there is no record or the field to check for emptyness is empty
    if(!reader.Read() || string.IsNullOrEmpty(reader.GetString("SampleField"))
         input_data = AcceptFile(coda);
    else
         testing.Text = "File exists, please try another file.";
 }

 private string AcceptFile(string coda)
 {
      coda_file_upload.SaveAs(Server.MapPath("~/") + coda);
      string readText = System.IO.File.ReadAllText(Server.MapPath("~/") + coda);
      parse_CODA(readText, coda);
      testing.Text = "Success";
      return readText;
 }

if you are just interested to know if a row matching the fieldname exists or not then you don't need to retrieve any record but you can just use an ExecuteScalar coupled with the T-SQL operator EXISTS to get a single value from a single row

cmd.CommandText = @"IF EXISTS(SELECT 1 FROM name_table 
                              WHERE file_name LIKE @name) 
                              SELECT 1 ELSE SELECT 0";
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = "%" + filename + "%";
int result = Convert.ToInt32(cmd.ExecuteScalar());
if(result == 0)
    .... no record found....
else
    .... record exists...

A part from the stated errors, your problem is caused by the fact that you cannot use a reference to an SqlCommand and apply a ToString hoping to get out something meaningful. The class cannot use the ToString to execute the command and return whatever field is present in the returned data set. The class simply return the full qualified name of itself.

Hi Steve, Thanks. cmd.ExecuteNonQuery() returns an int is it? Because visual studio pops up an error on that " SqlDataReader reader = cmd.ExecuteNonQuery()" line and also what is "SampleField" ? Coz that pops up an error too. Cannot convert from string to int. — Richeek Dey, Oct 02 '17 at 12:52
Oops sorry. It is a copy paste error. It is ExecuteReader as stated in the beginning of the answer — Steve, Oct 02 '17 at 12:53
Right. Thanks. And what is samplefield? Coz this line still shows an error : if (!reader.Read() || string.IsNullOrEmpty(reader.GetString("SampleField")) — Richeek Dey, Oct 02 '17 at 13:00
I don't know what field you want to check or retrieve from the command. If you just need to test the existance of the row then a better query could be written. Let me update the answer — Steve, Oct 02 '17 at 13:03
So basically. I have 2 columns, one with a file name and the other with a unique key. I just wish to check the column with file_name. So that when a user is uploading, if an existing file with the same name already exists, then it won't allow. — Richeek Dey, Oct 02 '17 at 13:05
Exactly, in this case you don't need to retrieve anything. You don't need to build and use an SqlDataReader, you just prepare the query in a way that returns just 1 if the record exists or 0 if not. Then ExecuteScalar is the most performant way to query the database. It returns an object but we know that this object could be only a 1 or a 0 and thus we can convert safely to an integer and test for the return value. — Steve, Oct 02 '17 at 13:09

ahmed abdelqader · Answer 2 · 2017-10-02T13:04:37.580

First: Don't use Select * while you wanna just check, use select 1 instead.

second: You said:-

my return type is SQL.Data.Command and not the actual string itself

the next line is the reason:-

string sample = Convert.ToString(cmd);

Fixed code:-

if (coda_file_upload.HasFile)
    {
        coda = Path.GetFileName(filePath);  //gets the file name
        using (connection = new SqlConnection(connection_string))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;

            cmd.CommandText = "SELECT 1 FROM name_table WHERE file_name LIKE '%"+coda+"%' ";

            cmd.Connection = connection;
            connection.Open();
            string result = Convert.ToString(cmd.ExecuteScalar());
            connection.Close();

            if (result != "1")
            {
                coda_file_upload.SaveAs(Server.MapPath("~/") + coda);
                input_data = System.IO.File.ReadAllText(Server.MapPath("~/") + coda);
                parse_CODA(input_data, coda);

                testing.Text = "Success";
            }

            else /* Result == 1 */
                testing.Text = "File exists, please try another file.";

        }

You will never get any rows or number of rows as the result of an ExecuteNonQuery. Also '1' is a char not a string. — Steve, Oct 02 '17 at 13:02
Thanks, I Meant using ExecuteScalar instead, the answer is Edited — ahmed abdelqader, Oct 02 '17 at 13:05

Return a string to a SQL server query about searching

2 Answers2