Connecting to a Database

Once you have established a connection, you can then open it and perform the desired actions:

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : Form
{
    public Exercise()
    {
	InitializeComponent();
    }

    void InitializeComponent()
    {
	SqlConnection connection =
		new SqlConnection("Data Source=(local);" +
				  "Database='Exercise';" +
				  "Integrated Security=yes;");
	SqlCommand command = new SqlCommand(SQL Code, connection);

	connection.Open();
	command.ExecuteNonQuery();
	connection.Close();
    }
}

public class Program
{
    static int Main()
    {
	System.Windows.Forms.Application.Run(new Exercise());
	return 0;
    }
}

Database maintenance consists of renaming one database or removing another. To change the name of a database, Transact-SQL provides sp_renamedb. (Notice that the name starts with sp_. This is called a stored procedure. We will learn how to create them. For now, you can trust that they work and do what they are supposed to do). The formula used would be:

EXEC sp_renamedb 'ExistingName', 'NewName'

The EXEC sp_renamedb expression is required. The ExistingName factor is the name of the database that you want to rename. The NewName factor is the name you want the database to have after renaming it.

Here is an example of renaming a database:

EXEC sp_renamedb 'RentalCars', 'BethesdaCarRental
GO

To rename a table in a C# code, pass the EXEC sp_renamedb code as string to a SqlCommand object and call the SqlCommand.ExecuteNonQuery() method.

If you have created a database but don't need it anymore, you can delete it. It is important to know, regardless of how you create a database, whether using Microsoft SQL Server Management Studio, the Command Prompt, or Microsoft Visual Studio, every database can be access by any of these tools and you can delete any of the databases using any of these tools.

As done with creating a database, every tool provides its own means.

To delete a database in Microsoft SQL Server Management Studio, locate it in the left frame, right-click it and click Delete:

The Delete Object dialog box would come up. If you still want to delete the database, you can click OK.

To delete a database in SQL, you use the DROP DATABASE instruction followed by the name of the database. The formula used is:

DROP DATABASE DatabaseName

Before deleting a database in SQL, you must make sure the database is not being used or accessed by some one else or by another object.

If you are in the Microsoft SQL Server Management Studio, you can delete a database using a code template. To get that code, display the Template Explorer. From the Template Explorer, expand the Databases node, then drag the Create Database node and drop it in the query window. You can then customize the generated code:

-- =============================================
-- Create database template
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'<Database_Name, sysname, Database_Name>'
)
DROP DATABASE <Database_Name, sysname, Database_Name>
GO

1. To allow the user to delete a database, change the design of the form as follows:
     

   Control Text Name Label Database Name:   TextBox   txtDatabaseCreate Button Create btnCreateDatabase Label Database Name:   TextBox   txtDatabaseDelete Button Create btnDeleteDatabase

2. Double-click the Delete button
3. Implement the Click event of the button as follows:
    

   private void btnDeleteDatabase_Click(object sender, EventArgs e) { // Make sure the user enters a database if( txtDeleteDatabase.Text.Length == 0) { MessageBox.Show("You must specify the name of " + "the database you want to delete"); txtDatabaseCreate.Focus(); } string strConnection = "Data Source=(local);Integrated Security=yes"; using (SqlConnection connection = new SqlConnection(strConnection)) { string strDatabase = txtDeleteDatabase.Text; SqlCommand command = new SqlCommand("DROP DATABASE [" + strDatabase + "];", connection); connection.Open(); command.ExecuteNonQuery(); MessageBox.Show("A database named \"" + txtDeleteDatabase.Text + "\" has been deleted from the " + connection.DataSource + " server."); txtDeleteDatabase.Text = ""; } }

4. Execute the application
5. In the Database text box, type Red Oak High School1
6. Click the Delete button
    
   
7. Click OK
8. Close the form and return to your programming environment

While writing SQL code, you should always know what database you are working on, otherwise you may add code to the wrong database. To programmatically specify the current database, use the USE keyword followed by the name of the database. The formula to use is:

USE DatabaseName;

Here is an example:

USE GovernmentStatistics;

Some of the windows that display databases, like the Microsoft SQL Server Management Studio, don't update their list immediately if an operation occurred outside their confinement. For example, if you create a database in the query window or in a Windows Application, its name would not be updated in the Object Explorer. To view such external changes, you can refresh the window that holds the list.

In Microsoft SQL Server Management Studio, to update a list, you can right-click its category in the Object Explorer and click Refresh. Only that category may be refreshed. For example, to refresh the list of databases, in the Object Explorer, you can right-click the Databases node and click Refresh.

When you install Microsoft SQL Server, it also installs 5 databases named master, model, msdb, and tempdb. These databases will be for internal use. This means that you should avoid directly using them, unless you know exactly what you are doing.

One of the databases installed with Microsoft SQL Server is named master. This database holds all the information about the server on which your Microsoft SQL Server is installed. For example, we saw earlier that, to perform any operation on the server, you must login. The master database identifies any person, called a user, who accesses the database, about when and how.

Besides identifying who accesses the system, the master database also keeps track of everything you do on the server, including creating and managing databases.

You should not play with the master database; otherwise you may corrupt the system. For example, if the master database is not functioning right, the system would not work.

As you should know from your learning C#, a namespace is a technique of creating a series of items that each has a unique name. For example, if you start creating many databases, there is a possibility that you may risk having various databases with the same name. If using a namespace, you can isolate the databases in various namespaces. In reality, to manage many other aspects of your database server, you use namespaces and you put objects, other than databases, within those namespaces. Therefore, a namespace and its content can be illustrated as follows:

Notice that there are various types of objects within a namespace.

Within a namespace, you can create objects as you wish. To further control and manage the objects inside of a namespace, you can put them in sub-groups called schemas. Therefore, a schema is a group of objects within a namespace. This also means that, within a namespace, you can have as many schemas as you want:

To manage the schemas in a namespace, you need a way to identify each schema. Based on this, each schema must have a name. In our illustration, one schema is named Schema1. Another schema is named Schema2. Yet another schema is named Schema_n.

Inside of a schema, two objects cannot have the same name, but an object in one schema can have the same name as an object in another schema. Based on this, if you are accessing an object within its schema, you can simply use its name, since that name would be unique. On the other hand, because of the implied possibility of dealing with objects with similar names in your server, when accessing an object outside of its schema, you must qualify it. To do this, you would type the name of the schema that contains the object you want to use, followed by the period operator, followed by the name of the object you want to use. From our illustration, to access the Something1 object that belongs to Schema1, you would type:

Schema1.Something1

There are two types of schemas you can use, those built-in and those you create. When Microsoft SQL Server is installed, it also creates a few schemas. One of the schemas is called sys.

The sys schema contains a list of some of the objects that exist in your system. One of these objects is called databases (actually, it's a view). When you create a database, its name is entered in the databases object using the same name you gave it.

In the previous lesson, we saw that, before using a database, you must establish a connection with the server. You do this using a user account that can use the server. Once the connection exists, you can create a database. In Microsoft SQL Server, the user who creates a database is referred to as the database owner. To identify this user, when Microsoft SQL Server is installed, it also creates a special user account named dbo. This account is automatically granted various permissions on the databases of the server.

Because the dbo account has default access to all databases, to refer to an object of a database, you can qualify it by typing dbo, followed by the period operator, followed by the name of the object.