Visual Studio: SqlDatabaseSource -v- NHiberate/Mapping

Gavin "shels"

In the middle of a college website project and have a question. Basically on the site there's a quiz, which one a user has finished it will enter their details (along with the recorded score) which will be saved to an SQL Database. Atm I'm using just the bulit in SqlDatabaseSource which is working fine but is there any advantage to using NHiberate and Mapping?

Cheers in advance.

Aswerty

For a small project like yours there would be no benefits to using an Object Relationship Mapper (ORM) such as NHibernate. The benefits of ORM's are to speed up the development of the persistence layer and to allow developers to interact with objects as opposed to SQL. The expectation typically is that when using an ORM you should already have a solid understanding of SQL since that is what the ORM is generating for you.

If you have gotten to a point where you are satisfied that you are competent in SQL and want to learn how to use NHibernate that is fair enough. ORM tools in my experience slow developers down in the short run while they get familiar with them but can seriously speed things up when this learning curve is over. Not something I have experience in but in large and/or old projects people say that ORM's increase complexity in comparison to SQL. This is mainly down to lack of familiarity with the framework or limitations in the framework.

I'm not a fan of SQL so I'm on the ORM bandwagon although I do recognise there is a number of down sides to using them.

Aswerty

Also if you decide to use NHibernate I strongly advise you to download the 1 month trial of NHibernate Profiler. This will give you a good understanding of the SQL queries being generated by NHibernate.

mewso

While I agree with most of what Aswerty is saying I would recommend anyone trying to learn this stuff to stay away from the wizard driven stuff like sqldatasource and so on. Write your own ado.net code and get familiar with it.

When you start working on more complex applications read up on Domain Driven Design and then Nhibernate/Entity Framework and so on as they make most sense in terms of mapping a model to a database allowing you to concentrate on modelling your application without wasting too much time worrying about how it will fit into a relational database structure.

Many people start applications by creating a database to fit a model they have in mind then develop a web app around that. ORMs are for the time when you make the switch to creating objects first and database later.

Gavin "shels"

Cheers. Is it true that ORM is more secure than the SqlDatabaseSource?

mewso

Gavin shels wrote: »

Cheers. Is it true that ORM is more secure than the SqlDatabaseSource?

Not ORMs exclusively. Anything beyond a sqldatasource will be more secure simply because it isn't declaring sql code and parameter names in the actual .aspx page but general security issues like protecting against injection would be handled specifically by using parameters for example regardless of what data access method you are using. As the developer you can chose not to use paramaterised queries with any of these options so the security risk stems from your decisions rather than a sqldatasource or nhibernate.

Gavin "shels"

Excellent. How would I go about making it secure using the parameters?

Basically we've learned ORM briefly in college, and our lecturer/supervisor for our 3rd year project is saying he'll deduct marks since we're using SqlDatabaseSource and not ORM, but his main (and really only reason) was that ORM is more secure.

mewso

Well I recommend you talk to your lecturer. If he is referring to using parameters to protect against injection then you can do that in the sqldatasource wizard. It's a long time since I used the sqldatasource but it may even use parameters by default so it sounds like he simply wants you to use an orm because it's better practice. Have a read up on entity framework if you want something relatively quick as you can use wizards to generate the ef stuff from the db and it will be superior to the sqldatasource. Entity Framework is Microsoft's ORM solution and these days it's a lot better than it started out. Not quite up to nhibernate's standard but getting there. You could also look at some of the micro orms out there for a very light way of implementing it - dapper, petapoco, massive etc. In fact for a simple app micro orm is probably a good solution.

Gavin "shels"

Just a quick question on this, what type of parameters would I use?

mewso

Gavin shels wrote: »

Just a quick question on this, what type of parameters would I use?

Have you done any searches on parameters in ado.net? They are dependent on the datatype of the relevant columns in the database table. Short of describing how to execute paramterised queries using ado.net I'm not sure what you are asking.

Procasinator

If you want a half-way house between SQL and ORM, you could use something like Dapper.

It takes in your SQL, with parameters, and can if you choose return a strongly typed list. This will be quicker for you to port too, as you should be able to re-use your SQL.

Of course, this might no please your lecture if he wants to see the query side handled purely by the ORM. On a security front however, you can argue that by using parameters you are offering similar protection to that provided by an ORM.

Gavin "shels"

mewso wrote: »

Have you done any searches on parameters in ado.net? They are dependent on the datatype of the relevant columns in the database table. Short of describing how to execute paramterised queries using ado.net I'm not sure what you are asking.

I read the below but I'm still a bit confused as to what way parameters work in the SqlDatabaseSource.

http://www.csharpuniversity.com/2009/05/29/how-to-use-adonet-with-parameters-in-aspnet-to-insert-a-record-into-a-sql-server-database/

Procasinator wrote: »

If you want a half-way house between SQL and ORM, you could use something like Dapper.

It takes in your SQL, with parameters, and can if you choose return a strongly typed list. This will be quicker for you to port too, as you should be able to re-use your SQL.

Of course, this might no please your lecture if he wants to see the query side handled purely by the ORM. On a security front however, you can argue that by using parameters you are offering similar protection to that provided by an ORM.

Cheers.

mewso

Gavin shels wrote: »

I read the below but I'm still a bit confused as to what way parameters work in the SqlDatabaseSource.

SqlDataSource simply does it in the background for you. Again as I said it's been a while since I used it but if you look at the html source of your page you'll see the sqldatasource element and it will have obvious properties set like the select statement and so on. If the select statement has 1 or more "?" in it and then below it has parameter elements it means that it has been setup to use sql parameters so in the background when you run the page it will do the ado.net stuff, creating parameters etc.

Gavin "shels"

Cheers man, think I get it, will have a crack at it later when I'm home from work.

Procasinator

It's fairly simple.

Say I have a table Person:
Name nvarchar
DateOfBirth datetime
FavouriteNumber int


If you wanted to insert into this table, you write SQL like:

INSERT INTO Person (Name, DateOfBirth, FavouriteNumber) VALUES (@Name, @DateOfBirth, @FavouriteNumber)

The @X are the parameters we need to pass in.

To do this, we create for each. For instance:

SqlCommand dbcmd = new SqlCommand(); 
// Our query
dbcmd.Text  = "INSERT INTO Person (Name, DateOfBirth, FavouriteNumber) VALUES (@Name, @DateOfBirth, @FavouriteNumber)";
 
// Create a parameter type and we'll fill in the data for DateOfBirth
SqlParameter  dbparam = dbcmd.CreateParameter();
// This is the name of our parameter we are passing in, derived from our query
dbparam.ParameterName = "DateOfBirth";
// This if the datatype being set
dbparam.DbType = System.Data.DbType.DateTime;
// And we are passing in DateTime.Now - let's hope they picked there favourite number already.
dbparam.Value = DateTime.Now;

You would repeat this for your other to parameters, then execute the query.

You could manually concatenate strings (advice: don't) to generate your query, but you are likely to expose yourself to security issues (such as SQL injection) and have to write helpers to format different data types correctly.

Gavin "shels"

Cheers Procasinator, I'm a little confused as to what the parameters are actually doing and how are they making the SQL more secure?

mewso

It's to do with sql injection. If your user enters something into an input on your form like "''; delete from table1" and you don't use parameters just append what they have entered to your sql then all rows in your table will be deleted:-

"select a, b, c from table where name = " & nameInput.Text

will result in a sql statement like this:-

select a, b, c from table where name = ''; delete from table1

Using parameters avoids this completely.