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