Enumeration problem 1

[jasonp45]

I am creating a Library project which looks up filesystem locations in an Access table.

Everything was working as expected, but then I tried switching my Locations Library's main method to static...now I can't seem to get it to work at all, even after switching it back.

When I check the value of the 'SQL_Query' variable in debug mode it is:
"Select Path From Locations Where Location_ID = Data_Files_Database"

It should be (and was when it was working):
"Select Path From Locations Where Location_ID = 10"

This was not happening before, and I'm not sure why it's happening now nor how to change it back.


I am calling it from another class in this manner:

Locations locations = new Locations();
string data_Files_Database = locations.GetLocationPath(Locations.Location.Data_Files_Database);

When I access the enumeration then call the method with an enum as parameter, the parameter is evaluated as the enumeration member's *LABEL*, rather than its integer value.

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace LocationsLibrary
{
    public class Locations
    {
         public enum Location
        {
             Archive_OldFormat = 1,
             Archive_Original = 2,
             Uploads = 4,
             CDsToBurn = 5,
             CurrentProjects = 9,
             Data_Files_Database = 10,
             DataFiles_ToBeProcessed = 12,
             DataFilesLog = 14,
             ConversionDatabase = 16,
             SpecialFiles = 17,
             FTP = 18,
             FTP_Uploads = 19,
             TargetDB = 21,
             FTP_Files = 22,
             FTP_Incoming_Files = 23
        }

         public string GetLocationPath(Location location)
         {
             //DataProvider enumDataProvider, string dataSourceLocation, string SQL_Query
             string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = \\MyPC\C$\WorkingFolder\Archive\Locations.mdb";
             string scalarReturned = "";
             string SQL_Query = "Select Path From Locations Where Location_ID = " + location;
             try
             {
                 OleDbConnection connection = new OleDbConnection(connectionString);
                 connection.Open();
                 OleDbCommand command = new OleDbCommand(SQL_Query, connection);
                 command.CommandTimeout = 0;
                 scalarReturned = command.ExecuteScalar().ToString();
             }
             catch (Exception ex)
             {
                 //ToDo: Implement ErrorHandlerLibrary!
             }
             return scalarReturned;
         }
    }
}

Thanks!

 

[jmeckley]

1st. don't use injected sql. use parameters instead
2nd. don't include the connection string in your posts.
3rd. you need to cast the location to an integer.

public string GetLocationPath(Location location)
{
   try
   {
      string connectionString = "[my cnn string]";
      using (IDbConnection cnn = new new OleDbConnection(connectionString))
      {
         IDbCommand cmd = cnn.CreateCommand();
         cmd.CommandText = "Select [Path] From [Locations] Where [Location_ID]=@id";
         cmd.CreateParameter("@id").Value = (int)location;
         return (string)cmd.ExecuteScalar();
      }
   }
   catch (Exception ex)
   {
      //ToDo: Implement ErrorHandlerLibrary!
      return string.Empty;
   }
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jasonp45]

Thanks.

1: What is the advantage of using the parameter vs. 'injected' SQL? Is it considered more appropriate so that the parameter is more evident in the code?
2: It is a fake connection string.
3: Okay, I will try this...but I was not casting it before and the code worked. I'm wondering what I changed. Aren't enumerations supposed to evaluate to their integer value, and not their label?

 

[jmeckley]

1. from a security POV this injected sql is a hugh security hole. worst case a hacker can drop the entire database. google sql injection attack for more information.

2. I explicitly cast my values so I know exactly what is happening in the code. since enumerations can be a string or an int, hex, short, etc. because of this I prefer to cast exlpicitly.

Looking back at your cnn string. I appologize. i didn't read the whole string.

if you're method was static then your enum must be static as well to access the values.
this wouldn't work

public class Foo
{
   public enum Numbers { One, Two, Three }
   public static IEnuemrable<string> DoSomething()
   {
      yield return Numbers.One;
      yield return Numbers.Two;
      yield return Numbers.Three;
   }
}

these variations would work

public class Foo
{
   public enum Numbers { One, Two, Three }
   public static IEnuemrable<string> DoSomething()
   {
      yield return Numbers.One;
      yield return Numbers.Two;
      yield return Numbers.Three;
   }
}

public class Foo
{
   public enum Numbers { One, Two, Three }
   public static IEnuemrable<string> DoSomething()
   {
      Foo f = new Foo();
      yield return f.Numbers.One;
      yield return f.Numbers.Two;
      yield return f.Numbers.Three;
   }
}

public class Foo
{
   public static enum Numbers { One, Two, Three }
   public static IEnuemrable<string> DoSomething()
   {
      yield return Numbers.One;
      yield return Numbers.Two;
      yield return Numbers.Three;
   }
}

public class Foo
{
   public enum Numbers { One, Two, Three }
   public IEnuemrable<string> DoSomething()
   {
      yield return Numbers.One;
      yield return Numbers.Two;
      yield return Numbers.Three;
   }
}

public class Foo
{
   public static IEnuemrable<string> DoSomething()
   {
      yield return Numbers.One;
      yield return Numbers.Two;
      yield return Numbers.Three;
   }
}
public enum Numbers { One, Two, Three }

finally. it's best not to assume you will be getting the correct value back.
return cmd.ExecuteScalar.ToString();
could return "System.DbNull.Value" instead of the actual string. that's because a DbNull object is returned if the value is null from the database.

casting to the object to a string works because a string is unique in that its a value type and behaves like an object (can be null).

for instance it wouldn't work to cast the object to an int, or datetime these require explicit nullable object types. (example: a return value of int? or DateTime?).

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jasonp45]

Great, thanks for your help {and also your book recommendation from an earlier thread).

So my thought is that when I switched the method (but not the enumeration) to static, something changed 'under the hood' in auto-generated code somewhere. Any idea where that might have occurred and how I can switch it back?

Your point about explicitly casting the enumerations is well taken (and makes my code work again!).

I'm generally aware of SQL injection attacks; however this application is on my computer only and is not accessible from the Web. However if it's common practice to use command parameters I'll certainly switch to doing that.

Thanks again!

 

[jmeckley]

one other benefit to parameters is you don't need to escape characters.
this would break

sting criteria = "don't";
string = string.Format("select * from foo where bar = '{0}'", criteria);

//output: select * from foo where bar = 'don't'

this would not

sting criteria = "don't";
string = "select * from foo where bar = @criteria";

//output: 
//delcare @criteria varchar
//set @criteria = 'don''t'
//select * from foo where bar = @criteria

I don't have any ideas how/why auto generated code broke.

Jason Meckley
Programmer
Specialty Bakers, Inc.