Need a method called from Form1_Load() to establish a connection to a MSSQL database

Greetings,

I'm trying to establish a method with exception handling to connect to the AdventureWorks database.  The code below shows the pieces I have but I don't know how to finish this off.

If you look at the picture you can see I have a data source established.  I just don't know how to put it all together.  Even though this is not a web application, should I use the SqlConnectionStringBuilder in the snippet below?

The  BuildConnectionString() method below indicates I could get some elements from the app.config file.  Where the heck is that thing in my project?  I want to start simple and make it more complex later if necessary.  Thus I really think the string should look like this:

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

End of story right?  In which case I just need:

Form1_Load()
{
     Server=myServerAddress;Database=AdventureWorks;Trusted_Connection=True;

      //create the database query
      string query = "SELECT * FROM Person.Contact";

      //create an OleDbDataAdapter to execute the query
      OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

      //create a command builder
      OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

      //create a DataTable to hold the query results
      DataTable dTable = new DataTable();

      //fill the DataTable
      dAdapter.Fill(dTable);

}

As you can see in the picture, I don't have an Adapter yet but I guess I can save that for the next question I open....

Thanks!

private static void BuildConnectionString(string dataSource,
    string userName, string userPassword)
{
    // Retrieve the partial connection string named databaseConnection
    // from the application's app.config or web.config file.
    ConnectionStringSettings settings =
        ConfigurationManager.ConnectionStrings["partialConnectString"];

    if (null != settings)
    {
        // Retrieve the partial connection string.
        string connectString = settings.ConnectionString;
        Console.WriteLine("Original: {0}", connectString);

        // Create a new SqlConnectionStringBuilder based on the
        // partial connection string retrieved from the config file.
        SqlConnectionStringBuilder builder =
            new SqlConnectionStringBuilder(connectString);

        // Supply the additional values.
        builder.DataSource = dataSource;
        builder.UserID = userName;
        builder.Password = userPassword;
        Console.WriteLine("Modified: {0}", builder.ConnectionString);
    }
}

Open in new window

connection-stuff.JPG
John500Asked:
Who is Participating?
 
Vikram Singh SainiConnect With a Mentor Software Engineer cum AD DeveloperCommented:
Hi,

Are you trying to connect to database simply in Form Load event. Please check code.

You have to add App.config file too. Modify it as shown in code.

Regards,
VSS

------------------
//App.config
------------------
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<connectionStrings>
		<add name="MyConn" connectionString="Data Source=ENCORE; Initial Catalog=CarDB; User ID=sa; Password=god; Integrated Security=false"/>
	</connectionStrings>
</configuration>

-----------------
//Form1.cs
-----------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

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

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                SqlConnection con = new SqlConnection();
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                    MessageBox.Show("Connection Opened!", "Connection Result", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (SqlException exc)
            {
                MessageBox.Show("EXCEPTION TRAPED: Technical Report\n" + exc.ToString());
            }
        }
    }
}

Open in new window

0
 
John500Author Commented:
Thanks for the input on this.  I was able to get your code to work.  Oddly enough, the Form loaded fine without any connection code whatsoever.

If I comment out your code, the Form will load as seen in the picture below.  I could close this question out.   However, I am curious to know your thoughts on what I would do to disable the automatic connection so that I must depend on this code.

I really think I need to incorporate a database file external to the application so that anybody could run this if they had the AdventureWorks database.

Your thoughts?
Thanks!

PS - My next question regarding inserts is here if you are interested:

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_CSharp/Q_26436224.html
table-loaded.JPG
0
 
Vikram Singh SainiSoftware Engineer cum AD DeveloperCommented:
Hi,

Oddly enough, the Form loaded fine without any connection code  whatsoever.
What you mean to say by this line?

However, I am curious to know your thoughts on what I would do to  disable the automatic connection so that I must depend on this code.
So in that case I think you have already set the datasource of datagridview in design view by some wizard. And so you are able to view the form as it is showing you. In that case I would say you to remove that datasource which you have set in design view as shown in your first snapshot.

I really think I need to incorporate a database file external to the  application so that anybody could run this if they had the  AdventureWorks database.
And yes this would be the best idea to do that. However if you let me know the exact requirements of your code I can give it try to develop for you. But you have to be patient with me for some time.

Regards,
VSS
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
John500Author Commented:
Hello vss,

I meant oddly because it loads without your code... but now I understand why.  See the picture below to see the datasource.

If it were possible to use an external database file *AND* the data source shown below - that would be nice !  Why?   Because the datasource below makes things easier.... but I would also like to make this application easy for anyone else with the use of a database file.  

If I cannot use a database file and predefined datasource, then I will do it the longer harder way.  If you would like to help me with my *end goal*  (inserting one record to the Person.Contact table) that would be great!   However, I need to finish this by tomorrow night.
datasource.JPG
0
 
Vikram Singh SainiSoftware Engineer cum AD DeveloperCommented:
Hi,

If you would like to help me with my *end  goal*  (inserting one record to the Person.Contact table) that would be  great!   However, I need to finish this by tomorrow night.

Do you want to insert record by modifying rows in datagridview.

Regards,
VSS
0
 
John500Author Commented:
When you say 'modifying' rows, that sounds like 'Editing'.

Therefore the answer woudl be 'no'.  I don't want to edit existing.  I only want to insert a new record to the Person.Contact table of the AdventureWorks database.

Thanks
0
 
John500Author Commented:
Another thought, when I set the 'ReadOnly' property of the DataGridView to 'False', I can access all the rows but this is not good.  I just want to allow access to the grid on the last row.  I don't want the users to be able to edit any of the others.

It would be nice if the last (empty) row appeard as the first row.

At any rate, this question is going beyond the original which you have already satisfied.  I'm going to post another question and provide you a link to it.  I'll close this out just as soon as I create the new question.

Thanks
0
 
John500Author Commented:
Here's the link to that new question and thanks for your help if you can help !

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_CSharp/Q_26436705.html

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.