multiple resultset

J

js

I am using the Microsoft.Practices.EnterpriseLibrary and trying to bind
all the DropDownList controls on a page by calling the following
function at Page_Load event. My attempt is make only one trip to the
SQL Server and populate all the result sets in a Datasset object then
bind each DropDownList to the repective table. How can I achieve this?
As you can see, I execute SQL statement multiple times in the sample.

private void bindDropDownListControls()
{
try
{
DataSet ds;
Database db = DatabaseFactory.CreateDatabase();
string sqlCommandMake = "Select Make, Make + ',' + cast(MakeId as
varchar(20)) as MakeValue from Make;";
string sqlCommandType = "Select Type, Type + ',' + cast(TypeId as
varchar(20)) as TypeValue from Type;";
string sqlCommandModel = "Select Model, Model + ',' + cast(ModelId
as varchar(20)) as ModelValue from Model;";

string sqlCommand = sqlCommandMake + sqlCommandType +
sqlCommandModel;

DBCommandWrapper dbCommandWrapper =
db.GetSqlStringCommandWrapper(sqlCommandMake);

//It would be nice to use GetSqlStringCommandWrapper(sqlCommand)
//then bind the DropDownList's DataSource to ds("tableName")

cboMake.DataSource = db.ExecuteDataSet(dbCommandWrapper);
cboMake.DataTextField = "MAKE";
cboMake.DataValueField = "MakeValue";
cboMake.DataBind();
cboMake.Items.Insert(0,"");

dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommandType);

//I am trying to avoid execute another command, instead
//it would be nice to access the second table in the ds since it
//contains all the results already.

cboType.DataSource = db.ExecuteDataSet(dbCommandWrapper);
cboType.DataTextField = "TYPE";
cboType.DataValueField = "TypeValue";
cboType.DataBind();
cboType.Items.Insert(0,"");

dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommandModel);
cboModel.DataSource = db.ExecuteDataSet(dbCommandWrapper);
cboModel.DataTextField = "MODEL";
cboModel.DataValueField = "ModelValue";
cboModel.DataBind();
cboModel.Items.Insert(0,"");

}
catch(InvalidCastException e)
{
lblError.Visible = true;
lblError.Text = e.Message;
}
}
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,768
Messages
2,569,575
Members
45,053
Latest member
billing-software

Latest Threads

Top