The Object Explorer is a dockable window, meaning you can move it from the left side to another side on the screen. To do this, you can click and drag its title bar to a location of your choice. The Object Explorer is also floatable, which means you can place it somewhere in the middle of the interface:
To place the window back to its previous position, you can double-click its title bar. The Object Explorer can also be tabbed. This means that the window can be positioned either vertically or horizontally. The right side of the window is equipped by a window whose tab is labeled Summary. This area will be used to display either the contents of what is selected in the Object Explorer, or to show a result of some operation. As you will see later on, many other windows will occupy the right section but they will share the same area. To make each known, it will be represented with a tab and the tab shows the name (or caption) of a window.
After installing Microsoft SQL Server, you can use it to create and manage databases. To assist you with this, you can use Microsoft SQL Server Management Studio. Microsoft SQL Server Management Studio is a window you will use to create and manage databases. To assist you with this, it comes equipped with various tools, some are graphically-based and some others require code from you. To perform some operations, you will use the main menu. Some other operations will be available from clicking one of the buttons on the toolbar. Many other operations will start from the Object Explorer. The Object Explorer displays a list of items as a tree-style of window. One of the most regularly used items will be the name of the server you are using. If you are just starting to learn database development or you are a junior database developer, you may use or see only one server. In some cases, you may be dealing with many. Regardless, you should always know what server you are currently connecting to. This is easy to check with the first node of the Object Explorer. In the following example, the server is named Central: The name of the server is followed by parentheses. In previous lessons, we saw that, to establish a connection to a server, you must authenticate yourself. In some cases you may use the same account over and over again. In some other cases you may have different accounts that you use for different scenarios, such as one account for database development, one account for database management, and/or one account for database testing. Some operations cannot be performed by some accounts. When performing some operations, you should always know what account you are using. You can check this in the parentheses of the server name. In the following connection, an account called Administrator is currently connecting to a server named Central: When the server name is selected in the Object Explorer, the whole caption of its node displays in the Summary window. For our lessons, probably the most regular node you will be interested in, is labeled Databases. This node holds the names of databases on the server you are connecting to. Also, from that node, you can perform almost any necessary operation of a database. To see most of the regularly available actions, you can expand the Databases node and some of its children. You can then right-click either Databases or one of its child nodes.
Although you will perform many of your database operations visually, some other operations will require that you write code. To assist with with this, Microsoft SQL Server provides a code editor and various code templates. To open the editor:
This would open a new window and position it on the right side of the interface. Whether you have already written code or not, you can save the document of the code editor at any time. To save it:
You will be required to provide a name for the file. After saving the file, its name would appear on the tab of the document. Besides the SQL Server Management Studio, you can also work on Microsoft SQL Server from the DOS command prompt. This is done using an application or command named SQLCMD.EXE. To use it, open the Command Prompt, type SQLCMD (case-insensitive) and press Enter:
You can then write SQL code.
After establishing a connection, you can take actions, such as creating a database and/or manipulating data. The Structured Query Language, known as SQL, is a universal language used on various computer systems to create and manage databases.
Like other non-platform specific languages such as C/C++, Pascal, or Java, the SQL you learn can be applied to various database systems. To adapt the SQL to Microsoft SQL Server, the company developed Transact-SQL as Microsoft's implementation of SQL. Transact-SQL is the language used internally by Microsoft SQL Server and MSDE. Although SQL Server highly adheres to the SQL standards, it has some internal details that may not be applied to other database systems like MySQL, Oracle, or even Microsoft Access, etc; although they too fairly conform to the standard. The SQL we will learn and use here is Transact-SQL. In other words, we will assume that you are using Microsoft SQL Server as your platform for learning about databases. This means that, unless specified otherwise, most of the time, on this site, the word SQL refers to Transact-SQL or the way the language is implemented in Microsoft SQL Server.
As a computer language, the SQL is used to give instructions to an internal program called an interpreter. As we will learn in various sections, you must make sure you give precise instructions. SQL is not case-sensitive. This means that CREATE, create, and Create mean the same thing. It is a tradition to write SQL's own words in uppercase. This helps to distinguish SQL instructions with the words you use for your database. As we will learn in this and the other remaining lessons, you use SQL by writing statements. To help you with this, Microsoft SQL Server provides a window, also referred to as the Query Window, that you can use to write your SQL code. To access it, on the left side of the window, you can right-click the name of the server and click New Query. In the same way, you can open as many instances as the New Query as you want. When the Query window comes up, it display a blank child window in which you can write your code:
The code
you write is a document and it can be saved as a file. The file would have
the extension .sql. Every time you open a new query, it is represented with
a tab. To switch from one code part to another, you can click its tab. To
dismiss an instance of the query, first access it (by clicking its tab), then, on
the right side, click the close button If you are working in a Windows Application, you can write the exact same code you would in a query window. Once your code is ready, you can pass it to a SqlCommand object you would have created as we saw already. This would be done as follows: void InitializeComponent()
{
SqlConnection connection =
new SqlConnection("Data Source=(local);Integrated Security=yes");
SqlCommand command = new SqlCommand(SQL Code, connection);
}
In this example, the SQL Code factor represents a SQL statement you would write and pass it as a string. In the next sections and lessons, we will learn various techniques of creating SQL statements with code. By default, when a new query window appears, after writing a statement, you can execute it, either to make it active or simply to test it. To execute a statement:
When you execute code, the query window becomes divided into two horizontal sections:
Also, when you execute code, the interpreter would first analyze it. If there is an error, it would display one or more red lines of text in its bottom section. Here is an example:
If there is no error in the code, what happens when you execute a statement depends on the code and the type of statement. If you are working in a Windows Forms Application, after passing the SQL code to a command, to execute it, as we saw in the previous lesson, you can call the ExecuteNonQuery() method of your SqlCommand object. This would be done as follows: void InitializeComponent()
{
SqlConnection connection =
new SqlConnection("Data Source=(local);Integrated Security=yes");
SqlCommand command = new SqlCommand(SQL Code, connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
A comment is text that the SQL interpreter would not consider as code. As such, a comment is written any way you like. What ever it is made of would not be read. Transact-SQL supports two types of comments. The style of comment that starts with /* and ends with */ can be used. To apply it, start a line with /*, include any kind of text you like, on as many lines as you want. To close the commented section, type */. Here is an example of a line of comment: /* First find out if the database we want to create exists already */ A comment can also be spread on more than one line, like a paragraph. Here is an example: /* First find out if the MotorVehicleDivision database we want to create exists already. If that database exists, we don't want it anymore. So, delete it from the system. */ Transact-SQL also supports the double-dash comment. This comment applies to only one line of text. To use it, start the line with --. Anything on the right side of -- is part of a comment and would not be considered as code. Here is an example: -- ============================================= -- Database: MotorVehicleDivision -- ============================================= /* First find out if the MotorVehicleDivision database we want to create exists already. If that database exists, we don't want it anymore. So, delete it from the system. */ -- Now that the database is not in the system, create it
In SQL, after writing a statement, you can end it with a semi-colon. In fact, if you plan to use many statements in one block, you should end each with a semi-colon. When many statements are used, some of them must come after others.
To separate statements, that is, to indicate when a statement ends, you can use the GO keyword (in reality and based on SQL standards, it is the semi-colon that would be required, but the Microsoft SQL Server interpreter accepts GO as the end of a statement).
Before using a database, you must first have one. A database is primarily a group of computer files that each has a name and a location. Just as there are different ways to connect to a server, there are also different ways to create a database. You can create a new database in Microsoft SQL Server Management Studio, in Microsoft Visual Studio, or on the Command Prompt. To create a database in Microsoft SQL Server Management Studio, you can right-click the Databases node and click New Database...
If you are working from Microsoft Visual Studio, to create a new database, in the Server Explorer, you can right-click Data Connections and click Create New SQL Server Database...
To programmatically create a database, pass the necessary SQL code as the command text of the SqlCommand object: private void btnDatabase_Click(object sender, EventArgs e)
{
SqlConnection connection =
new SqlConnection("Data Source=(local);Integrated Security=yes");
SqlCommand command = new SqlCommand(Database Creation Code, connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
To create a database from the Command Prompt, open the DOS window and use the SQLCMD program. Then write code as we will learn next. The command used to create a database in SQL uses the following formula: CREATE DATABASE DatabaseName The CREATE DATABASE (remember that SQL is not case-sensitive, even when you include it in a C# statement) expression is required. The DatabaseName factor is the name that the new database will carry. A statement in SQL can be terminated with a semi-colon. Although this is a requirement in many implementations of SQL, in Microsoft SQL Server, you can omit the semi-colon. Otherwise, the above formula would be CREATE DATABASE DatabaseName; Instead of manually writing all of your code, the Microsoft SQL Server Management Studio provides a code template you can use and customize. To access the Template Explorer, on the main menu, you can click View -> Template Explorer. Before creating a database, open a new query window. Then:
With any of these actions, Microsoft SQL Server would generate sample code for you: -- ============================================= -- 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>' ) CREATE DATABASE <Database_Name, sysname, Database_Name> GO You would then need to edit the code and execute it to create the database. If some sections of this code are not familiar to you, we will cover them in subsequent lessons
Probably the most important requirement of creating a database is to give it a name. The SQL is very flexible when it comes to names. In fact, it is very less restrictive than most other computer languages. Still, there are rules you must follow when naming the objects in your databases:
Because of the flexibility of SQL, it can be difficult to maintain names in a database. Based on this, there are conventions we will use for our objects. In fact, we will adopt the rules used in C/C++, C#, Pascal, Java, and Visual Basic, etc languages. In our databases:
After creating an object whose name includes space, whenever you use that object, include its name between [ and ]. Examples are [Countries Statistics], [Global Survey], or [Date of Birth]. Even if you had created an object with a name that doesn't include space, when using that name, you can still include it in square brackets. Examples are [UnitedStations], [FullName], [DriversLicenseNumber], and [Country].
As you should be aware already from your experience on using computers, every computer file must have a path. The path is where the file is located in one of the drives of the computer. This allows the operating system to know where the file is so that when you or another application calls it, the operating system would not be confused. By default, when you create a new database, Microsoft SQL Server assumed that it would be located at Drive:C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data folder. If you use the New Database dialog box of the SQL Server Management Studio, if you specify the name of the database and click OK, the interpreter automatically creates a new file, and appends the .MDF extension to the file: this is the (main) primary data file of your database.
When originally creating a database, you may or may not know how many lists, files, or objects the project would have. Still, as a user of computer memory, the database must use a certain portion, at least in the beginning. The amount of space that a database is using is referred to as its size. If you use the New Database dialog box in the Microsoft SQL Server Management Studio, after specifying the name of the database and clicking OK, the interpreter automatically specifies that the database would primarily use 2MB. This is enough for a starting database. Of course, you can either change this default later on or you can increase it when necessary. If you want to specify a size different from the default, if you are using the New Database to create your database, in the Database Files section and under the Initial Size column, change the size as you wish.
Once a database exists on the server, to use it, as we saw in the previous lesson, you must first establish a connection to it. We saw that, to programmatically connection to a Microsoft SQL Server database, you could use a SqlConnection variable. In the connection string, to specify the database, assign its name to the Database attribute. Here is an example: void CreateConnection()
{
SqlConnection connection =
new SqlConnection("Data Source=(local);Database='Exercise';Integrated Security=yes;");
}
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 accessed 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 someone 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
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.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
.
If you had written code in the query window, when you close it, you would
be asked to save your code. If you want to preserve your code, then save
it. If you had already executed the code in the window (we will learn how
to write and execute SQL code), you don't have to save the contents of the
window.
Practical
Learning: Creating a Database
