How to Execute SQL Script From Visual Basicby Paul Knorr
The latest version of Visual Basic provides a rich data access layer that allows for object-oriented abstraction of the data used in an application. Sometimes however, it is more efficient, quicker or more practical to simply execute a SQL script against the underlying database directly. A SQL script may contain multiple statements that need to be executed together or it may have dynamic SQL that is generated by the application. Regardless of the reason, executing SQL scripts from within Visual Basic is not very difficult.
Create a new Visual Basic Windows Forms project in Visual Studio by selecting New Project from the File menu and selecting the appropriate project type. In the default form that is created, form1, place a button control named Button1. Double click on the control to create a Button1_click event handler.
At the top of the code listing for Form1 (above the line "Public Class Form1"), add an imports statement to import the .NET data access library. The statement should look like this: Imports System.Data.SqlClient In this example, the database is assumed to be a SQL Server database. There are other libraries for ODBC and OleDB databases.
Within the Button1_Click event handler function, dimension a variable named db as a new SqlConnection. Set the connection string property to be the connection string for the database and open the database connection with the Open method. The code should look like this: Dim db As New SqlConnection db.ConnectionString = "Data Source=servername; Initial Catalog=dbname; User Id=user; Password=password" db.Open()
Dimension a variable named cmd as a new SqlCommand. Set the CommandText property to be the SQL script that is to be executed. Finally, call the ExecuteNonQuery method of the SqlCommand object to execute the SQL script. The code should look like this: Dim cmd As New SqlCommand cmd.Connection = db cmd.CommandText = "update tablename set field1 = 2 where field3 = 4" cmd.ExecuteNonQuery()
- check The return value of the ExecuteNonQuery method gives the number of records that were modified. For example, if an update statement was executed, the return would be the number of records updated. This can be useful in the user interface to provide feedback or it can be used to verify that records were found.
- check After the database connection is opened, it should always be closed using the Close method of the SqlConnection object. This will help prevent corruption of the database, especially for Microsoft Access database files.
Items you will need
- photo_camera laptop with database record on 15.4" wide screen image by .shock from Fotolia.com
Click here to provide feedback on this article