DAL - Accessing Output Parameters

[Garth Wells]

Using an example in the Jan 2006 release of the Enterprise Library,
I came up with the code shown below to create a DAL method
for returning several columns of a single row. I place the output
parameter values in a comma-separated string, and then split
the string to get the individual values on the calling page. This
approach works, but I can't help but think there is a more
efficient way to accomplish this.

Thanks for any insight you can provide.

--------------------------------------------------
// DAL Method
public string WorkItemGet()
{
Database db = DatabaseFactory .CreateDatabase ();
string sqlCommand = "pr_WorkItems_G etByID";
DbCommand dbCommand = db.GetStoredPro cCommand(sqlCom mand);
// Add paramters
db.AddInParamet er(dbCommand, "WI_ID", DbType.Int32, wi_id);
db.AddInParamet er(dbCommand, "WI_Type", DbType.String, "project");
// Output parameters
db.AddOutParame ter(dbCommand, "WI_Title", DbType.String, 100);
db.AddOutParame ter(dbCommand, "ProjectLeader" , DbType.String, 60);
db.AddOutParame ter(dbCommand, "WI_Description ", DbType.String, 200);

string wiString;

db.ExecuteNonQu ery(dbCommand);
// Row of data is captured via output parameters
wiString = string.Format(C ultureInfo.Curr entCulture, "{0}, {1}, {2}, {3}",
db.GetParameter Value(dbCommand , "WI_Title") ,
db.GetParameter Value(dbCommand , "ProjectLeader" ),
db.GetParameter Value(dbCommand , "WI_Description "));

return wistring
}

--------------------------------------------------
// Call
string WIGetResults = WI.WorkItemGet( );
string[] WIData = WIGetResults.Sp lit(',');
WI_Title.Text = WIData[0].ToString();
ProjectLeader.T ext = WIData[1].ToString();
WI_Description. Text = WIData[2].ToString();

[David Browne]

"Garth Wells" <no****@nowhere .com> wrote in message
news:e2******** ******@TK2MSFTN GP09.phx.gbl...

Using an example in the Jan 2006 release of the Enterprise Library,
I came up with the code shown below to create a DAL method
for returning several columns of a single row. I place the output
parameter values in a comma-separated string, and then split
the string to get the individual values on the calling page. This
approach works, but I can't help but think there is a more
efficient way to accomplish this.

Thanks for any insight you can provide.

--------------------------------------------------
// DAL Method
public string WorkItemGet()
{
Database db = DatabaseFactory .CreateDatabase ();
string sqlCommand = "pr_WorkItems_G etByID";
DbCommand dbCommand = db.GetStoredPro cCommand(sqlCom mand);
// Add paramters
db.AddInParamet er(dbCommand, "WI_ID", DbType.Int32, wi_id);
db.AddInParamet er(dbCommand, "WI_Type", DbType.String, "project");
// Output parameters
db.AddOutParame ter(dbCommand, "WI_Title", DbType.String, 100);
db.AddOutParame ter(dbCommand, "ProjectLeader" , DbType.String, 60);
db.AddOutParame ter(dbCommand, "WI_Description ", DbType.String, 200);

string wiString;

db.ExecuteNonQu ery(dbCommand);
// Row of data is captured via output parameters
wiString = string.Format(C ultureInfo.Curr entCulture, "{0}, {1}, {2}, {3}",
db.GetParameter Value(dbCommand , "WI_Title") ,
db.GetParameter Value(dbCommand , "ProjectLeader" ),
db.GetParameter Value(dbCommand , "WI_Description "));

return wistring
}

--------------------------------------------------
// Call
string WIGetResults = WI.WorkItemGet( );
string[] WIData = WIGetResults.Sp lit(',');
WI_Title.Text = WIData[0].ToString();
ProjectLeader.T ext = WIData[1].ToString();
WI_Description. Text = WIData[2].ToString();

Stuffing the values into a string is completely unnecessary.

THis is better:

// DAL Method
public string[] WorkItemGet()
{
Database db = DatabaseFactory .CreateDatabase ();
string sqlCommand = "pr_WorkItems_G etByID";
DbCommand dbCommand = db.GetStoredPro cCommand(sqlCom mand);
// Add paramters
db.AddInParamet er(dbCommand, "WI_ID", DbType.Int32, wi_id);
db.AddInParamet er(dbCommand, "WI_Type", DbType.String, "project");
// Output parameters
db.AddOutParame ter(dbCommand, "WI_Title", DbType.String, 100);
db.AddOutParame ter(dbCommand, "ProjectLeader" , DbType.String, 60);
db.AddOutParame ter(dbCommand, "WI_Description ", DbType.String, 200);

string wiString;

db.ExecuteNonQu ery(dbCommand);
// Row of data is captured via output parameters
return new string[] {
db.GetParameter Value(dbCommand , "WI_Title") ,
db.GetParameter Value(dbCommand , "ProjectLeader" ),
db.GetParameter Value(dbCommand , "WI_Description ") };

return wistring
}
string[] WIData = WI.WorkItemGet( );
WI_Title.Text = WIData[0].ToString();
ProjectLeader.T ext = WIData[1].ToString();
WI_Description. Text = WIData[2].ToString();


But this works well only because all of the parameters happen to be strings.
And you still have to remember the order in the calling code.

So this is better still:

public class WorkItem
{
string WI_Title;
string ProjectLeader;
string WI_Description;

public WorkItem(string WI_Title,
string ProjectLeader,
string WI_Description)
{
this.WI_Title = WI_Title;
this.ProjectLea der = ProjectLeader;
this.WI_Descrip tion = WI_Description;
)
}

// DAL Method
public string[] WorkItemGet()
{
Database db = DatabaseFactory .CreateDatabase ();
string sqlCommand = "pr_WorkItems_G etByID";
DbCommand dbCommand = db.GetStoredPro cCommand(sqlCom mand);
// Add paramters
db.AddInParamet er(dbCommand, "WI_ID", DbType.Int32, wi_id);
db.AddInParamet er(dbCommand, "WI_Type", DbType.String, "project");
// Output parameters
db.AddOutParame ter(dbCommand, "WI_Title", DbType.String, 100);
db.AddOutParame ter(dbCommand, "ProjectLeader" , DbType.String, 60);
db.AddOutParame ter(dbCommand, "WI_Description ", DbType.String, 200);

string wiString;

db.ExecuteNonQu ery(dbCommand);
// Row of data is captured via output parameters
return new Workitem(
db.GetParameter Value(dbCommand , "WI_Title") ,
db.GetParameter Value(dbCommand , "ProjectLeader" ),
db.GetParameter Value(dbCommand , "WI_Description ") );
}
Workitem WIData = WI.WorkItemGet( );
WI_Title.Text = WIData.WI_Title ;
ProjectLeader.T ext = WIData.ProjectL eader;
WI_Description. Text = WIData.WI_Descr iption;


David