Solved

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

Posted on 2010-08-27
8
458 Views
Last Modified: 2013-12-17
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
0
Comment
Question by:John500
  • 5
  • 3
8 Comments
 
LVL 16

Accepted Solution

by:
Vikram Singh Saini earned 500 total points
ID: 33548247
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
 

Author Comment

by:John500
ID: 33549517
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
 
LVL 16

Expert Comment

by:Vikram Singh Saini
ID: 33549542
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
 

Author Comment

by:John500
ID: 33549745
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 16

Expert Comment

by:Vikram Singh Saini
ID: 33549761
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
 

Author Comment

by:John500
ID: 33549856
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
 

Author Comment

by:John500
ID: 33550962
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
 

Author Comment

by:John500
ID: 33551101
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
A simple description of email encryption using a secure portal service. This is one of the choices offered by The Email Laundry for email encryption. The other choices are pdf encryption which creates an encrypted pdf of your email and any attachmen…

911 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

19 Experts available now in Live!

Get 1:1 Help Now