locked
IDbConnection and get data in a dataset RRS feed

  • Question

  • Hello Everyone,

    I'm using IDbConnection to connect to database and that works perfectly fine....

    I can do something like this as well...and this is good too....

    IDbCommand command = conn.CreateCommand();

    command.CommandText = "Select id, description from lot";

    IDataReader dr = command.ExecuteReader();

    while (dr.Read())

    {

    string some = dr[1].ToString();

    }

    The problem I'm facing is how to return it in dataset, as I directly want to bind my controls....or somebody can suggest a better way to do it...

    Thanks,

    Harsimrat

    Sunday, October 1, 2006 5:02 PM

Answers

  • Then just change the line in my last post with this one:
    IDataAdapter _da = new SqlDataAdapter("Select id, description from lot";, ".....connection string.......");
    Monday, October 2, 2006 1:32 PM
  • Here is a usefull link for you:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/vsgenerics.asp
    Sorry if this link will give you some headache or/and force you in some shift delete operation :) .
    About the problem here is some example code that i wrote:

    SqlConnection _conn = new SqlConnection("... connection string .....");
    IDbCommand _cmd = new SqlCommand("dbo.YourProcedureName"
    , _conn);
    _cmd.CommandType =
    CommandType
    .StoredProcedure;
    IDbDataParameter param = new SqlParameter("@InputParameter", SqlDbType
    .VarChar, 50);
    param.Value = "asdaf";
    _cmd.Parameters.Add(param);
    IDbDataParameter outparam = new SqlParameter("@OutParam", SqlDbType
    .VarChar, 50);
    outparam.Direction =
    ParameterDirection
    .Output;
    _cmd.Parameters.Add(outparam);
    try
    {
       _conn.Open();
       IDataReader
    DR = _cmd.ExecuteReader();
       if
    (DR.Read())
       {
          //here you read all other values from data reader
      
    }
       DR.Close();
       string
    output = outparam.Value.ToString();
    }
    ....
    ....
    There is no big problem with output parameters, and only one thing that should know. That is datareader must be closed.

    Monday, October 2, 2006 5:14 PM

All replies

  • well you would use a dataReader (Sql/OleDb) to "fill" the dataset with records. Example:

    SqlCommand theSQLCommand = new SqlCommand("SELECT [ID], [description] FROM lot")

    theSQLCommand.Connection = new SqlConnection(connectionString);

    DataSet theDataSet = new DataSet(); //you may wish to declare this globally so other parts of the class can access it

    SqlDataAdapter theDataAdapter = new SqlDataAdapter(theSQLCommand); //same applies here, declare it globally

     

    theSQLCommand.Connection.Open();

    theDataAdapter.Fill(theDataSet);

    theSQLCommand.Connection.Close();

     

    //then bind to UI control, such as a combobox:

     

    this.theComboBox.DataSource = theDataSet.Tables[0].DefaultView;

    this.theComboBox.DisplayMember = "fieldName";

     

    this will fill records into a dataset from the SELECT command using a dataAdapter. In this example I have used a SQL data adapter but you can do the same with OleDb (same procedure, just different class names!)

    Does this help?

    Sunday, October 1, 2006 6:50 PM
  • The method you provided works if I'm using SQLCommand and SQLadapter.....but if I need to use IDbCommand and IDbDataAdapter....that doesn't work....
    Sunday, October 1, 2006 8:53 PM
  • question is - why do you need to use the interface for the dbCommand and dbDataAdapter?
    Sunday, October 1, 2006 9:19 PM
  • Thats a very good question, I'm using a third party component and thats how they want to use it. Its one of the requirement...Can't help.....
    Sunday, October 1, 2006 11:20 PM
  • Can you try something like this:
    DataSet _ds = new DataSet();
    _ds.Locale =
    CultureInfo
    .CurrentCulture;
    IDataAdapter
    _da = new OleDbDataAdapter("Select id, description from lot";, ".....connection string.......")
    _da.Fill(_ds);

    OleDbDataAdapter should probably solve the problem because it's used to connect to different sources. By the way from which type is your connection, that also must have some provider type?

    Monday, October 2, 2006 9:12 AM
  • My Connection Type is SQL.....
    Monday, October 2, 2006 1:00 PM
  • Then just change the line in my last post with this one:
    IDataAdapter _da = new SqlDataAdapter("Select id, description from lot";, ".....connection string.......");
    Monday, October 2, 2006 1:32 PM
  • I will test it and let you know, I have one more question say I have to pass some arguments to Stored Procedure...

    I can do the proper SQL way like this.....

    SqlParameter paramReturnValue = new SqlParameter();
    paramReturnValue.ParameterName = "@return_value";
    paramReturnValue.SqlDbType = SqlDbType.Int;
    paramReturnValue.Direction = ParameterDirection.ReturnValue;

    but if I do IDb way....

    say

    IDbDataParameter parameter = null; ( // It says object is not instantiated)

    and then rest of the code...

    How can I overcome this problem...??

    Thanks,

    Harsimrat

    Monday, October 2, 2006 1:35 PM
  • Return type parameters can be readed after you read and close data reader.
    Monday, October 2, 2006 1:44 PM
  • But whenever I try that way it always throws me an exception....

    IDbDataParameter pa = null;

    as I'm not making a new instance, how can I make a new instance....

    Thanks,

    Harsimrat

    Monday, October 2, 2006 3:48 PM
  • Here is a usefull link for you:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/vsgenerics.asp
    Sorry if this link will give you some headache or/and force you in some shift delete operation :) .
    About the problem here is some example code that i wrote:

    SqlConnection _conn = new SqlConnection("... connection string .....");
    IDbCommand _cmd = new SqlCommand("dbo.YourProcedureName"
    , _conn);
    _cmd.CommandType =
    CommandType
    .StoredProcedure;
    IDbDataParameter param = new SqlParameter("@InputParameter", SqlDbType
    .VarChar, 50);
    param.Value = "asdaf";
    _cmd.Parameters.Add(param);
    IDbDataParameter outparam = new SqlParameter("@OutParam", SqlDbType
    .VarChar, 50);
    outparam.Direction =
    ParameterDirection
    .Output;
    _cmd.Parameters.Add(outparam);
    try
    {
       _conn.Open();
       IDataReader
    DR = _cmd.ExecuteReader();
       if
    (DR.Read())
       {
          //here you read all other values from data reader
      
    }
       DR.Close();
       string
    output = outparam.Value.ToString();
    }
    ....
    ....
    There is no big problem with output parameters, and only one thing that should know. That is datareader must be closed.

    Monday, October 2, 2006 5:14 PM
  • Awesome, Thanks a lot...It works really well.....

    There is one more thing....

    /// <summary>

    /// The ConnectionManager instance

    /// </summary>

    private ConnectionManager connmanager = ConnectionManager.Instance;

    What happens is, I compile and run and get this exception

    Object reference not set to an instance of an object.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

    Source Error:

    Line 33: 		/// The ConnectionManager instance 
    Line 34: 		/// </summary>
    Line 35: 		private ConnectionManager connmanager = ConnectionManager.Instance;
    Line 36: 
    Line 37: 		/// <summary>

    Now I run it again or refresh the page it works......I have no idea whats going on and why it is happening.....

    Really appreciate for your help...

    Thanks,

    harsimrat

    Monday, October 2, 2006 5:43 PM
  • Don't know what is the problem, by the way i don't have enough info. When you ask different question please create a new one with problem specific name. Maybe is better to move this question to ASP.NET forum.
    Monday, October 2, 2006 7:14 PM