Saturday, June 23, 2012

MVC3 on Soft Sys Hosting

I am trying to get an MVC 3 web application running on a Web Server. Soft Sys Hosting claims support of MVC 3 and .Net 4.0 and since this is what I am using, I thought I would give it a shot.

Setting up Soft Sys Hosting

I started by logging in and ordering a package including domain name N1-Web.com. That was easy. What was much more complicated than it had to be was setting up all the logins. I don’t know why there were so many. I set all the passwords to be the same. See Password Safe

Host Name

User Name

Description

https://www.n1-web.com:8443

https://173.248.177.6:8443

nwebcom

This is the login to Parallels control panel for ONE machine account.

http://domaincp.softsyshosting.com/customer

wc@wc1.us

This is the login for the master account

ftp://www.n1-web.com

Nwebcom

This is the default FTP account. It is read only.

ftp://www.n1-web.com

Warren

I had to set this up to get an FTP account that would allow me to write

With this, I was able to upload HelloWorld.html to /httpdocs and display it using: http://www.n1-web.com/HelloWorld.html.

Uploading Files

I am starting by copying the following to /httpdocs:

  • Bin directory (including EntityFramework.dll which I hope is not necessary)
  • Content directory
  • Images directory
  • Scripts
  • ASPX files
  • N1_Logo.gif
  • Global.asax
  • Web.config

I also renamed index.html to index.html_old.html

 

Trying to run the web site

When I tried to run the web site, the system told me I had errors but would not tell me what they are. I added <customErrors mode="Off"/> to the <system.web> section of Web.Config.

Then when I ran it again, it tells me:

clip_image002

So, I contacted them and they did something that caused me to get a different error message:

clip_image004

I was able to make this go away by getting rid of the line: <customErrors mode="Off"/> in Config.sys that I had added above. This gave me a bit of success; I was able to get the default page of my application, then go to the users page and then try to do a filtered search. The attempt to go to the database failed and of course since I could no longer see my errors, I did not get an error message.

So, I surrounded the code in N1_UserController with a try/catch block with catch doing a Response.Write(U_Log.ToHtmlText(ex));.

This showed me the error which was that it could not find MySql.Data. I tried copying MySql.Data.dll from: clip_image006 to the bin directory on the web site.

That worked! Horray! I am up and running.

Wednesday, May 2, 2012

Moving from SQL Server to MySQL for Web Development

I am a Web Developer who is quite comfortable using Visual Studio 2005 and 2008 to build web sites based on SQL Server. Today, I am trying to transfer this knowledge to creating sites based on three new technologies:

  • Visual Studio 2010 Web developer
  • MVC 3 for developing sites that are partially or completely based on the Model / View / Controller design pattern. This seems to be the wave of the future and also seems to be the basis for using DHTML5 with AJAX. Hey – maybe this isn’t necessary, but it looks to me like the most probably future direction for Microsoft based development.
  • MySQL. Narrative 1 databases are currently based on MySQL and this will continue. This last transition is the one I will talk about here.

Connecting to MySQL

In my experience, connecting the plumbing is always the hardest part of any new technology and connecting to MySQL from Visual Studio is no exception. Here is what I think is necessary.

  1. You need something called the MySQL Connector for .Net which I got from http://dev.mysql.com/downloads/connector/net/. Somewhere I saw that I was supposed to uninstall previous versions of this. I did so and that made my Narrative 1 workbooks stop connecting to the MySQL databases. Hopefully, reinstalling Narrative 1 will correct this,but I have not yet tried this.
  2. Once you have successfully installed MySQL Connector, open the VS 2010 project and add a reference to this. Right click the References section under the Project in the Solution Explorer, Select the .NET tab, and sort by Component Name. Scroll down and click on MySQL.Data. Then click OK. For me, this component was on my system at

image

For easier access, I added the following to the top of the file:

image

The first step in using the program to connect to a MySQL database is setting up the connection. Here is the the connect string format that I ended up using:

image

At this point, one would think that all our problems would be solved; if you can do it in SQL Server, you should be able to do it in MySQL, right?  WRONG! It ain’t that easy.

Accessing a table seems to work as one would expect. You set the Command Text property to the name of the table, and set the Command Type to Table and you get back the entire table. No Problem.

Invoking a normal SQL Command also seems to not be a problem. Set the Command Text property to a string holding the command, set the Command Type to Text and it runs the the stored procedure.  No Problem.

Stored Procedures, however, are a problem. Set the Command Text Property to a string holding the procedure name, set the Command Type property to Stored Procedure and Execute Reader says that it cannot find the Stored Procedure. I have tried this with and without “`” characters, with and without the database name in front, with and without the “( )” characters surrounding it all to no avail. I suspect that there is some stupid incantation that I am missing, but I have no idea what that would be.

I did find a workaround. Instead of setting the Command Type to Stored Procedure, I set it to Text. I was successful when I set:

image

The next issue is the passing of parameters. One might think that if you are going to pass the procedure name as text, you might just as well pass the parameters as text as well. That sounds find to me. But there are two reasons that you might not want to do this.

For me, the first reason is that I have code that has to work with both SQL Server and MySQL. It is all set up to loop through the parameters and add them to the parameter list. I want to be able to structure the code the same way in MySQL.

The way you do this is to add the parameter names to the Command Text, preceding the parameter name with a “@”. Note that MySQL procedures would not take arguments starting with “@”; I used “_” instead. When passing the parameters, the “@” character works fine. Because MySQL does not accept default values, it seems that it cares only about the order of the parameters and not the names. This means that all parameters must be passed; you can’t default them to null and skip them as I often do (or did) in SQL Server. Also, I discovered that if one wants to pass a null value to a parameter, one should use “dbNull.Value”. Of course, if you are putting this into a Command Text string, you just put in “null” as shown in the previous code snip.

Here is some sample code that shows how to work with parameters.

image 

Note the Prepare statement above. This causes a sort of compile to occur. In the code above, this would improve performance if the command is to be executed multiple times.

All this sure sounds simple enough. Why did it take me half the day to figure it all out??? I wish it had been in the manuals. But that is what this blog is for, isn’t it?