IDbConnection and get data in a dataset

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

Answer this question

IDbConnection and get data in a dataset

seu
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
jiao
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.....
TonyMan - MSFT

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
Jan Ku&#269;era

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
Matt Morehead
Then just change the line in my last post with this one:
IDataAdapter _da = new SqlDataAdapter("Select id, description from lot";, ".....connection string.......");
EADM
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
Autofreak
Return type parameters can be readed after you read and close data reader.
Can-Ann
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....
Folyjon
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
Linusguy
question is - why do you need to use the interface for the dbCommand and dbDataAdapter
Flashouille
My Connection Type is SQL.....
JeryH

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.
phoenix11
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.
IDbConnection and get data in a dataset

Answer this question