Solved

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

Posted on 2010-08-27
8
459 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

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…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 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