Return a string to a SQL server query about searching
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
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.