Before using a database, you must first have one. 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:
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim connection As SqlConnection = _
New SqlConnection("Data Source=(local);Integrated Security=yes")
Dim command As SqlCommand = _
New SqlCommand(Database Creation Code, connection)
connection.Open()
Command.ExecuteNonQuery()
connection.Close()
End Sub
End Class
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) 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:
CREATE DATABASE DatabaseName;
Probably the most important requirement of creating a
database is to give it a name. There are rules you must follow when naming the objects in
your databases:
Here is an example of creating a database:
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim strConnection As String = _
"Data Source=(local);Integrated Security=yes"
Using connection As SqlConnection = New SqlConnection(strConnection)
Dim strDatabase As String = "Exercise"
Dim cmd As SqlCommand = _
New SqlCommand("CREATE DATABASE [" & strDatabase & "];", _
connection)
connection.Open()
cmd.ExecuteNonQuery()
MsgBox("A database named ""Exercise"" has been created on the " & _
connection.DataSource & " server.")
End Using
End Sub
End Class
Once a database exists on the server, to use it, you must first establish a connection to it.
To programmatically connect 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:
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim connection As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='Exercise';" & _
"Integrated Security=yes;")
End Sub
End Class
Once you have established a connection, you can
then open it and perform the desired actions.
If you have created a database but do not need it anymore, you can
delete it. 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
Here is an example:
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim strConnection As String = _
"Data Source=(local);Integrated Security=yes"
Using connection As SqlConnection = New SqlConnection(strConnection)
Dim strDatabase As String = "Exercise"
Dim cmd As SqlCommand = _
New SqlCommand("DROP DATABASE [" & strDatabase & "];", _
connection)
connection.Open()
cmd.ExecuteNonQuery()
MsgBox("A database named """ & _
strDatabase & _
""" has been deleted from the " & _
connection.DataSource & " server.")
End Using
End Sub
End Class
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.
|
Introduction to SQL
Subscribe to:
Post Comments (Atom)
Related Scripting
No comments:
Post a Comment