Solved

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

Posted on 2010-08-27
8
457 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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