ADO.NET and VBasic - Lesson 03: SQL and ADO.NET

	SQL and ADO.NET

Actions on an MSDE Database

Introduction

After establishing a connection, if you are successful, the database system becomes available to you and you can take actions, such as creating a database and/or manipulating data. An action you perform on the database server or on a database is carried as a command.

To support the various actions that you can perform on a Microsoft SQL Server or MSDE database, the .NET Framework provides the SqlCommand class. To use it, declare a variable of type SqlCommand using one of its constructors.

SQL on Command

The SqlCommand class is equipped with four constructors. The default constructor allows you to initiate a command without specifying what action would be taken. The action to perform is created as a string statement. This action is represented by the SqlCommand.CommandText property which is of type string. If you want to use the default constructor, you can then create a string that would carry the action to perform. Once the string is ready, you can assign it the CommandText property. This would be done as follow:

Dim cmdSQL As SqlCommand = New SqlCommand()
Dim strCommandToExecute As String = "Blah Blah Blah"
cmdSQL.CommandText = strCommandToExecute

After creating the action that would be performed, you must specify what connection would carry it. To do this, you can first create a SqlConnection object. To provide it to the command, the SqlCommand class is equipped with a Connection property that is of type SqlConnection. After creating a SqlConnection object, to provide it to the command, you can assign it to the SqlCommand.Connection property.

Instead of declaring a SqlCommand variable and the command text separately, as an alternative, you can define the command text when declaring the SqlCommand variable. To do this, you can use the second constructor of the SqlCommand class. The syntax of this constructor is:

Public Sub New(ByVal cmdText As String)

Once again, after using this constructor, you must specify what connection would carry the action. To do this, you can assign a SqlConnection object to the Connection property of your SqlCommand.

Instead of assigning the SqlConnection to the SqlCommand.Connection property, you can specify what connection would carry the action at the same time you are creating the command. To specify the connection when declaring the SqlCommand variable, you can use the third constructor of this class. Its syntax is:

Public Sub New(ByVal cmdText As String, ByVal connection As SqlConnection)

The second argument to this constructor is an established connection you would have defined. If you had initiated the action using the default constructor of the SqlCommand class, you can assign a SqlConnection object to the Connection property of the SqlCommand class.

In the next sections and future lessons, we will study the types of commands that would be carried.

Command Execution

After establishing a connection and specifying what command needs to be carried, you can execute it. To support this, the SqlCommand class is equipped with the ExecuteNonQuery() method. Its syntax is:

Public Overridable Function ExecuteNonQuery() As Integer Implements _
   IDbCommand.ExecuteNonQuery

This method doesn't take any argument. The SqlCommand object that calls it must have prepared a valid command.

In future lessons, we will see that there are other ways a SqlCommand object can execute commands.

Well, the Command Timed Out

In some cases, some actions take longer than others to execute. For this type of command, the compiler would keep trying to execute a command until successful. If there is a problem, this operation can take long or too long. You can specify how long the compiler should wait to try executing the command, again.

The SqlCommand.CommandTimeOut property allows you to specify the time to wait before trying to execute a command. The default value of this property is 30 (seconds). If you want a different value, assign it to your SqlCommand variable.

The Type of Command

In this and the next few lessons, all of the commands we perform will be communicated as strings. To allow you to specify the type of command you want to perform, the SqlCommand class is equipped with the CommandType property, which is based on the CommandType enumerator.

The CommandType enumerator has three members: StoredProcedure, TableDirect, and Text. For a SqlCommand object, the default value is Text.

The Structured Query Language

Introduction

The Structured Query Language, known as SQL, is a universal language used on various computer systems to create and manage databases.

SQL can be pronounced Sequel or S. Q. L. In our lessons, we will consider the Sequel pronunciation. For this reason, the abbreviation will always be considered as a word, which would result in “A SQL statement” instead of "An SQL statement". Also, we will regularly write, “The SQL” instead of “The SQL language, as the L already represents Language.

Like other non-platform specific languages such as C/C++, C#, Pascal, or Java, the SQL you learn can be applied to various database systems. To adapt the SQL to Microsoft SQL Server or MSDE, 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. Unlike C# and XML as we have learned in the past, 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.

SQL Code

When creating your application, you can write your SQL code and pass it to a SqlCommand object you would have created as we saw earlier. This would be done as follows:

Imports System
Imports System.Data.SqlClient

Module Exercise

    Public Sub Main()

        Dim conDatabase As SqlConnection = _
          New SqlConnection("Data Source=(local);Integrated Security=yes;")
        Dim cmdDatabase As SqlCommand = New SqlCommand(SQL Code, conDatabase)

        conDatabase.Open()

        cmdDatabase.ExecuteNonQuery()
        conDatabase.Close()
    End Sub

End Module

In this example, the SQL Code factor represents a SQL statement you would write and pass it as a string.

Database Creation

Introduction

