Database connection using visual studio

 The SqlConnection Object

This lesson describes the SqlConnection object and how to connect to a data base. Here are the objectives of this lesson:

• Know what connection objects are used for.
• Learn how to instantiate a SqlConnection object.
• Understand how the SqlConnection object is used in applications.
• Comprehend the importance of effective connection lifetime management.

Introduction

The first thing you will need to do when interacting with a data base is to create a connection. The connection tells the rest of the ADO.NET code which database it is talking to. It manages all of the low level logic associated with the specific database protocols. This makes it easy for you because the most work you will have to do in code is instantiate the connection object, open the connection, and then close the connection when you are done. Because of the way that other classes in ADO.NET are built, sometimes you don't even have to do that much work.

Although working with connections is very easy in ADO.NET, you need to understand connections in order to make the right decisions when coding your data access routines. Understand that a connection is a valuable resource. Sure, if you have a stand-alone client application that works on a single database one one machine, you probably don't care about this. However, think about an enterprise application where hundreds of users throughout a company are accessing the same database. Each connection represents overhead and there can only be a finite amount of them. To look at a more extreme case, consider a Web site that is being hit with hundreds of thousands of hits a day Applications that grab connections and don't let them go can have seriously negative impacts on performance and scalability.

Creating a SqlConnection Object

A SqlConnection is an object, just like any other C# object.  Most of the time, you just declare and instantiate the SqlConnection all at the same time, as shown below:

    SqlConnection conn = new SqlConnection(
        "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");

The SqlConnection object instantiated above uses a constructor with a single argument of type string This argument is called a connection string. Table 1 describes common parts of a connection string.

Table 1. ADO.NET Connection Strings contain certain key/value pairs for specifying how to make a database connection. They include the location, name of the database, and security credentials.

Connection String Parameter Name	Description
Data Source	Identifies the server. Could be local machine, machine domain name, or IP Address.
Initial Catalog	Database name.
Integrated Security	Set to SSPI to make connection with user's Windows login
User ID	Name of user configured in SQL Server.
Password	Password matching SQL Server User ID.

Integrated Security is secure when you are on a single machine doing development. However, you will often want to specify security based on a SQL Server User ID with permissions set specifically for the application you are using. The following shows a connection string, using the User ID and Password parameters:

    SqlConnection conn = new SqlConnection(
        "Data Source=DatabaseServer;Initial Catalog=Northwind;User ID=YourUserID;Password=YourPassword");

Notice how the Data Source is set to DatabaseServer to indicate that you can identify a database located on a different machine, over a LAN, or over the Internet. Additionally, User ID and Password replace the Integrated Security parameter.

Using a SqlConnection

The purpose of creating a SqlConnection object is so you can enable other ADO.NET code to work with a database. Other ADO.NET objects, such as a SqlCommand and a SqlDataAdapter take a connection object as a parameter. The sequence of operations occurring in the lifetime of a SqlConnection are as follows:

1. Instantiate the SqlConnection.
2. Open the connection.
3. Pass the connection to other ADO.NET objects.
4. Perform database operations with the other ADO.NET objects.
5. Close the connection.

We've already seen how to instantiate a SqlConnection. The rest of the steps, opening, passing, using, and closing are shown in Listing 1.

The SqlCommand Object

This lesson describes the SqlCommand object and how you use it to interact with a database. Here are the objectives of this lesson:

• Know what a command object is.
• Learn how to use the ExecuteReader method to query data.
• Learn how to use the ExecuteNonQuery method to insert and delete data.
• Learn how to use the ExecuteScalar method to return a single value.

Introduction

A SqlCommand object allows you to specify what type of interaction you want to perform with a database. For example, you can do select, insert, modify, and delete commands on rows of data in a database table. The SqlCommand object can be used to support disconnected data management scenarios, but in this lesson we will only use the SqlCommand object alone. A later lesson on the SqlDataAdapter will explain how to implement an application that uses disconnected data. This lesson will also show you how to retrieve a single value from a database, such as the number of records in a table.

Creating a SqlCommand Object

Similar to other C# objects, you instantiate a SqlCommand object via the new instance declaration, as follows:

    SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

The line above is typical for instantiating a SqlCommand object. It takes a string parameter that holds the command you want to execute and a reference to a SqlConnection object. SqlCommand has a few overloads, which you will see in the examples of this tutorial.

Querying Data

When using a SQL select command, you retrieve a data set for viewing. To accomplish this with a SqlCommand object, you would use the ExecuteReader method, which returns a SqlDataReader object. We'll discuss the SqlDataReader in a future lesson. The example below shows how to use the SqlCommand object to obtain a SqlDataReader object:

// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);// 2. Call Execute reader to get query resultsSqlDataReader rdr = cmd.ExecuteReader();

In the example above, we instantiate a SqlCommand object, passing the command string and connection object to the constructor. Then we obtain a SqlDataReader object by calling the ExecuteReader method of the SqlCommand object, cmd.

This code is part of the ReadData method of Listing 1 in the Putting it All Together section later in this lesson.

