Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Developing with SQL Server: Three ways to get at data

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.

image

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.

image

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:

  1. 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

     

  2. 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

     

  3. 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!

Comments

Posted by Steve Knox on 7 April 2011

The SQL data source wizard in (1) allows you to write SQL commands with joins, or to access stored procedures, as well. Just select the "Specify a custom SQL statement or stored procedure" radio button as shown in your first screenshot. So it has the flexibility of (2) and (3) built-in.

Posted by petrisor.dumitru on 7 April 2011

What about LinqToSql ?

Posted by Justin Hostettler-Davies on 7 April 2011

Cheers for the comments both - a colleague of mine Nick Allport actually wrote this article, and its hoped he'll be able to provide more of a developers view. nickallport.wordpress.com

sknox - Nick is intending to do another article to do a deep dive into the SQL Data Source and look at joins and stored procedures.

petriso.dumitru - Another article is lined up for LINQ to SQL. This was just an overview of three options.

Posted by Matt Penner on 7 April 2011

All of these are fairly standard and long in practice, but for developers the world has opened up quite a bit.

I would recommend people look into what's going on in the Linq2SQL front as well as Entity Framework 4.  Both are enjoying growing adoption and respect among SQL admins and developers.  

I haven't used data readers in the last few years (except one extreme case).  In my personal experience my production has been much faster and has produced more stable/secure code that is much more flexible to change.  Changing a simple column name (which I do a lot in initial development) is a simple property change in EF4 rather than having to touch a mound of SQL with no compile time checking.

Leave a Comment

Please register or log in to leave a comment.