Solved

Connection string and Openfiledialog

Posted on 2016-07-19
16
25 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:miksuFin
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 44

Accepted Solution

by:
AndyAinscow earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Bit flags and bit flag manipulation is perhaps one of the most underrated strategies in programming, likely because most programmers developing in high-level languages rely too much on the high-level features, and forget about the low-level ones. Th…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now