Microsoft EnterpriseLibrary connection

I hope this is the right place to ask this:

I am using the microsoft enterpriselibrary for accessing a sql server database. when I use the following code, I receive an error 'ExecuteScalar: Connection property has not been initialized.':

-----------------------

Database db = new SqlDatabase(p_ConnectionString);

DbCommand cmd = db.GetSqlStringCommand(p_Sql);

output = cmd.ExecuteScalar();

-----------------------

but when I use this code, everything works correctly:

-----------------------

Database db = new SqlDatabase(p_ConnectionString);

DbCommand cmd = db.GetSqlStringCommand(p_Sql);

output = db.ExecuteScalar(cmd);

-----------------------

I was assured by my supervisor that both snippets' syntax are correct. Can anyone shine some light on why the first snippet will throw an exception?

Thanks in advance,

Drew

Drew,

This is because entlib's Database object opens a new connection for you internally if there isn't already one open on the command object. The DbCommand implementation (either SqlCommand, OleDbCommand or OdbcCommand) does not do it for you, hence the error you get on an uninitialized connection.

-- Victor

|||

I guess my question is more of; how do i correct the first snippet?

|||

Not sure which version of EnterpriceLibrary you are using. If you use 2.0 and later, you don't need to create yourself connection. Instead you can use default if you only have one DB connection.

What you need to do is: in your web.config file, add these lines:

<configSections>

<sectionname="dataConfiguration"type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null"/>

</configSections>

<dataConfigurationdefaultDatabase="TestDB"/>

<connectionStrings>

<addname="TestDB"connectionString="YourConnectionStringForDBHere;"providerName="System.Data.SqlClient"/>

</connectionStrings>

Please note: <dataConfigurationdefaultDatabase="TestDB"/>

Then, in your code, just do:

Database db =DatabaseFactory.CreateDatabase();

string sqlCommand ="RetrieveCustomer";

DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

db.ExecuteScalar(sqlCommand);

In your case, you can do

string p_ConnectionString = "TestDB";

Hope it helps.

|||

dhysong:

I guess my question is more of; how do i correct the first snippet?

To fix the first snippet you'll have to add code like this:

DbCommand command = db.GetSqlStringCommand("SELECT ...");if (command.Connection.State != ConnectionState.Open){command.Connection.Open();}

-- Victor

method call works from .Net form and not from SQL CLR - EnterpriseLibrary used

Hello

I created a wrapper class for a function, and exposed it through CLR. However, if I call this function form SQL it blows up but if I call directly from a test Windows Form the call works fine.

The blow up is related to EnterpriseLibrary.Data, where my Queue class uses that library to do all data access call ops

Here's my wrapper class:

namespace inlineCLRsql{

public static class Wrapper{

public static void CallQueueEntry(int queueId, int deskNo, int missed){

inLineLib.Queue oQueue;

inLineLib.QueueEntry oQueueEntry;

oQueue = new inLineLib.Queue(queueId);

oQueueEntry = oQueue.callQueueEntry(deskNo, false);

Microsoft.SqlServer.Server.SqlContext.Pipe.Send(oQueueEntry.queueNum.ToString());

}

}

And this is my CLR SQL creation code:

CREATE PROC sp_CallQueueEntry

@.queueId int,

@.deskNo int,

@.missed int

AS

EXTERNAL NAME inLineLib.[inlineCLRsql.Wrapper].CallQueueEntry

GO

sp_CallQueueEntry 4,2,0

Here is what I get as a result

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseConfigurationView.get_DefaultName()

at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseMapper.MapName(String name, IConfigurationSource configSource)

at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.ConfigurationNameMappingStrategy.BuildUp(IBuilderContext context, Type t, Object existing, String id)

at Microsoft.Practices.ObjectBuilder.BuilderBase`1.DoBuildUp(IReadWriteLocator locator, Type typeToBuild, String idToBuild, Object existing, PolicyList[] transientPolicies)

at Microsoft.Practices.ObjectBuilder.BuilderBase`1.BuildUp(IReadWriteLocator locator, Type typeToBuild, String idToBuild, Object existing, PolicyList[] transientPolicies)

at Microsoft.Practices.ObjectBuilder.BuilderBase`1.BuildUp[TTypeToBuild](IReadWriteLocator locator, String idToBuild, Object existing, PolicyList[] transientPolicies)

at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.EnterpriseLibraryFactory.BuildUp[T](IReadWriteLocator locator, IConfigurationSource configurationSource)

at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.EnterpriseLibraryFactory.BuildUp[T](IConfigurationSource configurationSource)

at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.NameTypeFactoryBase`1.CreateDefault()

at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase()

at inLineLib.Queue.getNextQueueEntry(Int32 servedBy)

at inLineLib.Queue.callQueueEntry(Int32 servedBy, Boolean callMissed)

at inlineCLRsql.Wrapper.CallQueueEntry(Int32 queueId, Int32 deskNo, Int32 missed)

What can I do to fix this?

Cheers

M

This is almost a total guess - I've never used EntLib. However, I found some one else hit this issue when EntLib was not able to find the database in the application config file: http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_21833370.html

This is likely to be the same problem you're facing. I believe you can solve this by running the Enterprise Library Config tool and specify your config file as sqlservr.exe.config in the same directory as sqlservr.exe. Or you can try copying and renaming the config file your Windows Form app is using.

Hope this works.

Steven