Probably before using a database, you must first have one. If you are just starting with databases and you want to use one, Microsoft SQL Server ships with two databases ready for you. One of these databases is called Northwind and the other is called pubs.

Besides, or instead of, the Northwind and the pubs databases, you can create your own. A database is primarily a group of computer files that each has a name and a location. When you create a database using Microsoft SQL Server or MSDE, it is located in the Drive:\Program Files\Microsoft SQL Server\MSSQL\Data folder.

The Name of a Database

When creating a new database, you must give it a name. The SQL is very flexible when it comes to names. In fact, it is very less restrictive than C#. Still, there are rules you must follow when naming the objects in your databases:

A name can start with either a letter (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z), a digit (0, 1, 2, 3, 4, 5, 6, 7, 8, or 9), an underscore (_) or a non-readable character. Examples are _n, act, %783, Second
After the first character (letter, digit, underscore, or symbol), the name can have combinations of underscores, letters, digits, or symbols. Examples are _n24, act_52_t
A name cannot include space, that is, empty characters. If you want to use a name that is made of various words, start the name with an opening square bracket and end it with a closing square bracket. Example are [Full Name] or [Date of Birth]

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. In our databases:

A name will start with either a letter (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z) or an underscore
After the first character, we can use any combination of letters, digits, or underscores
A name will not start with two underscores
A name will not include one or more empty spaces. That is, a name will be made in one word
If the name is a combination of words, at least the second word will start in uppercase. Examples are dateHired, _RealSport, FullName, or DriversLicenseNumber

Creating a Database

To create a database, at the Command Prompt of MSDE, you use 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. Although SQL is not case-sensitive, as a C# programmer, you should make it a habit to be aware of the cases you use to name your objects.

As done in C#, every 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

To execute a statement, you can simply press Enter.

To create a database with code, simply pass a CREATE DATABASE statement (including the name of the database) to a SqlCommand object.

Practical Learning: Creating a Database

Start Notepad and type the following:

Imports System
Imports System.Data.SqlClient

Module Exercise
    Private Sub CreateNewDatabase()
        Dim conDatabase As SqlConnection = _
              New SqlConnection("Data Source=(local);Integrated Security=yes")
        Dim cmdDatabase As SqlCommand = _
         New SqlCommand("CREATE DATABASE Countries", conDatabase)

        conDatabase.Open()

        cmdDatabase.ExecuteNonQuery()
        conDatabase.Close()
    End Sub

    Public Sub Main()
        Dim answer As Integer = 0

        Console.WriteLine(" =-= Main Menu =-=")
        Console.WriteLine("0. Quit")
        Console.WriteLine("1. Create New Database")
        Console.Write("Your Choice: ")
        answer = CInt(Console.ReadLine())

        Select Case answer
            Case 1
                CreateNewDatabase()
        End Select
    End Sub

End Module

To save the file, on the main menu, click File -> Save
Locate your VBasic folder and display it in the Save In combo box
Click the Create New Folder button. Type Countries1 and press Enter twice to display it in the Save In combo box
Change the Save As Type to All Files
Set the File Name to Exercise.vb
Open the Command Prompt and change to the above Countries1 folder
To compile the exercise, type vbc Exercise.vb and press Enter
To execute the application, type Exercise and press Enter
Return to Notepad

To allow the user to specify a name for the new database, change the function as follows:

Imports System
Imports System.Data.SqlClient

Module Exercise
    Private Sub CreateNewDatabase()
        Dim strDatabase As String

        Console.Write("Enter the name of the database: ")
        strDatabase = Console.ReadLine()

        Dim strConnection As String = "CREATE DATABASE " & strDatabase & ""
        Dim conDatabase As SqlConnection = _
         New SqlConnection("Data Source=(local);Integrated Security=yes")
        Dim cmdDatabase As SqlCommand = New SqlCommand(strConnection, conDatabase)

        conDatabase.Open()

        cmdDatabase.ExecuteNonQuery()
        conDatabase.Close()
    End Sub

    Public Sub Main()
        Dim answer As Integer = 0

        Console.WriteLine(" =-= Main Menu =-=")
        Console.WriteLine("0. Quit")
        Console.WriteLine("1. Create New Database")
        Console.Write("Your Choice: ")
        answer = CInt(Console.ReadLine())

        Select Case answer
            Case 1
                CreateNewDatabase()
        End Select
    End Sub

End Module

Save the file
Compile and execute it to create a sample database named ROSH2000
Return to Notepad

Database Maintenance

Renaming a Database

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, we can use them exactly as you have learned to use functions like cos or pow in C#: You don't need to know how they work but 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 in SQL at the MSDE Command Prompt:

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.

Deleting a Database

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, every database can be accessed by any of these tools and you can delete any of the databases using any of these tools.

To delete a database, 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.

Practical Learning: Deleting a Database

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

Imports System
Imports System.Data.SqlClient

