[SQL/MySQL/.NET] Setting databases for .NET usage

After setting up WCF services and having my android devices consuming it, I wanted my WCF to be the layer that communicates to a database resource. At first, I had a hard time with getting started with SQL Server, so I went with MySQL instead.

NOTE: In your IIS, make sure the ASP.NET 4.0 Application Pool "Identity" is set to Local System. This is the reason why SQL Server wouldn't go so smooth at first, but I will discuss details later.

MySQL setup:

1. Download MySQL server installer, MySQL OBDC connector, and MySQL Net connector. Optionally, download the Workbench, which provides a GUI solution for handling SQL functions. If you're lame like me, you would use this tool.

2. Set up your tables using Workbench or commandline.

3. In Visual Studio, add a new ADO.NET Entity Data Model and point to your MySQL Server when prompted. If you don't know the server name or IP Addy, you can always go to the Workbench and see the properties there. Mine was 127.0.0.1. Follow through with creating the .edmx file.

4. Now you should see the newly created entities right in front of you. The name of the object is exactly what the header of the entity is. So you can instantiate it like:

User objUser = new User();

Of course, we don't want to instantiate like this, but rather grab the info from the database and create the object then. We will use LINQ to do so.

 Now look for Entity Container Name in the properties of your .edmx file. This is the object you use to connect to the database. So lets just put 2 and 2 together with the entity object included:

MyDBEntities db = new MyDBEntities();
List<User> users = ( from p in db.Users select p ).ToList();

This should select all users from a "Users" table and return it as a list of User objects.

Run the WCF Test Client and test if it works.

SQL Server setup:

Okay, if you installed SQL Server 2008, I believe that it should've created an instance of SQLEXPRESS. So we will just go with this default setup, although, you might have set up another instance, you'll just have to remember the server name for that one.

So by default, your instance of SQLEXPRESS should be your <computername>\SQLEXPRESS. (ex. KETTLEPOT-PC\SQLEXPRESS).

Create tables in SQL Management Studio (Free) by using the server name.

From now on, follow step 3 from the MySQL setup section to configure everything in VS2010.

 Now, if you're getting the "underlying provider" error when you run the WCF Test Client and invoke your method, check out the "NOTE" at the beginning of this post.

Hope that helps!!!!

[.NET] Setting up WCF in Virtual Directories of IIS

Alright, this one was an adventure for me. So this info will be here for the next time I need to set up WCF in a virtual directory. This tutorial will start from the beginning, which is installing IIS. This is assumed that Visual Studio 2010 is already installed. So let's get started with the list:

1. Install IIS through Programs and Features. If you're feeling lazy, just try to install as much of the IIS options you can check. Otherwise, you just need ASP.NET under World Wide Web Services. This will auto-check dependencies as well. And IIS Metabase under IIS 6 Management Compatibilty. Also, you will need to go under the Microsoft .NET Framework 3.5.1 section, which is separated from the IIS section btw, and check both boxes under that. They should have the description with Windows Communication Foundation within it.

2. Make a new wcf service project or open an existing one. Go to it's project properties. Under the Web tab, select he use local IIS option. When you save, it will create the virtual directory for you. There are other ways to deal with the virtual directory like publishing, or adding it directory in the IIS manager. It is up to you.

If you get an error when you try to save the project properties, check if you have all the IIS components installed. VS2010 does a good job telling you what you need anyways.

Hmmm, that seemed to be a short list. But here are some problems + fixes that you may have to encounter.

Errors you may have encountered:

1. "Something about having a duplicate handler in your web.config". What happened here was that I tried to add a handler myself before installing the WCF stuff under Microsoft .NET Framework 3.5.1 section. Therefore, IIS couldn't even populate it's integrated handlers, let alone, let me add any more handlers. So I had to edit the web.config manually by copying it outside it's original directory, removing the handler, and then moving it back into the original directory. It will not let you edit it if you open it within the original directory.

2. 404.17: The requested content appears to be script and will not be served by the static file handler.

Check if you installed all the necessary components for IIS and WCF. You may have to run aspnet_regiis.exe -i under the command line if you have ASP.NET 4.0. After doing this, WCF should run smooth and you should be able to see the .svc file in your browser as intended.  This solution may be different if you are running an older version of ASP.NET.

3. Make sure  the application pools between the website and your virtual directory are ASP.NET 4.0. I haven't thoroughly tested this one out yet. I changed the website to ASP.NET anyways just to be safe. 

Local Host Stuff 

 If you tried to access your IIS or .svc from another computer, you may find that it will not connect by timing out or what not. This may be caused by the firewall. If you are using Windows Firewall, find and enable the inbound rule for World Wide Web Services. The port should be 80.

DNS

When on another computer, you may not want to type in 192.168.1.23 just to get to connect to your service. You may want to type in http://MySite instead. This will be controlled in a DNS Server. The DNS settings should be located in your router firmware.

[.NET/IIS] Tired of opening up a web service just to run it? Use Virtual Directories!

Alright, so lately I've been working a lot with web services and one the major gripes is actually running the darn things. At first, I always had to open VS2010, open my project, and then right click the solution that contains the svc, and click view in model browser. Being that I'm currently on windows XP, this whole procedure was not pleasant. Then I found out how to activate it through a batch script. The batch script looked like this:


@echo off

::WEBSERVER PATH
set webserverpath="C:\Program Files\Common Files\Microsoft Shared\DevServer\10.0\WebDev.WebServer40.EXE"

::WEBSERVICE PATHS
set webservice="C:\Workspace\MyWebService"

::COMMANDS
start "" %webserverpath% /port:3934 /path:%webservice%

To add more web service solutions, I just had to new variables and commands under WEBSERVICE PATHS and COMMANDS. Although, this saved me A LOT of time compared to the first method, it was still a hassle just click the little bugger. Also, when a web project gets deployed on a server, there may be instances (maybe all instances) where the web services are needed to be running at all times. So if you configure your localhost to be the same way, then there shouldn't be any hassles when deploying code on the server (for example, the need to change the web.config).

So here's how to do it:

Go to your IIS. So under where you have Web Sites, and maybe Default Web Site, add a new virtual directory. Assign the path to where your .SVC file or web service project is located. Now go back to your web app in VS2010, and open up the web.config. You may need to add or edit the necessary endpoints. Right now, my endpoint address looks like, "http://localhost/MyWebService/WebService.svc". After that, you should be good to go and should never have to run or edit your webservice....hopefully...yea

Edit: If you're running .NET 4, make sure your application pool for your web service is also version 4 in the IIS and ASP.Net tab of your service.

[.NET] IIS - Failed to access IIS metabase

In command line, go to the folder of your .NET Framework version that is specified in the error page. It should be the small text at the bottom. Now type aspnet_regiis -i and you should be good to go. If not, try aspnet_regiis -u and aspnet_regiis -i afterwards. If that doesn't work, then keep trying aspnet_regiis -i. It'll eventually work I guess.

This is for a WinXP SP3 system running IIS 5.1.