Enterprise library 3.1 and stored procedures

[Alex. O. Koranteng]

i have created DAL code and Business layer code. I am using objectdatasource
to point to the select and Update methods. I am also using Enterprise library
3.1 and the GetStoredproc command method to access/modify data for the
gridview. I am getting errors like (1)"Type or namespace could not be found,
(2)'Microsoft.Practices.EnterpriseLibrary.Data.Database' does not contain a
definition for 'GetSprocCommand', (3) The name Northwind does not exist in
the current context. Any suggestions will be appreciated, and below is the
code sample

DAL code

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;


/// <summary>
/// Summary description for DataAccess
/// </summary>
public class DataAccess
{
public DataAccess()
{
//
// TODO: Add constructor logic here
//

}



public DataSet GetCustomers()

{
//DataSet dsGetCustomer = null;
DataSet dsGetCustomer = null;
DbCommand Command = null;

DataTable dt = new DataTable();
string DbCommand = "CustomerList1";
Database Northwind = DatabaseFactory.CreateDatabase();

DbCommand command = Northwind.GetStoredProcCommand("CustomerList1");

//Northwind.AddInParameter(DbCommand, "Customerid", DbType.Int32,
Customer);

using (IDataReader dr = Northwind.ExecuteReader(command))
{
dt.Load(dr);
}

//return Customers;

return dsGetCustomer;


}
public void UpdateCustomers(string CustomerID, string companyName, string
contactName)
{
string dbcommand = "sp_UpdateCustomerEntLib";
DbCommand command =
Northwind.GetStoredProcCommand("sp_UpdateCustomerEntLib");

DataTable dt = new DataTable();

//Northwind.AddInParameter(DbCommand, "Customerid", DbType.Int32,
Customer);

using (IDataReader dr = Northwind.ExecuteReader(command));
{
dt.Load(dr);
}

}




}

Business Layer Code

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Practices.EnterpriseLibrary.Data;

/// <summary>
/// Summary description for BizLayer
/// </summary>
public class BizLayer
{
public BizLayer()
{
//
// TODO: Add constructor logic here
//
}

public DataSet GetCustomers()
{
DataAccess dataAcess = null;

try
{
dataAcess = new DataAccess();

return dataAcess.GetCustomers();
}
finally
{
dataAcess = null;
}
}

public void UpdateCustomers(string CustomerID,string contactName)
{
DbCommand dbCommand = null;
Database Northwind = DatabaseFactory.CreateDatabase("Northwind");


dbCommand = Northwind.GetSprocCommand("sp_UpdateCustomerEntLib");
Northwind.ExecuteNonQuery(dbCommand);
}
}

 

[Allen Chen [MSFT]]

Hi Alex,

The DAAB(Data Access Application Block) provided by the Enterprise Library
is the DAL itself so we don't have to write our own DAL. What we need is to
write the BLL.

I'd like to demonstrate how to use DAAB in ASP.NET with the following
sample.

1. Create a new ASP.NET web site in Visual Studio 2005. The database used
in the sample is the Northwind database.

2. Please make sure you've correctly configured the connection string,
provider, etc. You can refer to the following article to learn how to do
this:
http://msdn.microsoft.com/en-us/library/cc309171.aspx

3. Add a new class BLL.cs in the web site and paste following code into the
class file:
public class BLL
{
public DataSet CustOrdersDetail(Int32 orderid)
{
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "CustOrdersDetail";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "OrderID", DbType.Int32, orderid);
DataSet productDataSet = db.ExecuteDataSet(dbCommand);
return productDataSet;
}

public DataSet CustOrder()
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand("Select * from
Orders");
DataSet orderDataSet =db.ExecuteDataSet(dbCommand);
return orderDataSet;
}
}

4. Open Default.aspx and paste following code in the aspx file:

<asp:GridView ID="GridView1" runat="server"
DataSourceID="ObjectDataSource1" SelectedRowStyle-BackColor="Pink"
DataKeyNames="OrderID" AutoGenerateSelectButton="true" AllowPaging="true"
PageSize="5">
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
TypeName="BLL" SelectMethod="CustOrder">
</asp:ObjectDataSource>

<asp:GridView ID="GridView2" runat="server"
DataSourceID="ObjectDataSource2">
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource2" runat="server"
TypeName="BLL" SelectMethod="CustOrdersDetail">
<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="orderid"
/></SelectParameters>
</asp:ObjectDataSource>

5. View the Default.aspx page. You can select a row of the first table to
view the details information on the second table.

The above sample uses the DAAB provided by the Enterprise Library 3.1 as
the DAL. Then write a BLL by ourselves to use the DAAB. The final effect is
a typical master-details scenario. Here two GridViews are used to render
the table.

You can learn more about the the Enterprise Library 3.1 from MSDN:
http://msdn.microsoft.com/en-us/library/cc309205.aspx

Please have a try and let me know if it works. If you need further
assistance please feel free to ask.