Inserting Data

To insert data into a database, use the ExecuteNonQuery method of the SqlCommand object. The following code shows how to insert data into a database table:

// prepare command string
 string insertString = @"
     insert into Categories
     (CategoryName, Description)
     values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";

 // 1. Instantiate a new command with a query and connection
 SqlCommand cmd = new SqlCommand(insertString, conn);

 // 2. Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

The SqlCommand instantiation is just a little different from what you've seen before, but it is basically the same. Instead of a literal string as the first parameter of the SqlCommand constructor, we are using a variable, insertString. The insertString variable is declared just above the SqlCommand declaration.

Notice the two apostrophes ('') in the insertString text for the word "doesn''t". This is how you escape the apostrophe to get the string to populate column properly.

Another observation to make about the insert command is that we explicitly specified the columns CategoryName and Description. The Categories table has a primary key field named CategoryID. We left this out of the list because SQL Server will add this field itself. trying to add a value to a primary key field, such as CategoryID, will generate an exception.

To execute this command, we simply call the ExecuteNonQuery method on the SqlCommand instance, cmd.

This code is part of the Insertdata method of Listing 1 in the Putting it All Together section later in this lesson.

Updating Data

The ExecuteNonQuery method is also used for updating data. The following code shows how to update data:

// prepare command string
 string updateString = @"
     update Categories
     set CategoryName = 'Other'
     where CategoryName = 'Miscellaneous'";
 
 // 1. Instantiate a new command with command text only
 SqlCommand cmd = new SqlCommand(updateString);
 
 // 2. Set the Connection property
 cmd.Connection = conn;
 
 // 3. Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

Again, we put the SQL command into a string variable, but this time we used a different SqlCommand constructor that takes only the command. In step 2, we assign the SqlConnection object, conn, to the Connection property of the SqlCommand object, cmd.

This could have been done with the same constructor used for the insert command, with two parameters. It demonstrates that you can change the connection object assigned to a command at any time.

The ExecuteNonQuery method performs the update command.

This code is part of the UpdateData method of Listing 1 in the Putting it All Together section later in this lesson.

Deleting Data

You can also delete data using the ExecuteNonQuery method. The following example shows how to delete a record from a database with the ExecuteNonQuery method:

// prepare command string
 string deleteString = @"
     delete from Categories
     where CategoryName = 'Other'";
 
 // 1. Instantiate a new command
 SqlCommand cmd = new SqlCommand();
 
 // 2. Set the CommandText property
 cmd.CommandText = deleteString;
 
 // 3. Set the Connection property
 cmd.Connection = conn;
 
 // 4. Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

This example uses the SqlCommand constructor with no parameters. Instead, it explicity sets the CommandText and Connection properties of the SqlCommand object, cmd.

We could have also used either of the two previous SqlCommand constructor overloads, used for the insert or update command, with the same result. This demonstrates that you can change both the command text and the connection object at any time.

The ExecuteNonQuery method call sends the command to the database.

This code is part of the DeleteData method of Listing 1 in the Putting it All Together section later in this lesson.

Getting Single values

Sometimes all you need from a database is a single value, which could be a count, sum, average, or other aggregated value from a data set. Performing an ExecuteReader and calculating the result in your code is not the most efficient way to do this. The best choice is to let the database perform the work and return just the single value you need. The following example shows how to do this with the ExecuteScalar method:

// 1. Instantiate a new command
 SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
 
 // 2. Call ExecuteNonQuery to send command
 int count = (int)cmd.ExecuteScalar();

The query in the SqlCommand constructor obtains the count of all records from the Categories table. This query will only return a single value. The ExecuteScalar method in step 2 returns this value. Since the return type of ExecuteScalar is type object, we use a cast operator to convert the value to int.

This code is part of the GetNumberOfRecords method of Listing 1 in the Putting it All Together section later in this lesson.

Putting it All Together

For simplicity, we showed snippets of code in previous sections to demonstrate the applicable techniques. It is also useful to have an entire code listing to see how this code is used in a working program. Listing 1 shows all of the code used in this example, along with a driver in the Main method to produce formatted output.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;// if we add this namespace we can perform database operations

namespace WindowsFormsApplication1
{
    class DbEntry
    {
        public void AddData(int rno, string name)
        {
            bool a;
            string s;
            string connection = "Server = VAMSHI\\SQLEXPRESS; Database = Project;Integrated Security = true";// to connect to our database
            SqlConnection con = new SqlConnection(connection);// this class is responsible to establish connection with sql database.
            try
            {
                con.Open();// is used to open the connection between our application and sql server.
                string query ;               
                query = "Insert into MyFirstTable values("+ rno+",'"+name+"')";
                SqlCommand cmd = new SqlCommand(query,con);// phrase the qurey
                cmd.ExecuteNonQuery();// this command helps to execute the query
            }
            catch(Exception Ex)
            {
                a = false;
                s = Ex.Message;
            }
            finally
            {
                con.Close();
            }
        }
    }
}