• Select values from different db with same app C#

Select values from different db with same app C#

  • 553 Views
  • Last Post 05 September 2018
  • Topic Is Solved
Stylus STYLUS posted this 04 September 2018

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 combobox.
On Form 2 empty partner datagrid

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?

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;
        }
c#
Order By: Standard | Newest | Votes
Daniel Soares posted this 04 September 2018 

 using (SqlConnection con = new SqlConnection(cs))

Before this line you must switch between the different connection string, stored in the cs variable. This can be done in various ways...you can declare a bool parameter to select:

Example:

public List<string> GetDatabaseList(bool oldData = false)
{

   if (oldData)
      cs = @"alternative database connection string";
}

...etc

admin posted this 05 September 2018

I think you should create a configuration form that allows you to change your connection string at runtime as the following

c# dynamic connection string

  • Server: Contains your sql server name, you can enter your server name or select from the available server list
  • Database: Enter your database name that you want to connect
  • User name: Use the sql username to connect to your sql server
  • Password: Use the sql password to connect to your sql server

Put your connection string in the app.config file

<configuration>
  <connectionStrings>
    <add name="cn" connectionString="Data Source=YourServerName;Initial Catalog=YourDatabase;User ID=YourUserSql;Password=YourPasswordSql;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

Create an AppSetting class to read and update your connection string

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WindowsForms1
{
    public class AppSetting
    {
        Configuration config;

        public AppSetting()
        {
            config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
        }

        //Get connection string from App.Config file
        public string GetConnectionString(string key)
        {
            return config.ConnectionStrings.ConnectionStrings[key].ConnectionString;
        }

        //Save connection string to App.config file
        public void SaveConnectionString(string key,string value)
        {
            config.ConnectionStrings.ConnectionStrings[key].ConnectionString = value;
            config.ConnectionStrings.ConnectionStrings[key].ProviderName = "System.Data.SqlClient";
            config.Save(ConfigurationSaveMode.Modified);
        }
    }
}

Open your configuration form, then add code to handle connect button and save button

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsForm1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            string connectionString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};", cboServer.Text, txtDatabase.Text, txtUsername.Text, txtPassword.Text);
            try
            {
                SqlHelper helper = new SqlHelper(connectionString);
                if (helper.IsConnection)
                    MessageBox.Show("Test connection succeeded.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //Add server name to combobox
            cboServer.Items.Add(".");
            cboServer.Items.Add("(local)");
            cboServer.Items.Add(@".\SQLEXPRESS");
            cboServer.Items.Add(string.Format(@"{0}\SQLEXPRESS", Environment.MachineName));
            cboServer.SelectedIndex = 3;
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            //Set connection string
            string connectionString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};", cboServer.Text, txtDatabase.Text, txtUsername.Text, txtPassword.Text);
            try
            {
                SqlHelper helper = new SqlHelper(connectionString);
                if (helper.IsConnection)
                {
                    AppSetting setting = new AppSetting();
                    setting.SaveConnectionString("cn", connectionString);
                    MessageBox.Show("Your connection string has been successfully saved.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);                      
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}

You should change your connection string

SqlConnection con = new SqlConnection(cs)

to

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString)

or you can create a global class that's help you easily to change the connection string

public static class Global
{
    public static string ConnectionString => ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
}

then change your connection string as the following

SqlConnection con = new SqlConnection(Global.ConnectionString)
  • Liked by
  • Stylus STYLUS
Search
Popular Tags
This Weeks High Earners
Hot Topics
Close