Solved

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

Posted on 2010-08-27
8
460 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
C# Desktop Application 3 54
TimeZone, day light savings, Sql server, asp.net 6 37
Are triggers slow? 7 12
itextsharp with c# 3 6
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

856 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