Getting at data within a .NET application can be straightforward using built in controls or much more complex through connections, commands and data readers to name but a few. This article is a short introduction to each of the available options.
1. Using a SQLDataSource
The built-in .NET controls for accessing data are easy to use when working with a single table within a database. Using the wizard interface it is quick to build simple SELECT statements and preview results.
Provided the table you are querying has a primary key defined (which is included in your query) then you can also automatically generate INSERT, UPDATE and DELETE queries using the Advanced button. The SQLDataSource control even takes care of concurrency conflicts.
Attaching this to a GridView and/or a DetailsView makes displaying data and making simple additions, updates and deleting rows really quick and easy. The downside is that it’s one table at a time so this only works for simple applications.
2. Writing code within the logic tier
Although not advisable, or indeed best practice, it is possible to execute SQL statements within the code-behind of an ASPX page. In the past this would have been the only way to access data through development tools like Classic ASP and is still prevalent in widely used web languages such as PHP. Please note, when writing SQL statements directly within code it is important that developers account for security vulnerabilities such as SQL Injection.
Sometimes, despite the security risks, it is just easier to put a simple SQL statement straight into code.
SELECT * FROM Movies hardly warrants the potential effort of contacting the DBA and having stored procedures created. So to execute this, and any other SQL statement from code follow the basic principles below:
- Make a connection to the database using a SQLConnection and a ConnectionString stored in your Web.config file.
<connectionStrings> <add name="MoviesConnectionString" connectionString="Data Source=serverName;Initial Catalog=Movies;Persist Security Info=True;User ID=userName;Password=password" providerName="System.Data.SqlClient" /> </connectionStrings>Dim conn As New System.Data.SqlClient.SqlConnection
conn.ConnectionString = ConfigurationManager.ConnectionStrings("MoviesConnectionString").ConnectionString - Create a new SQL Command that uses the connection to connect to the database and execute the SQL statement.
Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.Connection = conn
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = "SELECT * FROM Movies"
Try
' Open the connection to the database
conn.Open()
' Set up a SqlDataReader to read the results from the query
Dim r As System.Data.SqlClient.SqlDataReader
' Populate the SqlDataReader
r = cmd.ExecuteReader
r.Read()
' Send the SqlDataReader back to the requesting method
Return r
Catch ex As Exception
' Deal with your error
Return False
Finally
' Always close the connection to the database
conn.Close()
End Try
- Bind the returned dataset to the control to render the results
GridView1.DataSource = getMovies()
Where getMovies is a function that returns the SqlDataReader above.
There is clearly a lot more code involved with this compared to using a SQLDataSource control but even though it’s not shown in this example this method allows for far greater flexibility including writing joins within the SQL query to return related data. Just remember to be vigilant for SQL Injection attacks and try not to get caught out with the apostrophe in names such as O’Neil which would break a SQL query and cause many hours of debugging when everything works otherwise!
3. Using Stored Procedures in the data tier
Using Stored Procedures gives benefits in terms of security, speed and ease of maintenance as the database schema changes. There is very little difference with this and the second option when it comes to actual coding.
A full example is:
Dim conn As New System.Data.SqlClient.SqlConnection
conn.ConnectionString = ConfigurationManager.ConnectionStrings("MoviesConnectionString").ConnectionString
Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.Connection = conn
cmd.CommandType = System.Data.CommandType.StoredProcedure
cmd.CommandText = "StoredProcedureName"
Try
' Open the connection to the database
conn.Open()
' Set up a SqlDataReader to read the results from the query
Dim r As System.Data.SqlClient.SqlDataReader
' Populate the SqlDataReader
r = cmd.ExecuteReader
r.Read()
' Send the SqlDataReader back to the requesting method
Return r
Catch ex As Exception
' Deal with your error
Return False
Finally
' Always close the connection to the database
conn.Close()
End Try
Apart from the extra effort required of creating a Stored Procedure and the fact that changes may have to be made by a DBA there isn’t really a good reason to not do this. Leveraging the power of Stored Procedures within your application can allow for extremely complex querying of data which is returned securely and quickly as the processing sits on the SQL Server and doesn’t compete with the Web Server.
Each of the three methods can be used for SELECT, INSERT, UPDATE and DELETE SQL queries and they all have their own merits. When all the application requires is to work with a single table there is no reason not to use .Net controls like a SQLDataSource and when something more complex is required let the DBA experts do all the hard work!
