Link to home
Create AccountLog in
Avatar of miksuFin
miksuFinFlag for Finland

asked on

Connection string and Openfiledialog

I want change the connection string automatically when user select file with Openfiledialog. How do I that?

// Form1. The connection string is set same as selected with Openfieldialog
SqlConnection sqlConn = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\xxx.mdf;Integrated Security=True;Connect Timeout=30");

//Form2
private void button2_Click(object sender, EventArgs e)
        {
            Stream myStream = null;
            OpenFileDialog openFileDialog1 = new OpenFileDialog();

            openFileDialog1.InitialDirectory = "c:\\";
            openFileDialog1.Filter = "mdf files (*.mdf)|*.mdf";
            openFileDialog1.FilterIndex = 2;
            openFileDialog1.RestoreDirectory = true;

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    if ((myStream = openFileDialog1.OpenFile()) != null)
                    {
                        using (myStream)
                        {
                            // Insert code to read the stream here.
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
                }
            }
        }
Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

Is that what you mean?  (You don't use the SQL connection string anywhere in the code sample you supplied)

SqlConnection sqlConn = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=" + openFileDialog1.FileName + ";Integrated Security=True;Connect Timeout=30");
Avatar of miksuFin

ASKER

This error message appears when I run the app.
Error      CS0103      The name 'openFileDialog1' does not exist in the current context      App      C:\\Form32.cs      119      IntelliSense      Active
I copied that name from your code.

Look, you have given a code snippet (button click) and the connection string and they are totally separate.  I made a guess about what you wanted.

Have a look at your question and provide more details about what you want
Form2

 private void button2_Click(object sender, EventArgs e)
        {
            Stream myStream = null;
            OpenFileDialog openFileDialog1 = new OpenFileDialog();

            openFileDialog1.InitialDirectory = "c:\\";
            openFileDialog1.Filter = "mdf files (*.mdf)|*.mdf";
            openFileDialog1.FilterIndex = 2;
            openFileDialog1.RestoreDirectory = true;

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    if ((myStream = openFileDialog1.OpenFile()) != null)
                    {
                        using (myStream)
                        {
                            // Insert code to read the stream here.
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
                }
            }
        }

Many Forms
// includes for example this kind of connection above. The connection string is same in every form.

 private DataTable Datatable(String item)
        {

            SqlConnection sqlConn = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=" + openFileDialog1.FileName + ";Integrated Security=True;Connect Timeout=30");

            using (DataTable table = new DataTable("Datatable"))
            {
                using (sqlConn)
                {
                    string sqlQuery = "SELECT Table FROM Info WHERE Id ='" + item + "'";
                    using (SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn))
                    {
                        sqlConn.Open();

                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))


                            da.Fill(table);
                        sqlConn.Close();


                    }
                }
                return table;
            }


        }

I hope this makes clear what I mean.
Still not really clear - there isn't any link between those codes.
Is this what you want?

private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();

            openFileDialog1.InitialDirectory = "c:\\";
            openFileDialog1.Filter = "mdf files (*.mdf)|*.mdf";
            openFileDialog1.FilterIndex = 2;
            openFileDialog1.RestoreDirectory = true;

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                try
                {

            SqlConnection sqlConn = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=" + openFileDialog1.FileName + ";Integrated Security=True;Connect Timeout=30");
//Database stuff goes here

                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
                }
            }
        }

Open in new window

If you want the path to the database to be available for many forms/many locations in the one form then you need to store it somewhere where it is available to all forms.  eg. a singleton class or the Program class for your Application or....
Form1
User generated image
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;

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

        private void button1_Click(object sender, EventArgs e)
        {
            Stream myStream;
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();

            saveFileDialog1.Filter = "mdf files (*.mdf)|*.mdf";
            saveFileDialog1.FilterIndex = 2;
            saveFileDialog1.RestoreDirectory = true;

            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                if ((myStream = saveFileDialog1.OpenFile()) != null)
                {
                    // Code to write the stream goes here.
                    myStream.Close();
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Stream myStream = null;
            OpenFileDialog openFileDialog1 = new OpenFileDialog();

            openFileDialog1.InitialDirectory = "c:\\";
            openFileDialog1.Filter = "mdf files (*.mdf)|*.mdf";
            openFileDialog1.FilterIndex = 2;
            openFileDialog1.RestoreDirectory = true;

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    if ((myStream = openFileDialog1.OpenFile()) != null)
                    {
                        using (myStream)
                        {
                            // Insert code to read the stream here.
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
                }
            }
        }

      // Back to main page
        private void button3_Click(object sender, EventArgs e)
        {
            this.Hide();
            Form3 f3= new Form3();
            f3.Show();
        }
    }
}

Form2 (one of douzens of forms
User generated image
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;
using System.IO;
using System.Data.SqlClient;


 private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename =" + openFileDialog1.FileName + "; Integrated Security = True");

            using (conn)
            {
                string query = "SELECT * FROM Info";

                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    conn.Open();

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {

                        DataSet ds = new DataSet();
                        da.Fill(ds, "Info");

                        string result = ConvertToCSV(ds);


                        StreamWriter objWriter = new StreamWriter("C://File.csv", false);
                                                   
                                objWriter.WriteLine(result.ToString());
                                objWriter.Close();
                           

                        conn.Close();
                    }
                }
            }


        }
ASKER CERTIFIED SOLUTION
Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
I mean that when user open database on Form1, this same database should be in every form's connection strings.
Repeat of earlier comment:
If you want the path to the database to be available for many forms/many locations in the one form then you need to store it somewhere where it is available to all forms.  eg. a singleton class or the Program class for your Application or....
How can I put the variable to server's database connection string?

I created Class in GetDB.cs:

class GetDB
    {
        public static string db = "openFileDialog1.FileName";
    }

User generated image
This in every form:
 string db = GetDB.db;


And the variable in connection strings:
SqlConnection sqlConn = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename="+ db +";Integrated Security=True;Connect Timeout=30");
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
>>How can I put the variable to server's database connection string?

I've shown you that twice already.
sqlConn = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=" + openFileDialog1.FileName + ";Integrated Security=True;Connect Timeout=30");

That needs to be done when you use the OpenFileDialog to prompt the user for the file name/path


ps.. This is extremely basic programming.  Scope and lifetime of variables.
https://www.experts-exchange.com/questions/28958259/Connection-string-and-Openfiledialog.html?anchorAnswerId=41719530#a41719530

That does what was originally asked for.  I did also indicate what one can do to answer the followup question