How to Execute a SQL Script or Query From an ASP.NET Website

SQL (Structured Query Language) is a standard interactive and programming language for querying and modifying data and managing databases. Many might pronounce “SQL” as SEQUEL.

In this article I will cover how to create connection with your database and how to execute / run the SQL script from within your ASP.NET code.

Let’s create a simple Microsoft Access database.

Step 1. Open up Microsoft Access

Step 2. Select ‘New’ from ‘File’ menu

Step 3. Create a blank database and name it sampleDB.mdb (see ‘DB Screen Shot’)

Step 4. Double click on ‘Create table in design view’ to create your sample “Customer” table.

Step 5. Create fldID, fldName, fldAddress, fldPhone, fldActive fields. (See ‘Table Columns’ screen shot)

Step 6. Save the table name name it tblCustomer

Step 7. Now let’s add few customers to our tblCustomer table. (See ‘Table Data’ screen shot)

Now you have your first Access database “sampleDB.mdb”. Move this database file to your “App_Data” folder in your ASP.NET website project. Moving a database file in App_Data folder is much better than keeping it in any other folders. ASP.NETrecognizes the App_Data folder without specifying any path. If you have your database file in any other folder, then you would have to specify the path which is not always the best option.

Let’s set a connection string variable for the Access database file you created and execute and run a SQL script against the table called 'tblCustomer'.

Open up your page, any page that needs to execute SQL script, and enter the below code (without line numbers) within a button click event or page_load event.

For this example, I’m using Default.aspx and Default.aspx.cs. Default.aspx is similar to an HTML page and Default.aspx.cs is its code behind page.

Open up Default.aspx (HTML) and add two labels, one to show status and another to show all of the customers.

<asp:Label ID="lblStatus" runat="server"></asp:Label>

<br /><br />

<asp:Label ID="lblCustomer" runat="server"></asp:Label>

Please note I have used ID=”lblStatus” and ID=”lblCustomer”. This is how the code behind page (Default.aspx.cs) will be calling these controls. IDs are same as an object’s name.