Select values from diferent db with same app C#

Question

1.00/5 (1 vote)

See more:

I have some simple app

Two database:
1) 2017 (table name partners(id, name))
2) 2018 (table name partners(id, name))

On Form 1 have username, password, button login and combo box.
On Form 2 empty partnerdatagrid

I need when in combobox chose value 2017 show data from database 2017 (table name partners(id, name)),
if choose value 2018 to show data from

2018 (table name partners(id, name))

Some help?

What I have tried:

Combobox value are find on next way

InitializeComponent();
     comboBox1.DataSource = GetDatabaseList();

public List<string> GetDatabaseList()
        {
            List<string> list = new List<string>();

            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();

                // Set up a command with the given query and associate
                // this with the current connection.
                using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases where name like '20%'", con))
                {
                    using (IDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            list.Add(dr[0].ToString());
                        }
                    }
                }
            }
            return list;

        }

Posted 4-Sep-18 7:21am

Goran Bibic

Updated 4-Sep-18 23:16pm

Add a Solution

Comments

MadMyche 4-Sep-18 13:30pm

Are these databases on the same server? Are there multiple connection strings involved? Can the same credentials access both DBs? What flavor of DB server are you using?

Goran Bibic 4-Sep-18 13:36pm

Same server, Yes, Yes(but if there is a better solution suggest),Microsoft SQL 2016.

ZurdoDev 4-Sep-18 13:44pm

Just change what database you connect to in the ConnectionString. Where are you stuck?

Goran Bibic 4-Sep-18 13:48pm

I want to log in to the application, on Form1 to select the combobox(2017 or 2018)and to use that conn string

Some help?

Goran Bibic 4-Sep-18 13:48pm

When choose 2017 to use that conn string, when chose 2018 to use that conn string

ZurdoDev 4-Sep-18 13:57pm

A simple if statement. Don't just repeat what you've already said, explain where you are stuck. Do you not know how to do an if statement?

Goran Bibic 4-Sep-18 14:02pm

Yes. What to put in if statement?

ZurdoDev 4-Sep-18 14:05pm

You need to have 2 connection strings. Then just write code to decide which to use. It's rather simple.

Goran Bibic 4-Sep-18 14:08pm

I need when login in app to to use that selected conn string.

ZurdoDev 4-Sep-18 14:15pm

I don't mean to be rude but I'm not sure how to really help you because it does not seem like you know how to do even the simplest of programming tasks. Hopefully someone else can walk you through it.

Good luck.

Goran Bibic 4-Sep-18 14:21pm

My friend. I now elementary statement. Exmpl(if selected_index==1 selected is 2017, else 2018 etc...but what when login in app? When string app use?

ZurdoDev 4-Sep-18 14:29pm

It is your decision. I can't tell you which one to use.

Goran Bibic 4-Sep-18 14:30pm

I need some recommendation

[no name] 4-Sep-18 14:41pm

Make it easy on yourself; use 2 "radio buttons" (RBs); one for "2017" and one for "2018".

(Users will think it overkill having a CB with only "2" items).

Of course you need to "group" the RBs; else they will act independently.

if (rb1.IsSelected or whatever){etc}

Goran Bibic 4-Sep-18 15:03pm

Maybe. Good solution

3 solutions

Solution 4

General note:
Stop "monkey-programming" and start thinking!

First of all...
An idea to store partners (keep partners details) into two different databases is bad idea!, because of...
a) you loose control over your database for set of reason (new partner id, etc.),
or
b) (if not `a` then...) you need to duplicate partners details every year in new database.

Second of all...
If you need to copy data between two different databases placed on the same server, you don't need to change connection string, because you already are connected to this server. You need to change SELECT statement:

SQL

SELECT <<ListOfFields>>
FROM Database2.Partners;

Note: you have to have permission to do that!
If you need to copy data between two different databases placed on two different servers, you can use Linked Servers (Database Engine)[^] to be able to access data from outside sql server.

