using a local database in ADO.NET vs. using a SQL server

[Sadus]

Hi,

I'm just getting started with database programming though I've studied the basics of SQL already.. Nearly all of the ADO.NET examples I've read on MSDN etc all use the Northwind database, using Microsoft SQL server (I think). The connection object is always similar to this:

Dim con As New SqlConnection("server=localhost;integrated security=true;database=northwind&quot

I was just wondering what modifications I need to make to work with a local database, when I don't have a SQL server of any sort installed? Or do I *have* to be running a SQL server of some type (Microsoft SQL server or a free SQL server or whatever) to use ADO.NET?

I'm stuck at work all day where I do a lot of programming reading in my large amount of spare time... I am going to do some tests at home this evening when I get off work, but I was just hoping to find an answer in advance. For all I know the SqlConnection string above would work fine since it says server=localhost... (though of course I'd change the database=xxx to the right database name)

I can just go into Control Panel / Admin Tools / Data Sources (ODBC) and add a new "User DSN" right? If I select "microsoft access driver (*.mdb)" and on the next screen select my local .mdb file as the source and give it whatever name I want to use instead of "northwind"... That will work even without having a SQL Server installed right? But that would use the Jet database engine which if I remember correctly is a bit slower than some of the other alternatives... But without having a SQL server installed I can't see any other better choices to use when setting up the DSN.

What about when my application (e.g. an ASP.NET app) is ready for deployment on a professional server? My host would just have to set up their own DSN using the same name as my local DSN and the application would work ok? If I then wanted to switch the database to use some kind of SQL driver instead of the Microsoft Access .mdb driver that wouldn't break my existing code since ADO.NET works the same with the different database drivers, right?

Apologies if my questions are a bit strange, or if I keep answering my own questions... It's just that everything I've read always seem to assume you are running M$ SQL Server and don't really talk about using a local database, let alone how you can go from a local database during development to a better performing database at deployment.

Thanks!
Jeremy

 

[sunaj]

Hi,

You can connect to almost any datasource with ADO.net. So, no, you do not need to have an SQL server, you can use any other relational database or even a flat file or excel document if you like.
Neither do you need a DSN (but you can use one of you like). Usually it is easier and faster without a DSN (depending on your datasource though).
See e.g.

http://www.able-consulting.com/ADO_Conn.htm

for examples of different connection strings.

Note that VB.NET has a special namespace for SQL server (which does not use a provider), so if you use SQL server this is the most efficent - but if you want to swith between different datasources you might be better off by using a provider and thereby making you ADO code generic.

Hope that helped a bit.

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'