Regards,
Allen Chen
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Allen Chen [MSFT]]

Hi Alex,

Do you have any progress on this issue?

Regards,
Allen Chen
Microsoft Online Support

 

[Alex. O. Koranteng]

Hi Alex,

Do you have any progress on this issue?

Regards,
Allen Chen
Microsoft Online Support

 

[Allen Chen [MSFT]]

Hi Alex,

Do you have any problems to ask?

Regards,
Allen Chen
Microsoft Online Support

 

[Alex. O. Koranteng]

Allen,

Thanks for the follow up. I have implemented your suggestions per the latest
thread and I am still experiencing problems. I will email you an attached
file of the latest suggestions and the error message I got. I am starting to
know how to configure the connectionstring using the application block
console, so I am learning form your support soo far. I think my problem is in
the Bll code. Check email

Thanks

 

[Allen Chen [MSFT]]

Hi Alex,

Thanks for your code. I've reproduced this problem on my side. To solve
this problem you can:

1. Right click the Web.config file in the Solution Explorer window, select
"Edit Enterprise Library Configuration".
2. Select "Data Access Application Block".
3. In the properties window, select "NWConnectionString" as the value of
the "DefaultDataBase".

Or you can specify the database name in the BLL class:

public DataSet CustOrdersDetail(Int32 orderid)
{
Database db = DatabaseFactory.CreateDatabase("NWConnectionString");

....

Please have a try and let me know if it works. If you have further
questions please feel free to ask.

Regards,
Allen Chen
Microsoft Online Community Support

 

[Alex. O. Koranteng]

Allen,

I finally got the two gridviews to open up per your latest suggestions,
adding the "Nwconnectionstring" to the Createdatabase command. I would like
to keep improving on the solution project and need to understand the
following pieces
For the following syntax: string sqlCommand = "CustOrdersDetail";
where is the object "CustOrdersDetail coming from and where can I can find
it. My assumption is that it is referring to a SPROC and should be identified
in Sql Server explorer but I do not see it there. Once I get all these pieces
I will go back to the original questions and make the gridview samples work
for editing. Thanks for your help soo far

Alex

 

[Allen Chen [MSFT]]

Hi Alex,

You're correct that CustOrdersDetail is a stored procedure of the Northwind
database.

http://www.schematodoc.com/NWWeb/Northwind_2007328229_CustOrdersDetail.html

Could you find it in the SQL Server Management Studio?

http://technet.microsoft.com/en-us/library/ms345356(SQL.90).aspx

Regards,
Allen Chen
Microsoft Online Support

 

[Allen Chen [MSFT]]

Hi Alex,

Do you have any progress on this issue?

Regards,
Allen Chen
Microsoft Online Community Support

 

[Alex. O. Koranteng]

Allen,

Yes I did it find itand things make sense to me now. COuld you point me any
links that uses SPROCS and Entlib 3.1. for examples using output parameters

Thanks

 

[Alex. O. Koranteng]

Steve,

I just sent you email on my progress and request for some reference links.
See thread for 1/06/2009. Thanks for all your support.

 

[Alex. O. Koranteng]

Allen,

I have looked at this link which you pointed me to
earlier:http://msdn.microsoft.com/en-us/library/cc309205.aspx and it is a
good one. I am currently looking at the section on using the Dbdatareader to
retrieve multiple rows and will create a new Bll class for the CustOrder
dataset and use it on a new page. Are they are performance benefits in using
the Dbdatareader instead of the dataset approach we used earlier. I will
update you of my progress end of week. I think, I am getting to know how to
use data access block for entlib3.1. and I appreciate all your help soo far

Alex

 

[Allen Chen [MSFT]]

Hi Alex,

Thanks for your update.

Quote from Alex==================================================
Are they are performance benefits in using
the Dbdatareader instead of the dataset approach we used earlier.
==================================================

This is a good question. I recommend you read the following article to see
the difference between and the DataSet. After reading, you'll know when to
use DataSet and when to use DataReader.

http://msdn.microsoft.com/en-us/magazine/cc188717.aspx

Here're some related articles that may address your other questions. They
are written by the user of Enterprise Library. I think they are easier for
you to understand.

http://aspnet.4guysfromrolla.com/articles/030905-1.aspx
http://www.stereoplex.com/two-voices/using-output-parameters-with-the-enterp
rise-library
http://www.devx.com/dotnet/Article/30910/0/page/4

Please feel free to ask if you need further assistance.

Regards,
Allen Chen
Microsoft Online Support

 

[Alex. O. Koranteng]

Allen,

Thanks for the links. I will visit them and review them and will update you
end of week

 

[Allen Chen [MSFT]]

Hi Alex,

Have you got the expected answer? Do you have additional questions about
this issue?

Regards,
Allen Chen
Microsoft Online Community Support

 

[Alex. O. Koranteng]

Allen,

Per your email on 6/07/2009 about the status of my submitted incidents, yoiu
can close this case and thanks for all your help.