Search Results in SQL Table

Question

I need to search a SQL table in C# for a value(filename). If it existed return TRUE and if not return FALSE and continue my code based on that.

This is what I have but it's not working:

SqlCommand cmdName = new SqlCommand("SELECT CASE WHEN EXISTS (SELECT * FROM [dbo].[Document] WHERE FileName = @NewFileName) THEN CAST (1 AS BIT) ELSE CAST (0 AS BIT) )", con);
{
 cmdName.Parameters.AddWithValue("@NewFileName", NewFileName);
 cmdName.ExecuteNonQuery();
}

Any suggestions?

The whole code is to prevent user from renaming a file to one that already existed in the table. So, if there is a better way to do it, I'd appreciate the help.

If you want proper help, you should define "not working". No results? Query throws an exception? ... — Conduit, Sep 30 '14 at 17:36
The original query: SELECT CASE WHEN EXISTS ( SELECT * FROM [dbo].[Document] WHERE FileName = 'SomeFileName') THEN CAST (1 AS BIT) ELSE CAST (0 AS BIT) works. But when I'm trying to execute it from C#, I can't get it to work. — user3853986, Sep 30 '14 at 17:39
http://stackoverflow.com/questions/4269548/executenonquery-for-select-sql-statement-returning-no-rows — eddie_cat, Sep 30 '14 at 17:39
@user3853986 , i guess , your problem is solved, or are you looking for any other way to solve this? — Arindam Nayak, Sep 30 '14 at 18:25
It is solved. I used your second approach. Thanks for the help. — user3853986, Sep 30 '14 at 18:27

Steve · Answer 1 · 2014-09-30T17:46:20.703

Using IF EXISTS is the fastest vay

string cmdText = @"IF EXISTS(SELECT 1 FROM [dbo].[Document] WHERE FileName = @NewFileName)
                   SELECT 1 ELSE SELECT 0"
SqlCommand cmdName = new SqlCommand(cmdText, con);
cmdName.Parameters.AddWithValue("@NewFileName", NewFileName);
int result = Convert.ToInt32(cmdName.ExecuteScalar());
if(result == 1)
   Console.WriteLine("File exists");

Of course you need to change the method called. ExecuteNonQuery is not appropriate for SELECT queries because the return value is the number of rows changed or modified by an action like INSERT/DELETE/UPDATE. Instead ExecuteScalar returns the first column of the first row of the query executed.

score 0 · Accepted Answer · answered Sep 30 '14 at 17:39

You can use like following, this has simplified SQL query.

SqlCommand cmdName = new SqlCommand("SELECT 1 FROM [dbo].[Document] WHERE FileName = @NewFileName", con);
cmdName.Parameters.AddWithValue("@NewFileName", NewFileName);
var result =  cmdName.ExecuteScalar();
if(result != null)
{
      // implies data exist
}

Or you can use this.

SqlCommand cmdName = new SqlCommand("SELECT 1 FROM [dbo].[Document] WHERE FileName = @NewFileName", con);
cmdName.Parameters.AddWithValue("@NewFileName", NewFileName);
SqlDataReader sdr = cmdName.ExecuteReader();
if (sdr.HasRows && sdr.Read())
{
          // implies data exist
}

// if you can check physically file is present in directory, then there is no DB call required. File check -- System.IO.File.Exists("PathToFile")

Search Results in SQL Table

2 Answers2