Dynamic connectionstring without web.config

Question

User1652530521 posted

Hi,

I am using EntLib and I need to pass a connectionstring dynamically and connect to a stored procedure.

How can I do that?

 

This is what I tried but I get an error:

public class CustomDatabaseFactory
{
    private string serverName;
    private string connectionString;
    private string databaseName;

    public CustomDatabaseFactory(string ServerName, string DatabaseName)
    {

        this.serverName = ServerName;

        this.databaseName = DatabaseName;

    }


    static readonly DbProviderFactory dbProviderFactory =
        DbProviderFactories.GetFactory("System.Data.SqlClient");
    public Database CreateDatabase(string connectionString)
    {
        Database db = null;

        //database mydb = new EnterpriseLibrary.Data.Sql.SqlDatabase("connection string here");

        db = new GenericDatabase(connectionString, dbProviderFactory);
        return db;
    }

    public Database GetDatabase(string connectionString)
    {

        DbConnectionStringBuilder builder = new DbConnectionStringBuilder();

        Database db = null;

        db = new GenericDatabase(connectionString, dbProviderFactory);

        return db;

    }

    public DataTable PMHistory(int Id, string connectionString)
    {
        string spName = "stp_pm_History";

        DataTable dt = new DataTable();
        Database db = DatabaseFactory.CreateDatabase(connectionString);

        object[] parameters = { Id };

        DbCommand command = db.GetStoredProcCommand(spName, parameters);

        return dt;

    }



}

public partial class _Default : System.Web.UI.Page
{
    protected ExportFile createFile;
    
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
         string    connectionString = "Data Source=SQLDEV01;Initial Catalog=test;Persist Security Info=True;User ID=rrrrr;Password=xxxxx";
                  CustomDatabaseFactory getData = new CustomDatabaseFactory("", "");
         getData.PMHistory(1, connectionString); //FALLS HERE         }

        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }
    }
}

System.Configuration.ConfigurationErrorsException: The requested database Data Source=SQLDEV01;Initial Catalog=test;Persist Security Info=True;User ID=rrrrr;Password=xxxxx is not defined in configuration. at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseConfigurationView.ValidateConnectionStringSettings

Thanks for the help

Answer 1

User753101303 posted

Hi,

According to https://msdn.microsoft.com/en-us/library/microsoft.practices.enterpriselibrary.data.databasefactory.createdatabase(v=pandp.31).aspx it doesn't seems to be possible (the name is not a connection string but the name of a connection string found in your config file).

I assume EntLib is open source? Check out what it does but it seems you would have to implement this on your own if you really want to be able to provide a connection string on the fly...

Answer 2

User-75283316 posted

Hi,

You can create dynamic Connection string as follow.

private void Form1_Load(object sender, EventArgs e)
{
     SqlConnectionStringBuilder connectionString = new SqlConnectionStringBuilder();
     connectionString.DataSource = @".\SQLEXPRESS";
     connectionString.InitialCatalog = "MyDatabase";
     connectionString.IntegratedSecurity = true;
     MessageBox.Show(connectionString.ConnectionString);
}

for more info. please visit this link. http://msdn.microsoft.com/en-us/library/dce36088.aspx

Thanks.