Module Exercise
    Private Sub CreateNewDatabase()
        Dim strDatabase As String

        Console.Write("Enter the name of the database: ")
        strDatabase = Console.ReadLine()

        Dim strConnection As String = "CREATE DATABASE " & strDatabase & ""
        Dim conDatabase As SqlConnection = _
         New SqlConnection("Data Source=(local);Integrated Security=yes")
        Dim cmdDatabase As SqlCommand = New SqlCommand(strConnection, conDatabase)

        conDatabase.Open()

        cmdDatabase.ExecuteNonQuery()
        conDatabase.Close()
    End Sub

    Private Sub DeleteDatabase()
        Dim strDatabase As String

        Console.Write("Enter the database to delete: ")
        strDatabase = Console.ReadLine()

        If strDatabase = "master" Then
            Console.WriteLine("You are not allowed to delete the ""master"" database")
            Exit Sub
        End If

        If strDatabase = "model" Then
            Console.WriteLine("You are not allowed to delete the ""model"" database")
            Exit Sub
        End If

        If strDatabase = "msdb" Then
            Console.WriteLine("You are not allowed to delete the ""msdb"" database")
            Exit Sub
        End If

        If strDatabase = "tempdb" Then
            Console.WriteLine("You are not allowed to delete the ""tempdb"" database")
            Exit Sub
        End If

        If strDatabase = "Northwind" Then
            Console.WriteLine("Even though you can, don't delete the Northwind database")
            Exit Sub
        End If

        If strDatabase = "pubs" Then
            Console.WriteLine("Even though you can, don't delete the ""pubs"" database")
            Exit Sub
        End If

        Dim strConnection As String = "DROP DATABASE " & strDatabase & ""
        Dim conDatabase As SqlConnection = New SqlConnection("Data Source=(local);Integrated Security=yes;")
        Dim cmdDatabase As SqlCommand = New SqlCommand(strConnection, conDatabase)

        conDatabase.Open()

        cmdDatabase.ExecuteNonQuery()
        conDatabase.Close()
    End Sub

    Public Sub Main()
        Dim answer As Integer = 0

        Console.WriteLine(" =-= Main Menu =-=")
        Console.WriteLine("0. Quit")
        Console.WriteLine("1. Create New Database")
        Console.WriteLine("2. Delete a Database")
        Console.Write("Your Choice: ")
        answer = CInt(Console.ReadLine())

        Select Case answer
            Case 1
                CreateNewDatabase()
            Case 2
                DeleteDatabase()
        End Select
    End Sub

End Module

Save the file and return to the Command Prompt
Compile and execute the application
Try to delete the database created in the previous section
Also delete the database named Countries
Type Exit and press Enter to close the DOS window
Close Notepad to end the lesson

Accessories for SQL Code Writing

The Current Database

While writing SQL code at the Command Prompt, 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, type the USE keyword followed by the name of the database. Here is an example:

USE Northwind

The End of a Statement

In SQL, after writing a statement, you can end it with a semi-colon. In fact, many SQL environments require the semi-colon. If you want to execute many statements at the Command Prompt, you can write them once. Here is an example:

CREATE DATABASE CountriesStatistics
USE CountriesStatistics

In this case, you are asking SQL Server to create a database and then to make it the current database. The problem here is that, when the interpreter encounters these two lines, it would execute all of them at once and in fact would treat both lines as one expression. When the statements are executed, some of them must come after others. To separate statements, that is, to indicate when a statement ends, you can/must use the GO keyword (in reality and based on SQL standards, it is the semi-colon that would be more relevant, but the Microsoft SQL Server interpreter accepts GO as the end of a statement). Therefore, the above two statements can be performed separately but with one execution as follows:

CREATE DATABASE CountriesStatistics
GO
USE CountriesStatistics
GO

This time, when you execute the code, it would work fine.

Comments

A comment is text that an 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 C# style of comment that starts with /* and ends with */ can be used in SQL. 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:

-- =============================================
-- Database: MotorVehicleDivision
-- =============================================
/* First find out if the database we want to create exists already */
IF EXISTS (SELECT * 
	   FROM   master..sysdatabases 
	   WHERE  name = N'MotorVehicleDivision')
	DROP DATABASE MotorVehicleDivision
GO

CREATE DATABASE MotorVehicleDivision
GO

A comment can also spread on more than one line, like a paragraph. 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. */

IF EXISTS (SELECT * 
	   FROM   master..sysdatabases 
	   WHERE  name = N'MotorVehicleDivision')
	DROP DATABASE MotorVehicleDivision
GO

CREATE DATABASE MotorVehicleDivision
GO

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. */

IF EXISTS (SELECT * 
	   FROM   master..sysdatabases 
	   WHERE  name = N'MotorVehicleDivision')
	DROP DATABASE MotorVehicleDivision
GO

-- Now that the database is not in the system, create it
CREATE DATABASE MotorVehicleDivision
GO


Previous	Copyright © 2005-2016, FunctionX	Next