I’ve been working with .net and C# for a while now, but any time I’ve had to deal with databases (mostly SQL server) all I ever had to do was some basic data modeling, connecting to an existing database on a server, and finally setting up LINQ.
That’s served me well so far, but now I need to work on a project that:
Needs to use an sql database to work with data.
The database does not exist and cannot be deployed until the project is complete.
The database will be hosted on a machine at the office that is not being used and is connected to the LAN. The program I’m writing is essentially a client that will connect and browse the database (with a neat GUI). Several users on the network will be running these clients at the same time.
I can develop the project with a local database on my work machine without issues. But since now I’m in charge of doing everything I have to deploy the database on a machine not being used and connected to the LAN, and make sure that every client can browse that database. How do I do this?
I can’t just create a database in my project, as it will be deployed along with my application and every client will be working with it’s own version of the database stored and running locally (I believe). The database does not exist yet, so I can’t connect to it and work on my solution in a way that will allow the final deployed clients to communicate with it. Damn it, I’m going to need to pick up a book aren’t I?
I also thought about writing a server application that sits on the database and uses TCP to communicate with the clients. But time is limited and I’d rather avoid going that route.
Most remote database connections I know of use ODBC connectors.
That way, the ODBC connection string is a text in the application.
For testing, you can use a connector string to get to your local DB.
For production, you swap that string for one that connects to the production database.
The downside is that the text includes the password. IIRC, the guy who was doing a web site once showed me a trick to transform the password, but that was specific to that release of the program; needed to be redone with each compile?
An alternative trick was to use a C# call to obtain the userid and pass it as one of the parameters after the connection is established, thus determining the user’s role in the database - update or read only, etc. You only run stored procedures, which first check the user’s rights before executing the SQL call.
You can create a database with a script; or copy the empty database across. There’s even a way to generate a “make me a database” script from a database in SQL server, but it’s something else I forget how to do. Creates db, tables, keys, indexes, dependencies, etc.
If you can spend just a little bit of money, look at DevExpress’ ORM: XPOit will help you manage connections and will even create the database/tables for you if they aren’t there. It will allow you to connect to many different types of databases without needing to know much behind the scenes.
It is just a few hundred dollars and is a real time saver. There are free ORMs, and I looked at about 4 different ones, but this was easily the easiest to implement.
If you can spend even more money, get their whole suite of tools. The grid component alone will save you weeks of dev and hooks neatly into their ORM.
MS SQL Server DBA checking in. Develop the application with a database on your local machine. Then backup the database to a file, copy the file to the server, and restore the database on the server. The application’s connection string will need to contain the server name.
For MS SQL Server, Windows authentication is recommended. No passwords are required, access is granted to Windows accounts or better yet Windows groups. An alternative would be to use SQL authentication and embed a password in the application but this is much less secure.
Changing from one DB running on one machine to another DB running on another machine is as easy as changing the connection string in the web.config. If it’s not, you didn’t write the code correctly :D. But we know you wouldn’t do that, right?
.NET applications ordinarily use OLEDB, and there’s a separate set of OLEDB drivers optimized for SQL Server databases included in the .NET framework. The most important thing to remember for your purposes, assuming you follow AnalogSignal’s excellent advice, is to avoid specifying the connection string in your C# code each time you open a database connection. You can store it in web.config as he suggests, or have a public SQL Connection object defined in your project that’s shared by all sections of your code. The point is to be able to change that connection string in one place after you get the production database running & have the whole project running with the new connection.
Everything said above remains valid, but could I make a suggestion? In terms of architecture, it might be a lot easier to manage if you were to write this as a web application in ASPX (and your choice of C# or VB.NET) rather than having it as a desktop application which you have to install on your users’ machines.
The benefits this brings are:
[ul]
[li]Central management - nothing to install or upgrade on clients[/li][li]Less mucking about with configuration settings like connection strings; it’s all defined in one place[/li][li]Maintenance and troubleshooting becomes easier (no questions about which version of the client they are running, for example)[/li][li]You can still natively use C# or VB, with all the .NET libraries[/li][/ul]
I’m sure there are more benefits to doing it like this, with a few downsides, but I think there’s quite a compelling argument to doing it as a web app, especially for a LAN where latency and connectivity aren’t a problem. If you want to run your app in disconnected mode, that’s another story, of course.
The development tools are free if you use Visual Studio Express, and you can install a local copy of SQL Server Express to develop and test your database. When it’s in the shape you want, just back it up and restore it on your production server (no changes required to move a DB from SQL Server Express to SQL Server Standard), and publish your application there. You can even run your live app on SQL Server Express if you can live within the data and performance constraints of that edition.
I would also recommend a web app, using a single account for Windows Authentication to the database from the web app, for the best security. Then use individual logins internal to the application to do any user tracking, app permissions, etc.
Install the free SQL Server Express to do development, then install it to your server when it’s up and running and just change the config in your webapp to the new server.
It’s often easier to detach the database, copy the mdf & ldf files to the new server, and then attach them there, though. (Instead of doing backup/restore or trying to copy the database.)
We used odbc to connect to Oracle. It was shop standard to require the user to enter their login/password. Hard coding it in a program was a big no no. Security is tied to the tables. We wanted to be sure the user logging in had the rights to run that particular report.
This isn’t really a concern in a .NET application. The VB.NET or C# code in the modules is not exposed to the user, whether they’re using a stand-alone application or an ASP.NET page. I have ASP.NET applications running which connect to the database using a standard ID, and pass the user’s Windows login with each report request; the stored procs check the ID in a permissions table to confirm that user has access to the requested report before serving it back up to the browser. It’s just (well, almost) as secure & much easier to maintain.