0

I have a problem with the following code. Please help.

What I am trying to do is pass a sqlcommand to a function which then returns a dataset.

The function 'Get data' takes sqlcommand as a parameter. This function is in class 'DatabaseUtilities'

    //Initializing sql connection
    static SqlConnection _Connection = new SqlConnection("Data Source=(local);Initial Catalog=db_Test;Integrated Security=True");

    //Connection property
    public static SqlConnection Connection
    {
        get {return _Connection;}
    }

    //The class that takes sqlcommand as parameter
    public static DataSet GetData(SqlCommand Command)
    {
        _Connection.Open();

        SqlDataAdapter Adapter = new SqlDataAdapter();
        Adapter.SelectCommand = Command;

        DataSet Table = new DataSet();

        Adapter.Fill(Table);

        _Connection.Close();

        return Table;
    }

This is how sqlcommand is passed into the above function. This function is from a different class.

public DataSet GetLogByDate(string SearchValue)
    {
        Command.CommandType = CommandType.StoredProcedure;
        Command.Connection = DatabaseUtilities.Connection;

        Command.CommandText = "sp_GetLogByDate";
        Command.Parameters.AddWithValue("@LogDate", SearchValue);

        return GetData(Command);
    }

This code throws the fllowing error.

Invalid object name 'sp_GetLogByDate'.

I do have the above stored procedure in my database. I have no idea why this happened. Could anyone help?

JeffO
  • 7,957
  • 3
  • 44
  • 53
Abhishek Singh
  • 111
  • 2
  • 3
  • 10
  • 1
    `static SqlConnection`is not a good idea when you use multiple threads (e.g. ASP.NET) and even Connection-Pooling is enabled (default): – Tim Schmelter Mar 14 '13 at 17:20

1 Answers1

2

You have to connect Command with Connection:

//The class that takes sqlcommand as parameter
public static DataSet GetData(SqlCommand Command)
{
    Command.Connection = _Connection;
MarcinJuraszek
  • 124,003
  • 15
  • 196
  • 263
  • I did that in the class that passes the sqlcommand. public DataSet GetLogByDate(string SearchValue) { Command.CommandType = CommandType.StoredProcedure; Command.Connection = DatabaseUtilities.Connection; – Abhishek Singh Mar 14 '13 at 17:33
  • @AbhishekSingh Command.Connection = DatabaseUtilities.Connection is not the same as the instance _Connection which is the one you opened. – JeffO Mar 14 '13 at 18:14