| re: DAL - Accessing Output Parameters
"Garth Wells" <nobody@nowhere.com> wrote in message
news:e2vPeLlOGHA.2064@TK2MSFTNGP09.phx.gbl...[color=blue]
> 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_GetByID";
> DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
> // Add paramters
> db.AddInParameter(dbCommand, "WI_ID", DbType.Int32, wi_id);
> db.AddInParameter(dbCommand, "WI_Type", DbType.String, "project");
> // Output parameters
> db.AddOutParameter(dbCommand, "WI_Title", DbType.String, 100);
> db.AddOutParameter(dbCommand, "ProjectLeader", DbType.String, 60);
> db.AddOutParameter(dbCommand, "WI_Description", DbType.String, 200);
>
> string wiString;
>
> db.ExecuteNonQuery(dbCommand);
> // Row of data is captured via output parameters
> wiString = string.Format(CultureInfo.CurrentCulture, "{0}, {1}, {2}, {3}",
> db.GetParameterValue(dbCommand, "WI_Title"),
> db.GetParameterValue(dbCommand, "ProjectLeader"),
> db.GetParameterValue(dbCommand, "WI_Description"));
>
> return wistring
> }
>
> --------------------------------------------------
> // Call
> string WIGetResults = WI.WorkItemGet();
> string[] WIData = WIGetResults.Split(',');
> WI_Title.Text = WIData[0].ToString();
> ProjectLeader.Text = WIData[1].ToString();
> WI_Description.Text = WIData[2].ToString();
>
>[/color]
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_GetByID";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
// Add paramters
db.AddInParameter(dbCommand, "WI_ID", DbType.Int32, wi_id);
db.AddInParameter(dbCommand, "WI_Type", DbType.String, "project");
// Output parameters
db.AddOutParameter(dbCommand, "WI_Title", DbType.String, 100);
db.AddOutParameter(dbCommand, "ProjectLeader", DbType.String, 60);
db.AddOutParameter(dbCommand, "WI_Description", DbType.String, 200);
string wiString;
db.ExecuteNonQuery(dbCommand);
// Row of data is captured via output parameters
return new string[] {
db.GetParameterValue(dbCommand, "WI_Title"),
db.GetParameterValue(dbCommand, "ProjectLeader"),
db.GetParameterValue(dbCommand, "WI_Description") };
return wistring
}
string[] WIData = WI.WorkItemGet();
WI_Title.Text = WIData[0].ToString();
ProjectLeader.Text = 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.ProjectLeader = ProjectLeader;
this.WI_Description = WI_Description;
)
}
// DAL Method
public string[] WorkItemGet()
{
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "pr_WorkItems_GetByID";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
// Add paramters
db.AddInParameter(dbCommand, "WI_ID", DbType.Int32, wi_id);
db.AddInParameter(dbCommand, "WI_Type", DbType.String, "project");
// Output parameters
db.AddOutParameter(dbCommand, "WI_Title", DbType.String, 100);
db.AddOutParameter(dbCommand, "ProjectLeader", DbType.String, 60);
db.AddOutParameter(dbCommand, "WI_Description", DbType.String, 200);
string wiString;
db.ExecuteNonQuery(dbCommand);
// Row of data is captured via output parameters
return new Workitem(
db.GetParameterValue(dbCommand, "WI_Title"),
db.GetParameterValue(dbCommand, "ProjectLeader"),
db.GetParameterValue(dbCommand, "WI_Description") );
}
Workitem WIData = WI.WorkItemGet();
WI_Title.Text = WIData.WI_Title;
ProjectLeader.Text = WIData.ProjectLeader;
WI_Description.Text = WIData.WI_Description;
David
|
