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 - ZsoltSince 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" <Zsolt@.nowhere.nospam> 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
>|||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 <auto-generated> 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" <Zsolt@.nowhere.nospam> wrote in message
> news:1173DEA0-9696-4C12-8F58-ACA1926D85D8@.microsoft.com...
>
>|||This is a good situation for putting the connection string information in th
e app.config file. You will not have to recompile and redeploy the applicati
on 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 p
airs that the application can retrieve.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="ConnectionString" value="DATA Source=ServerName;initial catalog=My
Database;integrated security=True" />
</appSettings>
</configuration>
Then in the application use:
for VB: Dim con As SqlConnection = New SqlConnection(System.Configuration.Co
nfigurationSettings.AppSettings("ConnectionString"))
for C#: SqlConnection con = New SqlConnection(System.Configuration.Configura
tionSettings.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" <Zsolt@.nowhere.nospam> wrote in message news:DD87AECE-A6FF-4C8B-ABA6-B5B80B89D9A6@.mi
crosoft.com...[vbcol=seagreen]
> 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;Initi
al
> 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 <auto-generated> file seems not good.
>
> I think there must be better solution then this.
>
> Thank you again,
> Zsolt
>
>
> "Arnie Rowland" wrote:
>|||Connection strings of different years (databases) can be stored in app.confi
g
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:
[vbcol=seagreen]
> 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 applica
tion each year, only the app.config file.
> Add an Application.config file to the solution, and then add the appSettin
gs section. You can add as many 'keys' as you wish. They are just name=value
pairs that the application can retrieve.
> <?xml version="1.0" encoding="utf-8" ?>
> <configuration>
> <appSettings>
> <add key="ConnectionString" value="DATA Source=ServerName;initial ca
talog=MyDatabase;integrated security=True" />
> </appSettings>
> </configuration>
> 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.Configu
rationSettings.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" <Zsolt@.nowhere.nospam> wrote in message news:DD87AECE-A6FF-4C8B-AB
A6-B5B80B89D9A6@.microsoft.com...

Change Tracking Displayed in Event Log

I have a customer that is complaining of poor performance when doing
searches against the FTS catalog during the day.
I believe what is happening is that the indexing process is taking place at
the same time as their searches.
select @.@.version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
The data is derived from an OCR process on scanned forms.
The data is added to the table throughout the day.
The catalog index is on one column of one table.
I have Change Tracking, Update Index in Background enabled.
Question 1 - When Chg Trk, UIB is enabled, should I see the crawl start and
crawl end events enumerated in the event log?
I thought this operation went on continuously.
I have included an abridged listing from the event log for one day.
You can see that on average, 200-300 items are processed each iteration
during the day.
Is this typical of Change Tracking, UIB?
The following list displays the data for one day,
crawl start time
crawl end time, gatherer count, modified count
7:21 crawl start
8:43 crawl end 2031581 6
9:04 crawl start
10:29 crawl end 2033495 307
11:01 crawl start
12:22 crawl end 2034510 385
12:50 crawl start
14:14 crawl end 2035862 341
14:23 crawl start
15:49 crawl end 2036796 244
16:27 crawl start
17:48 crawl end 2038034 244
19:00 crawl start
20:21 crawl end 2038557 1
Since the crawl process runs for one to one and one-half hours each event, I
am guessing that this is what is affecting their performance.
Question 2 - Would it be better to turn off Change Tracking, UIB during the
day and start an incremental index in the evening?
I thought CT, UIB was designed to allow indexing to take place while normal
database operations are executing.
This depends on your needs. If you need real time indexing updates you will
probably have to use Change tracking with Update Index in Background.
Otherwise, you might want to use Change Tracking and then schedule the index
update to periods when there is low activity on the server.
Note that there are dramatic improvements with SQL Server FTS in SQL Server
2005.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Binder" <rgondzur@.hotmail.com> wrote in message
news:eZPShpwLFHA.2888@.TK2MSFTNGP12.phx.gbl...
> I have a customer that is complaining of poor performance when doing
> searches against the FTS catalog during the day.
> I believe what is happening is that the indexing process is taking place
at
> the same time as their searches.
> select @.@.version
> --
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> The data is derived from an OCR process on scanned forms.
> The data is added to the table throughout the day.
> The catalog index is on one column of one table.
> I have Change Tracking, Update Index in Background enabled.
>
> Question 1 - When Chg Trk, UIB is enabled, should I see the crawl start
and
> crawl end events enumerated in the event log?
> I thought this operation went on continuously.
> I have included an abridged listing from the event log for one day.
> You can see that on average, 200-300 items are processed each iteration
> during the day.
> Is this typical of Change Tracking, UIB?
>
> The following list displays the data for one day,
> crawl start time
> crawl end time, gatherer count, modified count
>
> 7:21 crawl start
> 8:43 crawl end 2031581 6
> 9:04 crawl start
> 10:29 crawl end 2033495 307
> 11:01 crawl start
> 12:22 crawl end 2034510 385
> 12:50 crawl start
> 14:14 crawl end 2035862 341
> 14:23 crawl start
> 15:49 crawl end 2036796 244
> 16:27 crawl start
> 17:48 crawl end 2038034 244
> 19:00 crawl start
> 20:21 crawl end 2038557 1
>
> Since the crawl process runs for one to one and one-half hours each event,
I
> am guessing that this is what is affecting their performance.
> Question 2 - Would it be better to turn off Change Tracking, UIB during
the
> day and start an incremental index in the evening?
> I thought CT, UIB was designed to allow indexing to take place while
normal
> database operations are executing.
>
>