Solved

Connection string and Openfiledialog

Posted on 2016-07-19
16
36 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
16 Comments
 
LVL 44

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 44

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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 44

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 44

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 44

Accepted Solution

by:
AndyAinscow earned 250 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 44

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 250 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 44

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 44

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Diagnostics with Net and Net.Sockets 2 29
Media.Imaging 1 18
What can cause the styling on a .NET site to not be found? 17 26
Hey!! 5 33
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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

809 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