ASP.NET 4 SQL Methods?

Question

Hi,

Currently working on an ASP.NET web app and am starting to realise i'm writing the same Boiler Plate Code Over and Over Again..

i.e.

        SqlDataReader myReader = null;
        SqlCommand SQLcmd = new SqlCommand();
        SqlConnection SQLConn = new SqlConnection();
        String SQLStatement = "Select this 'nd That";
        SQLConn.ConnectionString = SQLConnectionString;
        SQLConn.Open();
        SQLcmd.Connection = SQLConn;
        SQLcmd.CommandText = SQLStatement;
        myReader = SQLcmd.ExecuteReader();
        while (myReader.Read())
        {
            //Do some awesome
        }
        SQLConn.Close();
    }

How is the .NET world playing with SQL now?

Basically how can i not spend half the day copying and pasting that code?

Is everyone using Linq to SQL? and if so please point me to a tutorial!

Thanks a lot!

Daniel

Answer 1

The general mood these days is to use some sort of ORM.

Popular choices are:

• nHibernate
• Entity Framework

Linq2Sql is indeed an ORM, but it is no longer being actively developed within Microsoft.

Answer 2

There is no "one" way. Some people use Linq to sql, some use Entity Framework, some use nHibernate, some use Enterprise Library data blocks, etc... it all depends on your needs and what you feel comfortable with.

Answer 3

Use an ORM. To find out more about ORM, visit http://en.wikipedia.org/wiki/Object-relational_mapping. Some choices for .Net are:

• NHibernate: Most powerful one but has a deep learning curve. Free and open source.
• Entity Framework: EF1 was not very promising But it seems like Microsoft is investing heavily on it since EF4 is very more powerful. Since you mentioned ASP.Net 4, I suggest you take a look at EF4.
• SubSonic Has a fast learning curve. Easy to use. Best for small-medium applications IMO. Free and open source.

The most famous commercial ORMs are Active Record, LLBLGen (Which has its own ORM and also is an OR-Mapping generator), DevForce (Free for databases with less than 10 tables)
You might want to take a look at this blog post to see a list of most famous ORMs available.

Answer 4

Instead of telling you to use an ORM, I'm going to show you how I do it (typed from memory) (and I'm going to be downmodded and flamebaited and who knows what else):

I see you do what I do, which is namely:

    SqlDataReader myReader = null;
    SqlCommand SQLcmd = new SqlCommand();
    SqlConnection SQLConn = new SqlConnection();
    String SQLStatement = "Select this 'nd That";
    SQLConn.ConnectionString = SQLConnectionString;
    SQLConn.Open();
    SQLcmd.Connection = SQLConn;
    SQLcmd.CommandText = SQLStatement;
    myReader = SQLcmd.ExecuteReader();
    while (myReader.Read())
    {
        //Do some awesome
    }
    SQLConn.Close();
}

And so I do something similar. Notice that you're creating new SqlCommand, new SqlConnection, and you're using a common SqlConnectionString (which you need to be able to reload from a central place, yah?) so I do this:

public class Global{
  public string ConnectionString {get;set;} 
  public Global(){
    ConnectionString = //load from wherever, this allows me to reload it later, via API? ;)
  }
  public SqlCommandFactory(string sprocName, SqlConnection con){
    return new SqlCommand{
      CommandText = sprocName,
      Connection = con,
      CommandTimeout = 0,
      ConnectionTimeout = 0,
      CommandType = StoredProcedure
    };
  }
}

//in my other class that uses this code:
public List<string> /* for simplicity sake, strigns */ GetListOfStringsFromDatabase(){
  List<string> returnValue = new List<string>();

  // use of using ensures that I don't forget to clean something up here!!!!
  using ( SqlConnection con = new SqlConnection(Global.ConnectionString) ) {
    SqlCommand cmd = Global.SqlCommandFactory("mysproc", con);
    cmd.Parameters.Add( "@param1", SqlDbType.VarChar ).Value = "somestring";

    con.Open();
    using ( SqlDataReader reader = cmd.ExecuteReader() ) {
      while (reader.Read()) {
        returnResult.Add( reader[0].ToString() );
      }
    }
  }

return returnValue;
}

But the ORM would probably be better. Just not something that we feel works well for our situation.