Solved

Connection string and Openfiledialog

Posted on 2016-07-19
16
45 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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