+ Reply to Thread
Results 1 to 5 of 5

Changing Connection String programmatically

  1. Changing Connection String programmatically

    How can I change the Connection String programmatically?
    (WS2005, Windows forms, C#)

    I need to change the server name, or Initial Catalog parts of the string.
    My connection strings are in a field of a master SQL database table.
    The design of the different databases are the same.
    How can I pass it to the _connection in the dataset?
    _connection and InitConnection() are private.
    I know, that I can edit the MyDbDataSet.Designer.cs, but I don't
    belive, that it is good solution.

    private void InitConnection() {
    this._connection = new System.Data.SqlClient.SqlConnection();
    this._connection.ConnectionString = "Data
    Source=SQL\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=" +
    "True";
    }

    If the Data Source Confoguration Wizard saves the connection string,
    the Settings.Designer.cs has also private properties I don't want edit.

    Thanks - Zsolt


  2. Re: Changing Connection String programmatically

    Since if the SQL Server holding your connection strings is renamed or
    changed, and you will not be able to get to the connection strings, that
    seems like a bad idea.

    Why not save the connection string(s) in the application.config file?

    If you are storing connnection string information related to additional
    servers, and you will use a confirmed static connection to retreive the
    stored strings, just put them in a table. You may wish to have different
    fields for server, database, security context, etc.. Create a stored
    procedure that will provide you the concatenated string when you pass it a
    parameter.

    --
    Arnie Rowland
    "To be successful, your heart must accompany your knowledge."



    "Zsolt" wrote in message
    news:1173DEA0-9696-4C12-8F58-ACA1926D85D8@microsoft.com...
    > How can I change the Connection String programmatically?
    > (WS2005, Windows forms, C#)
    >
    > I need to change the server name, or Initial Catalog parts of the string.
    > My connection strings are in a field of a master SQL database table.
    > The design of the different databases are the same.
    > How can I pass it to the _connection in the dataset?
    > _connection and InitConnection() are private.
    > I know, that I can edit the MyDbDataSet.Designer.cs, but I don't
    > belive, that it is good solution.
    >
    > private void InitConnection() {
    > this._connection = new System.Data.SqlClient.SqlConnection();
    > this._connection.ConnectionString = "Data
    > Source=SQL\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated
    > Security=" +
    > "True";
    > }
    >
    > If the Data Source Confoguration Wizard saves the connection string,
    > the Settings.Designer.cs has also private properties I don't want edit.
    >
    > Thanks - Zsolt
    >




  3. Re: Changing Connection String programmatically

    Thank you for your answer, Arnie!

    The reason for using different databases of the same design is that
    every year we will create new database. After creating the new years
    database, we will register it in the master database, entering the
    necessary connection data in a table. DataSet should use any years
    database, but only one at a time.

    My problem is that how can I pass it to the DataSet, because it is
    private. Of course I can edit the MyDataSet.Designer.cs file,
    change the body of the private void InitConnection(), and it works:

    private void InitConnection() {
    this._connection = new System.Data.SqlClient.SqlConnection();
    // insted of:
    // this._connection.ConnectionString = "Data Source=SQL\\SQLEXPRESS;Initial
    Catalog=Data2006;Integrated Security=True";
    // This:
    this._connection.ConnectionString = MyConnStrinProvider.GetConnString();
    }

    But whenever I make some changes on .xsd, the changes above are lost.
    Changing of an file seems not good.

    I think there must be better solution then this.

    Thank you again,
    Zsolt


    "Arnie Rowland" wrote:

    > Since if the SQL Server holding your connection strings is renamed or
    > changed, and you will not be able to get to the connection strings, that
    > seems like a bad idea.
    >
    > Why not save the connection string(s) in the application.config file?
    >
    > If you are storing connnection string information related to additional
    > servers, and you will use a confirmed static connection to retreive the
    > stored strings, just put them in a table. You may wish to have different
    > fields for server, database, security context, etc.. Create a stored
    > procedure that will provide you the concatenated string when you pass it a
    > parameter.
    >
    > --
    > Arnie Rowland
    > "To be successful, your heart must accompany your knowledge."
    >
    >
    >
    > "Zsolt" wrote in message
    > news:1173DEA0-9696-4C12-8F58-ACA1926D85D8@microsoft.com...
    > > How can I change the Connection String programmatically?
    > > (WS2005, Windows forms, C#)
    > >
    > > I need to change the server name, or Initial Catalog parts of the string.
    > > My connection strings are in a field of a master SQL database table.
    > > The design of the different databases are the same.
    > > How can I pass it to the _connection in the dataset?
    > > _connection and InitConnection() are private.
    > > I know, that I can edit the MyDbDataSet.Designer.cs, but I don't
    > > belive, that it is good solution.
    > >
    > > private void InitConnection() {
    > > this._connection = new System.Data.SqlClient.SqlConnection();
    > > this._connection.ConnectionString = "Data
    > > Source=SQL\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated
    > > Security=" +
    > > "True";
    > > }
    > >
    > > If the Data Source Confoguration Wizard saves the connection string,
    > > the Settings.Designer.cs has also private properties I don't want edit.
    > >
    > > Thanks - Zsolt
    > >

    >
    >
    >


  4. Re: Changing Connection String programmatically

    This is a good situation for putting the connection string information in the app.config file. You will not have to recompile and redeploy the application each year, only the app.config file.

    Add an Application.config file to the solution, and then add the appSettings section. You can add as many 'keys' as you wish. They are just name=value pairs that the application can retrieve.








    Then in the application use:

    for VB: Dim con As SqlConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))
    for C#: SqlConnection con = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))

    You may wish to check further in VS Help about using an app.config file.

    --
    Arnie Rowland
    Most good judgment comes from experience.
    Most experience comes from bad judgment.
    - Anonymous


    "Zsolt" wrote in message newsD87AECE-A6FF-4C8B-ABA6-B5B80B89D9A6@microsoft.com...
    > Thank you for your answer, Arnie!
    >
    > The reason for using different databases of the same design is that
    > every year we will create new database. After creating the new years
    > database, we will register it in the master database, entering the
    > necessary connection data in a table. DataSet should use any years
    > database, but only one at a time.
    >
    > My problem is that how can I pass it to the DataSet, because it is
    > private. Of course I can edit the MyDataSet.Designer.cs file,
    > change the body of the private void InitConnection(), and it works:
    >
    > private void InitConnection() {
    > this._connection = new System.Data.SqlClient.SqlConnection();
    > // insted of:
    > // this._connection.ConnectionString = "Data Source=SQL\\SQLEXPRESS;Initial
    > Catalog=Data2006;Integrated Security=True";
    > // This:
    > this._connection.ConnectionString = MyConnStrinProvider.GetConnString();
    > }
    >
    > But whenever I make some changes on .xsd, the changes above are lost.
    > Changing of an file seems not good.
    >
    > I think there must be better solution then this.
    >
    > Thank you again,
    > Zsolt
    >
    >
    > "Arnie Rowland" wrote:
    >
    >> Since if the SQL Server holding your connection strings is renamed or
    >> changed, and you will not be able to get to the connection strings, that
    >> seems like a bad idea.
    >>
    >> Why not save the connection string(s) in the application.config file?
    >>
    >> If you are storing connnection string information related to additional
    >> servers, and you will use a confirmed static connection to retreive the
    >> stored strings, just put them in a table. You may wish to have different
    >> fields for server, database, security context, etc.. Create a stored
    >> procedure that will provide you the concatenated string when you pass it a
    >> parameter.
    >>
    >> --
    >> Arnie Rowland
    >> "To be successful, your heart must accompany your knowledge."
    >>
    >>
    >>
    >> "Zsolt" wrote in message
    >> news:1173DEA0-9696-4C12-8F58-ACA1926D85D8@microsoft.com...
    >> > How can I change the Connection String programmatically?
    >> > (WS2005, Windows forms, C#)
    >> >
    >> > I need to change the server name, or Initial Catalog parts of the string.
    >> > My connection strings are in a field of a master SQL database table.
    >> > The design of the different databases are the same.
    >> > How can I pass it to the _connection in the dataset?
    >> > _connection and InitConnection() are private.
    >> > I know, that I can edit the MyDbDataSet.Designer.cs, but I don't
    >> > belive, that it is good solution.
    >> >
    >> > private void InitConnection() {
    >> > this._connection = new System.Data.SqlClient.SqlConnection();
    >> > this._connection.ConnectionString = "Data
    >> > Source=SQL\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated
    >> > Security=" +
    >> > "True";
    >> > }
    >> >
    >> > If the Data Source Confoguration Wizard saves the connection string,
    >> > the Settings.Designer.cs has also private properties I don't want edit.
    >> >
    >> > Thanks - Zsolt
    >> >

    >>
    >>
    >>


  5. Re: Changing Connection String programmatically

    Connection strings of different years (databases) can be stored in app.config
    file (among other places). User can select one, using a ComboBox. Ok.
    Using the selected connect string, SqlConnection and SqlCommand can be
    constructed, OK!

    But DataTableAdapter's System.Data.SqlClient.SqlDataAdapter _adapter member
    is declared in MyDataset.Designer.cs as private, so I can't access it. That
    is my original problem.

    Of course, I can edit the MyDataset.Designer.cs file, it compiles and works
    well,
    but it is not a clear method, and not recommended at all.
    Any changes in MyDataset.xsd file overwrites my editing (program is under
    construction).

    That's why I am looking for better and clearer solution.

    Thank you again,
    --
    Zsolt


    "Arnie Rowland" wrote:

    > This is a good situation for putting the connection string information in the app.config file. You will not have to recompile and redeploy the application each year, only the app.config file.
    >
    > Add an Application.config file to the solution, and then add the appSettings section. You can add as many 'keys' as you wish. They are just name=value pairs that the application can retrieve.
    >
    >
    >
    >
    >
    >

    >

    >
    > Then in the application use:
    >
    > for VB: Dim con As SqlConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))
    > for C#: SqlConnection con = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))
    >
    > You may wish to check further in VS Help about using an app.config file.
    >
    > --
    > Arnie Rowland
    > Most good judgment comes from experience.
    > Most experience comes from bad judgment.
    > - Anonymous
    >
    >
    > "Zsolt" wrote in message newsD87AECE-A6FF-4C8B-ABA6-B5B80B89D9A6@microsoft.com...
    > > Thank you for your answer, Arnie!
    > >
    > > The reason for using different databases of the same design is that
    > > every year we will create new database. After creating the new years
    > > database, we will register it in the master database, entering the
    > > necessary connection data in a table. DataSet should use any years
    > > database, but only one at a time.
    > >
    > > My problem is that how can I pass it to the DataSet, because it is
    > > private. Of course I can edit the MyDataSet.Designer.cs file,
    > > change the body of the private void InitConnection(), and it works:
    > >
    > > private void InitConnection() {
    > > this._connection = new System.Data.SqlClient.SqlConnection();
    > > // insted of:
    > > // this._connection.ConnectionString = "Data Source=SQL\\SQLEXPRESS;Initial
    > > Catalog=Data2006;Integrated Security=True";
    > > // This:
    > > this._connection.ConnectionString = MyConnStrinProvider.GetConnString();
    > > }
    > >
    > > But whenever I make some changes on .xsd, the changes above are lost.
    > > Changing of an file seems not good.
    > >
    > > I think there must be better solution then this.
    > >
    > > Thank you again,
    > > Zsolt
    > >
    > >
    > > "Arnie Rowland" wrote:
    > >
    > >> Since if the SQL Server holding your connection strings is renamed or
    > >> changed, and you will not be able to get to the connection strings, that
    > >> seems like a bad idea.
    > >>
    > >> Why not save the connection string(s) in the application.config file?
    > >>
    > >> If you are storing connnection string information related to additional
    > >> servers, and you will use a confirmed static connection to retreive the
    > >> stored strings, just put them in a table. You may wish to have different
    > >> fields for server, database, security context, etc.. Create a stored
    > >> procedure that will provide you the concatenated string when you pass it a
    > >> parameter.
    > >>
    > >> --
    > >> Arnie Rowland
    > >> "To be successful, your heart must accompany your knowledge."
    > >>
    > >>
    > >>
    > >> "Zsolt" wrote in message
    > >> news:1173DEA0-9696-4C12-8F58-ACA1926D85D8@microsoft.com...
    > >> > How can I change the Connection String programmatically?
    > >> > (WS2005, Windows forms, C#)
    > >> >
    > >> > I need to change the server name, or Initial Catalog parts of the string.
    > >> > My connection strings are in a field of a master SQL database table.
    > >> > The design of the different databases are the same.
    > >> > How can I pass it to the _connection in the dataset?
    > >> > _connection and InitConnection() are private.
    > >> > I know, that I can edit the MyDbDataSet.Designer.cs, but I don't
    > >> > belive, that it is good solution.
    > >> >
    > >> > private void InitConnection() {
    > >> > this._connection = new System.Data.SqlClient.SqlConnection();
    > >> > this._connection.ConnectionString = "Data
    > >> > Source=SQL\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated
    > >> > Security=" +
    > >> > "True";
    > >> > }
    > >> >
    > >> > If the Data Source Confoguration Wizard saves the connection string,
    > >> > the Settings.Designer.cs has also private properties I don't want edit.
    > >> >
    > >> > Thanks - Zsolt
    > >> >
    > >>
    > >>
    > >>


+ Reply to Thread