|
|
|
|
|
|
Author Rank:
|
|
Total page views :
245332
|
|
Total downloads :
|
|
|
|
|
|
|
Similar ArticlesMost ReadTop RatedLatest
|
|
|
|
|
|
|
|
|
|
SQL not only let you select, add, and delete data from databases table, it also provides commands to manage databases. Using SQL statements you can create database objects programmatically such as a table, view, stored procedure, rule, index and so on. It also provides commands to alter a database and database schemas for example adding and deleting a column from a database table, adding some constraints to a column and so on. This example shows you how to create a new database table, add data to it, create a view of the data, alter database table and then delete the newly created table.
In this application, I'll create a SQL Server database, create a database table, add data to it, create database objects such as views, stored procedures, rules, and index and view data in the data grid using Sql data provider.
To test this application, create a Widows application add a data grid control and some button controls. You can even test code by adding only one button or one button for each activity. Our application form looks like Figure 1.
 Figure 1. Creating a database and it's object application.
After adding controls, add the following variables in the beginning of the form class.
Private ConnectionString As String = "Integrated Security=SSPI;" + "Initial Catalog=;" + "Data Source=localhost;" Private reader As SqlDataReader = Nothing Private conn As SqlConnection = Nothing Private cmd As SqlCommand = Nothing Private AlterTableBtn As System.Windows.Forms.Button Private sql As String = Nothing Private CreateOthersBtn As System.Windows.Forms.Button Private button1 As System.Windows.Forms.Button
First thing I'm going to do is create ExecuteSQLStmt method. This method executes a SQL statement against the SQL Sever database (mydb which I will create from my program) using Sql data providers using ExecuteNonQuery method. The ExecuteSQLStmt method is listed in Listing 1.
Private Sub ExecuteSQLStmt(ByVal sql As String) ' Open the connection If conn.State = ConnectionState.Open Then conn.Close() End If ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=localhost;" conn.ConnectionString = ConnectionString conn.Open() cmd = New SqlCommand(sql, conn) Try cmd.ExecuteNonQuery() Catch ae As SqlException MessageBox.Show(ae.Message.ToString()) End Try End Sub 'ExecuteSQLStmt
Listing 1. The ExecuteSQLStmt method.
After this I'm going to create a new SQL Server database. The CREATE DATABASE SQL statement creates a database. The syntax of CREATE DATABASE depends on the database you create. Depending on the database type, you can also set values of database size, growth and file name. Listing 2 creates a SQL Server database mydb and data files are stored in the C:\\mysql directory.
'This method creates a new SQL Server database Private Sub CreateDBBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) ' Create a connection conn = New SqlConnection(ConnectionString) ' Open the connection If conn.State <> ConnectionState.Open Then conn.Open() End If Dim sql As String = "CREATE DATABASE mydb ON PRIMARY" + "(Name=test_data, filename = 'C:\mysql\mydb_data.mdf', size=3," + "maxsize=5, filegrowth=10%)log on" + "(name=mydbb_log, filename='C:\mysql\mydb_log.ldf',size=3," + "maxsize=20,filegrowth=1)" ExecuteSQLStmt(sql) End Sub 'CreateDBBtn_Click
Listing 2. Creating a SQL Server database.
Now next step is to create a table. You use CREATE TABLE SQL statement to create a table. In this statement you define the table and schema (table columns and their data types). Listing 3 creates a table myTable with four column listed in Table 1.
| Column Name |
Type |
Size |
Property |
| myId |
integer |
4 |
Primary Key |
| myName |
char |
50 |
Allow Null |
| myAddress |
char |
255 |
Allow Null |
| myBalance |
float |
8 |
Allow Null |
Table 1. New table myTable schema.
Private Sub CreateTableBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) ' Open the connection If conn.State = ConnectionState.Open Then conn.Close() End If ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=localhost;" conn.ConnectionString = ConnectionString conn.Open() sql = "CREATE TABLE myTable" + "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," + "myName CHAR(50), myAddress CHAR(255), myBalance FLOAT)" cmd = New SqlCommand(sql, conn) Try cmd.ExecuteNonQuery() ' Adding records the table sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 23.98 ) " cmd = New SqlCommand(sql, conn) cmd.ExecuteNonQuery() sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 353.64) " cmd = New SqlCommand(sql, conn) cmd.ExecuteNonQuery() sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 43.43) " cmd = New SqlCommand(sql, conn) cmd.ExecuteNonQuery() sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 23.00) " cmd = New SqlCommand(sql, conn) cmd.ExecuteNonQuery() Catch ae As SqlException MessageBox.Show(ae.Message.ToString()) End Try End Sub 'CreateTableBtn_Click
Listing 4. Creating a database table.
As you can see from Listing 5, I also add data to the table using INSERT INTO SQL statement.
The CREATE PROCEDURE statement creates a stored procedure as you can see in Listing 10-18, I create a stored procedure myPoc which returs data result of SELECT myName and myAddress column.
Private Sub CreateSPBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) sql = "CREATE PROCEDURE myProc AS" + " SELECT myName, myAddress FROM myTable GO" ExecuteSQLStmt(sql) End Sub 'CreateSPBtn_Click
Listing 5. Creating a stored procedure programmatically.
Now I show you how to create views programmatically using CREATE VIEW SQL statement. As you can see from Listing 6, I create a view myView which is result of myName column rows from myTable.
Private Sub CreateViewBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) sql = "CREATE VIEW myView AS SELECT myName FROM myTable" ExecuteSQLStmt(sql) End Sub 'CreateViewBtn_Click
Listing 6. Creating a view using CREATE VIEW
The ALTER TABLE is a useful SQL statement if you need to change your database schema programmatically. The ALTER TABLE statement can be used to add and remove new columns to a table, changing column properties, data types and constraints. The Listing 7 show that I change the database schema of myTable by first change column data type range from 50 to 100 characters and by adding a new column newCol of TIMESTAMP type.
Private Sub AlterTableBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) sql = "ALTER TABLE MyTable ALTER COLUMN" + "myName CHAR(100) NOT NULL" ExecuteSQLStmt(sql) End Sub 'AlterTableBtn_Click
Listing 7. Using ALTER TABLE to change a database schema programmatically.
The new table schema looks like Table 2.
| Column Name |
Type |
Size |
Property |
| myId |
integer |
4 |
Primary Key |
| myName |
char |
50 |
Allow Null |
| myAddress |
char |
255 |
Allow Null |
| myBalance |
float |
8 |
Allow Null |
| newCol |
timestamp |
8 |
Allow Null |
Table 2. MyTable after ALTER TABLE
You can also create other database object such as index, rule, and users. The code listed in Listing 8 creates one rule and index on myTable.
Note: Create Index can only create an index if you don't have an index on a table. Otherwise you will get an error message.
Private Sub CreateOthersBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) sql = "CREATE UNIQUE CLUSTERED INDEX " + "myIdx ON myTable(myName)" ExecuteSQLStmt(sql) sql = "CREATE RULE myRule " + "AS @myBalance >= 32 AND @myBalance < 60" ExecuteSQLStmt(sql) End Sub 'CreateOthersBtn_Click
Listing 8. Creating rules and indexes using SQL statement.
The DROP TABLE command can be used to delete a table and its data permanently. The code listed in Listing 9 deletes myTable.
Private Sub DropTableBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim sql As String = "DROP TABLE MyTable" ExecuteSQLStmt(sql) ' End Sub 'DropTableBtn_Click
Listing 9. Deleting table using DROP TABLE.
Now next step is to view data from the table, view and stored procedure. The ViewDataBtn_Click method listed in Listing 10 shows the entire data from the table. The ViewSPBtn_Click and ViewViewBtn_Click methods view stored procedure and view data we have created earlier. As you can see using views and stored procedures work same as you use a SQL Statement. We have discussed working with Views and stored procedures in the beginning of this chapter. As you can see from Listing 10, 11, and 12, I view data from stored procedure and view.
Private Sub ViewDataBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) '/ Open the connection If conn.State = ConnectionState.Open Then conn.Close() End If ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=localhost;" conn.ConnectionString = ConnectionString conn.Open() ' Create a data adapter Dim da As New SqlDataAdapter("SELECT * FROM myTable", conn) ' Create DataSet, fill it and view in data grid Dim ds As New DataSet("myTable") da.Fill(ds, "myTable") dataGrid1.DataSource = ds.Tables("myTable").DefaultView End Sub 'ViewDataBtn_Click
Listing 10. Viewing data from a database table.
Private Sub ViewSPBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) '/ Open the connection If conn.State = ConnectionState.Open Then conn.Close() End If ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=localhost;" conn.ConnectionString = ConnectionString conn.Open() ' Create a data adapter Dim da As New SqlDataAdapter("myProc", conn) ' Create DataSet, fill it and view in data grid Dim ds As New DataSet("SP") da.Fill(ds, "SP") dataGrid1.DataSource = ds.DefaultViewManager End Sub 'ViewSPBtn_Click
Listing 11.Using a stored procedure to view data from a table.
Private Sub ViewViewBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) ' Open the connection If conn.State = ConnectionState.Open Then conn.Close() End If ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=localhost;" conn.ConnectionString = ConnectionString conn.Open() ' Create a data adapter Dim da As New SqlDataAdapter("SELECT * FROM myView", conn) ' Create DataSet, fill it and view in data grid Dim ds As New DataSet da.Fill(ds) dataGrid1.DataSource = ds.DefaultViewManager End Sub 'ViewViewBtn_Click
Listing 12.Using a view to view data from a table.
Finally, I create AppExit method which releases the connection and reader objects and I call them from the Dispose method as you can see in Listing 13.
Protected Overrides Sub Dispose(ByVal disposing As Boolean) AppExit() If disposing Then If Not (components Is Nothing) Then components.Dispose() End If End If MyBase.Dispose(disposing) End Sub 'Dispose ' Called when you are done with the applicaton ' Or from Close button Private Sub AppExit() If Not (reader Is Nothing) Then reader.Close() End If If conn.State = ConnectionState.Open Then conn.Close() End If End Sub 'AppExit
Listing 13. AppExit method
Summary
In this article, you saw how to create a new database and database objects including tables, stored procedures, views and alter tables. You also saw how to delete these object using SQL statements.
Added by bradley on 1/30/2008
In using this code with 2005 .net adn sqlexpress I had to change a few things for it to work: 1. Import system.data.sqlclient 2. Change 'localhost' in all the strings to '.\sqlserver' 3. in the 'ExecuteSQLStmt' routine, delete the two lines below: ConnectionString = "Integrated security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=.\SqlExpress;" conn.ConnectionString = ConnectionString Also, if you have Management studio express running, the database will not show up until you hit 'refresh'. Hope this helps someone!
|
|
|
Login
to add your contents and source code to this article
|
|
|
|
|
|
|
|
Mahesh Chand
Mahesh is a software developer with over 13 years of experience building systems for Financial and Banking, Engineering & Architectural, Imaging, Construction, Biological & Pharmaceuticals, Healthcare and Education industries. His expertise is Windows Forms, ASP.NET, Silverlight, WPF, WCF, Visual Studio 2010, SQL Server, and Oracle. If you are looking for a Sharepoint, Windows Forms, ASP.NET, WPF, Silverlight, C#, VB.NET, Oracle, and SQL Server Consultant in Philadelphia area or remote location, drop me a line at MAHESH [AT] C-SHARPCORNER [DOT] COM.
|
|
|
|
|
|
|
|
|
C# Consulting is founded in 2002 by the founders of C# Corner. Unlike a traditional
consulting company, our consultants are well-known experts in .NET and many of them
are MVPs, authors, and trainers. We specialize in Microsoft .NET development and
utilize Agile Development and Extreme Programming practices to provide fast pace
quick turnaround results. Our software development model is a mix of Agile Development,
traditional SDLC, and Waterfall models.
|
|
Click here to learn more about C# Consulting. |
|
|
|
|
|
|
|
Introducing MaxV - one click. infinite control. Hyper-V Hosting from MaximumASP.
Finally – a virtual platform that delivers next-generation Windows Server 2008 Hyper-V virtualization technology from a managed hosting partner you can truly depend on. Visit www.maximumasp.com/max for a FREE 30 day trial. Hurry offer ends soon.
Climb aboard the MaxV platform and take advantage of High Availability, Intelligent Monitoring, Recurrent Backups, and Scalability – with no hassle or hidden fees.
As a managed hosting partner focused solely on Microsoft technologies since 2000, MaximumASP is uniquely qualified to provide the superior support that our business is built on. Unparalleled expertise with Microsoft technologies lead to working directly with Microsoft as first to offer IIS 7 and SQL 2008 betas in a hosted environment; partnering in the Go Live Program for Hyper-V; and product co-launches built on WS 2008 with Hyper-V technology.
|
Dynamic PDF
ceTE software specializes in components for dynamic PDF generation and manipulation. The DynamicPDF™ product line allows you to dynamically generate PDF documents, merge PDF documents and new content to existing PDF documents from within your applications.
|
Go.NET
Build custom interactive diagrams, network, workflow editors, flowcharts, or software design tools. Includes many predefined kinds of nodes, links, and basic shapes. Supports layers, scrolling, zooming, selection, drag-and-drop, clipboard, in-place editing, tooltips, grids, printing, overview window, palette. 100% implemented in C# as a managed .NET Control. Document/View/Tool architecture with many properties&events. Optional automatic layout.
|
Dundas Software
Dundas Chart for .NET is the most advanced .NET charting package available today. With an extremely complete feature set, elegant architecture and easy implementation, Dundas Chart can quickly add advanced Charting functionality to enhance and transform ASP.NET and Windows Forms applications. Whether you are implementing charting into internal projects, or building applications for clients, Dundas Chart offers advanced technology and advanced results to get the most out of data.
|
60 FREE UI Controls from DevExpress
Register for your FREE copy on over 60 free presentation controls from
DevExpress - Absolutely Free-of-Charge without any royalties or distribution
costs. Visit Devexpress.com/60 today. Free controls include advanced lists box, dropdown calendar, rich text edit, spin
edit, tab control and so much more!
DevExpress engineers feature rich presentation controls and reporting tools for WinForms, ASP.NET, WPF, and Silverlight. Our technologies help you build your best, see complex software with greater clarity and deliver compelling business solutions for Windows and the web in the shortest possible time.
|
Clickatell's SMS Gateway
Clickatell's Developer Solutions allow you to SMS enable any website or
application via a range of API's. Learn More about our API connections.
|
Microsoft Visual Studio 2010
Visualize your workspace with new multiple monitor support, powerful Web development, new SharePoint support with tons of templates and Web parts, and more accurate targeting of any version of the .NET Framework. Get set to unleash your creativity.
|
Nevron Chart for .NET 2010.1 Now Available
The leading .NET charting control now features PDF, Flash and Silverlight export, visualization of large datasets and more. Deliver true charting functionality to your BI, Scorecard, Presentation or Scientific apps. Download evaluation now.
|
Developer-Ready ASP.NET 2.0 Web Hosting with 3 MONTHS FREE
Now supporting .NET 3.0 Framework with Windows Workflow Foundation, Windows Communication Foundation (WCF), Windows Presentation Foundation (WPF), windows CardSpace (WCS)! Providing more flexibility for Developers with Web Services Support and a User/Permission Manger. Also supporting MS SQL 2005/2000 with Real-Time Backups, FREE Automated Attach .MDF Tool, FREE SQL Restore and Shrink SQL DB Tools, and SQL
|
|
|
|
|
|
|
|
|
|
|
|
|