?
Solved

Connection string and Openfiledialog

Posted on 2016-07-19
16
Medium Priority
?
62 Views
Last Modified: 2016-09-28
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);
                }
            }
        }
0
Comment
Question by:miksuFin
  • 8
  • 5
14 Comments
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 41719157
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");
0
 

Author Comment

by:miksuFin
ID: 41719342
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
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 41719362
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:miksuFin
ID: 41719378
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.
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 41719426
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

0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 41719433
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....
0
 

Author Comment

by:miksuFin
ID: 41719471
Form1
Form1.png
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
Form2.png
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();
                    }
                }
            }


        }
0
 
LVL 45

Accepted Solution

by:
AndyAinscow earned 1000 total points
ID: 41719530
You asked the following:
I want change the connection string automatically when user select file with Openfiledialog. How do I that?

Using your code the following should answer that question:
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



Just posting pieces of code that aren't connected isn't going to help me (or another expert) understand what you want to do.
0
 

Author Comment

by:miksuFin
ID: 41719678
I mean that when user open database on Form1, this same database should be in every form's connection strings.
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 41719815
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....
0
 

Author Comment

by:miksuFin
ID: 41720201
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";
    }

Server-s-connection-string.png
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");
0
 
LVL 11

Assisted Solution

by:ROMA CHAUHAN
ROMA CHAUHAN earned 1000 total points
ID: 41720316
You can store your connection string in app.config file.
You can update it as and when required inside your code and use it in every form of your application.

 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
                {
                    //Database stuff goes here
                    ChangeConnectionStringInConfig("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=" +  System.IO.Path.GetFileNameWithoutExtension(openFileDialog1.FileName) + ";Integrated Security=True;Connect Timeout=30");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
                }
            }

private void ChangeConnectionStringInConfig(string strSqlConnection)
        {
            var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            config.ConnectionStrings.ConnectionStrings["connectionInfo"].ConnectionString = strSqlConnection; //CONCATINATE YOUR FIELDS TOGETHER HERE
            config.Save(ConfigurationSaveMode.Modified, true);
            ConfigurationManager.RefreshSection("connectionStrings");
        }

Open in new window


Access connection string from config every where as ConfigurationManager.ConnectionStrings["connectionInfo"]
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 41720878
>>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.
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 41770427
https://www.experts-exchange.com/questions/28958259/Connection-string-and-Openfiledialog.html#a41719530

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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question