Connection Strings/Data Sources

Hey all,

I'm coming from ASP and I used to have a global connection string accessible to all of my ASP pages that I'd use for all of my data access. This was really convenient because I could easily switch to a backup or local data source for testing/debug by changing the connection string in one place.

Now, I'm using ASP.NET with Web Matrix and I love the drag and drop functionality but its dropping my connection string all over the place. How can I do this and keep my connection string in one spot so I can have the same convenience of switching data sources.

Thanks in advance!
LarryIf you are dropping connection objects, drag and drop functionality wasn't meant for that completely. You could try in your code to switch it to the connection in the web.config in one of the page events before it is used.

In the next version of Visual Studio, and most likely Visual Web Developer, you can use web.config entries in the connection objects.

Brian|||Hmmm...

So what can I do now? Should I define some kind of global string or an application variable with my database connection string and then search and replace all references to the connection string with that?

My application needs to be able to easily switch to a backup data source if the main source goes down.

Thanks in advance!
Larry|||I like the idea better of an n-tier architecture, where you have a data access layer that defines the connection information. However, if that's not for you, then I don't know what to tell you other than define the connection object in your web page. But then that throws off your datadapter or command objects dropped into the form.

I personally always created the database objects in the code because of that. Or look at changing the connection string in one of the page events. I think I got that concept to work in a windows form app, I think it may also work in a page event.

Brian

Connection string Error

Hello I need some help please.

I created a connection string in my web config. I then tried to call it in one of my c# pages but I get a "System.InvalidOperationException: Instance failure" every time I run it.

Please review my code and let me know what I am doing wrong.

Thank you in advance for all the help

Web Config

<

appSettings>

<

addkey="AVConnection"value="Server=Server; Database=DB; User ID=sa; Password=PWD" />

</appSettings>

//////calling connection and running a simple SQL statment///////

protected void Page_Load(object sender, EventArgs e)

{

string ConnApp;

ConnApp = System.Configuration.ConfigurationManager.AppSettings["AVConnection"];

Response.Write(ConnApp);//the string is being passed this far

SqlConnection objConn = new SqlConnection(ConnApp);

SqlCommand sqlCMD = new SqlCommand("Select * FROM Employee", objConn);

objConn.Open();//this is where the error occures

SqlDataReader objRdr = sqlCMD.ExecuteReader();

Repeater1.DataSource = objRdr;

Repeater1.DataBind();

objConn.Close();

objRdr.Close();

}

ERROR PAGE

Line 29: SqlCommand sqlCMD = new SqlCommand("Select * FROM Employee", objConn);
Line 30:
Line 31: objConn.Open();
Line 32: SqlDataReader objRdr = sqlCMD.ExecuteReader();
Line 33: Repeater1.DataSource = objRdr;

[InvalidOperationException: Instance failure.]
System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +683775
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
employeelist.Page_Load(Object sender, EventArgs e) in c:\Inetpub\wwwroot\FAUAV\employeelist.aspx.cs:31
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

---

Don't know how much of a difference this might make, but have you tried putting the connection string in the connectionStrings node of web.config? If not you might as well do that as that's why it exists.

Ryan

|||

I tried that but it did not change anything.

Thanks for the advice