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_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();

[David Browne]

"Garth Wells" <no****@nowhere.com> wrote in message
news:e2**************@TK2MSFTNGP09.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_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();

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