ASP.NET MVC 2 - user defined database connection.

Question

Hello,

I am looking to port my very basic DBMS software from classic ASP to ASP.net - however the user would need to input the connection details in order to connect to their specific DBMS server.

Is this at all possible with ASP.NET MVC (very similar to DSN-less connections in ASP).

Cheers, Joel

Answer 1

The question should really be "is this possible with .NET", ASP.NET MVC is not a database technology, and DSN-less connections aren't ASP technology either. In .NET, it is the ADO.NET framework that allows you to access database resources, and it can be used from any .NET code, be it desktop, web and mobile too.

There are some specialised libraries for certain platforms, .NET includes native support for Sql Server, you can get the MySql Connector for .NET, etc.

All of these providers are built around the ADO.NET provider model, you can either use them explicitly, or you can use the provider-agnostic method. Here are two examples, the first being Sql Server:

string connectionString = "Server=....";
using (SqlConnection connection = new SqlConnection(connectionString))
{
  using (SqlCommand command = new SqlCommand("SELECT [Name] FROM [People]")) 
  {
    connection.Open();

    using (SqlDataReader reader = command.ExecuteReader()) 
    {
      // Do something here.
    }
  }
}

In the above example, I'm using the specific Sql Server ADO.NET types to create a connection to a database and execute an arbitrary query against it.

If you are intended to support multiple database platforms, it's probably best to design your code such that it can utilise the ADO.NET Factory classes which are specialised factories geared to the creation of platform specific types. In the example below, I've used the Factory classes to access a MySql Server database:

string connectionString = "Server=....";
DbProviderFactory factory = DbProviderFactories.GetFactory("MySql.Data");
using (DbConnection connection = factory.CreateConnection()) 
{
  connection.ConnectionString = connectionString;

  using (DbCommand command = factory.CreateCommand())
  {
    command.CommandText = "SELECT `Name` FROM Page";

    connection.Open();

    using (DbDataReader reader = command.ExecuteReader())
    {
      // Do something here.
    }
  }
}

Not the perfect example, but enough to get you going, but it's important to remember that DSN-less connections are not tied to ASP or ASP.NET.

Hope that helps.