Finally...
Please, read about DAL[^] and BLL[^].

Posted 4-Sep-18 23:16pm

Maciej Los

Solution 2

Not positive on your question here. First, I'll make a few assumptions...1) you are using WinForm 2) for the sake of example, the combo box that presents the choices 2017 and 2018 is named comboBox2.

In that case, after your login is complete, I'd do the following. Select a connection string based on the value of comboBox2.SelectedIndex. For example:

C#

string cs = comoBox.SelectedIndex == 1 ? connectionString2018 : connectionString2017;

Then, I would set the data source for your other combo box as follows (passing cs as a parameter):

C#

comboBox1.DataSource = GetDatabaseList(cs);

Alternatively, if the 2017/2018 combo box appears elsewhere, you could also subscribe to the SelectedIndexChanged event and initialize the DataSource (as described previously) in that event.

UPDATE:
I notice now that I have misread your question and that your GetDatabaseList function is actually getting the list of database names, to populate your database selection list.

If you really only have the possibility of 2017 and 2018, hard code them in the combo box and select a connection string as described above.

If, instead, you need to support a random number of databases, you can populate the database list as you currently do and subscribe to the SelectedIndexChanged event to (somehow) get a connection string for that database.

I would consider this a poor design. If you have a bit more flexibility, consider moving to a single database that is not year-specific. Then, add a new table PartnersByYear that has a year column and a partnerId column.

Then, with a fairly basic INNER JOIN, between partnersByYear and partners you can select the partners for the year that they participate, using a combo box to get the year in the WHERE clause. This would be a lot less cumbersome than maintaining a bunch of separate databases.

Posted 4-Sep-18 8:06am

Eric Lynch

Updated 4-Sep-18 8:21am

v3

Comments

Goran Bibic 4-Sep-18 14:22pm

string cs = comoBox.SelectedIndex == 1 ? connectionString2018 : connectionString2017;

Where to put this?

namespace test_2017_2018
{
public partial class Form1 : Form
{

//string cs = "Data Source=.\\SQLEXPRESS;Initial Catalog=2017;Integrated Security=True";
string cs = comboBox1.SelectedIndex == 1 ? test_2017_2018.Properties.Settings.TEST_2018 : test_2017_2018.Properties.Settings.TEST_2017;
public Form1()
{
InitializeComponent();
comboBox1.DataSource = GetDatabaseList(cs);
}

Eric Lynch 4-Sep-18 15:47pm

It looks like you may have replied about the same time I updated. I'd take a look at the update and consider, if possible, consolidating databases. This is the better solution. That way you don't have to maintain multiple databases, connection strings, etc. An approach is suggested in the update.

If you decide to limit to two databases as above, in response to "where to put this?" The correct question is not "where" but "when". The answer is in the original response: "after your login is complete". You must have some code/event that validates your login. You would put it immediately after validation is completed.

Goran Bibic 4-Sep-18 15:54pm

Solved...thanks for help

Eric Lynch 4-Sep-18 15:55pm

no problem

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gerry Schmitz · Accepted Answer · 2018-09-04T08:01:00

Solution 1

Just make "2" connections; each with their own connection string.

It takes time to "connect"; open both at startup; pass / take the one you need whenever.

Add a "close / dispose" method at a key point / interval when the connections are no longer needed.

SqlConnection _con1 ...
SqlConnection _con2 ...

List<sqlconnection> _connections = new List<...>() { _con1, _con2 };

etc.

Posted 4-Sep-18 8:01am

Gerry Schmitz

Comments

Goran Bibic 4-Sep-18 14:04pm

That will be for all app when login?

Goran Bibic 4-Sep-18 14:32pm

I choose one conection when login. Hot to apply for all app that conn string when login whit him?

[no name] 4-Sep-18 14:45pm

It is obvious from your rapid responses that you are NOT taking the time to properly consider what you are being told.

Don't expect "more".