SQLDataSource in asp.net
Within asp.net and Visual Studio there are many controls where we write a minimum amount of code but rather we specify what we want declaritiveley and the control takes care of all of the coding and work for us.
These kinds of controls are more and more common as new versions of the framework and VS appear. They tend to not be used by professional developers since they do not lend themselves easily to tiered architectures. However they do have their place such as when used by less able developers, or amateurs building a personal website. Even professionals can use them for knocking together quick test pages or prototypes when their high score in the RAD factor does show it's benefits.
So with this in mind lets have a quick look at how we can use a SqlDataSource object
First create a new project and add a web form or use the default one which is added for you.
Now show the form in design view and then click on the Toolbox. Expand the Data Section and amongst all of the controls you will see a SqlDataSource control. Drag one of these onto the form. This is a non visual control - ie you won't see any visual representation of this control when the form renders itself but you can see it in design view and click on it and set properties etc.
Click on the arrow at the top right of the control in the form design view and then click on configure data source. This will start a wizard which will ask us a series of questions to quickly and painlessley configure the data source.
First we will click on the new connection button and then choose our SQL server instance from the drop down list of server names.
If you don not have SQL Server installed, you can get a free version of SQL Express from Microsoft at www.microsoft.com/Sqlserver/2005/en/us/express.aspx
Once you have chosen your server you should select the appropriate Logon method. You should really be using SQL authentication so select that and enter the username and account which you have setup to access your database. Setting up logons for SQL Server is beyond the scope of this article but you can look here to read up on this - http://msdn.microsoft.com/en-us/library/aa337562.aspx
You will also need to make sure this logon has access to whichever database you are using. Again this is outside the scope of this article but you can read up on this at http://www.exforsys.com/tutorials/sql-server-2005/sql-server-permissions.html
Once you have entered your username and password, make sure you tick Save my password. You should also select the database you wish to use from the drop down list. You can then confirm everything is working by clicking the Test Connection button. Assuming everything works ok we can proceed so click Ok and then click next.
You can leave the next screen as it is which will save our connection string in the web.config file. Do feel free to change the name of the connection string though to something more meaningful and then click next.
The next part of the wizard is asking us how we want to retrieve data from our database. Keeping to the theme of minimum coding we will choose a table from the drop down list and then tick on the columns you wish to select from that table. You may also tinker with the Order by caluse or the where caluse and even the Advanced settings but do that another time - lets just keep it simple for now. So after having ticked the columns you want, click the next button. On this screen you can click Test Query to check that everything is working as expected. If not click previous and fix any issues and then come back to this screen.
Finally click finish and we are done. If you now switch to source view in Visual studio we can see what has been generated as below:
All that remains is to show this data on screen so for that lets drag a GridView control onto our form. You can then click on the arrow at the top right of the GridView and choose our Data Source from the drop down menu. And that's it we are done so we can run the app and see our results!
So very quick and easy and very useful in the right scenario - prototypes for example would be very quick to develop this way.
Any questions or comments are welcome and can be submitted at the bottom of the page.