GUIDE: Setup VS C# 2008 Express and SQL Server 2008 Express, so it actually works.

[Mindwarp]

How to setup Visual Studio C# 2008 Express with SQL Server 2008 Express with Advanced Services.

NOTE: The files mentioned in this setup are for Vista Business x86. Be sure to download the correct version of the files for your version of Vista.

WHY THIS GUIDE?

As I use C# and SQL daily at work, I thought it would be nice to have a rig at home that I can test stuff on when ideas pop into my head or when I need to work out a problem with some code.

Not wanting to spend money unnecessarily, I thought I'd try MS's free version of their development packages, namely the Express versions. During install I found myself going back and forth to the net trying to find solutions to some of the problems I was encountering. However, what I found was a lack of step by step guides on the net to get these apps not only running but able to talk to one another. In an effort to help those who may wish to tinker with a similar setup, I made this guide to fill the void.

I have included links to the files needed to do this installation and they were valid at the time I wrote this guide, however if links do become broken please tell me so I may fix them.

THE SOFTWARE

The first step is to decide on which flavour of Visual Studio you would like to work with. In this guide, I will be using VS C# 2008 Express.

Program: Visual Studio C# 2008 Express
Location: http://www.microsoft.com/express/download/#webInstall
File Name: vcssetup.exe
Remarks: There are four flavours of VS 2008 Express, C++, C#, Visual Basic and Web Developer. You can download each Express edition individually or if you scroll to the bottom of the page, you can select the Offline .iso. This will give you all four Visual Studio Products in an iso that you can use to make a DVD. The install is straightforward so I will not spend time on it except to mention that you need to select the SQL Server 2008 option during the setup.

Program: SQL Server 2008 Express with Advanced Services
Location: http://www.microsoft.com/express/sql/download/
File Name: SQLEXPRADV_x86_ENU.exe
Remarks: Be sure to check out the Hardware and Software requirements at http://msdn.microsoft.com/en-us/library/ms143506.aspx and to verify the following Prerequisites:

• .NET Framework 3.5 SP1
• MDAC 2.8 SP1 (NOTE: Vista users do not need this file http://msdn.microsoft.com/en-us/library/ms693148.aspx says 2.8 is XP only. Vista has MDAC 2.6 included. See Remarks for this application for more info)
• Windows Installer 4.5
• Windows PowerShell 1.0

Program: .NET Framework 3.5 SP1
Location: http://www.microsoft.com/downloads/d...DisplayLang=en
File name: dotNetFx35setup.exe

Program: Microsoft Data Access Components (MDAC) 2.8 SP1
Location: http://www.microsoft.com/downloads/d...displaylang=en
File name: MDAC_TYP.EXE
Remark: Even though this is listed as a prerequisite by MS, Vista already has MDAC installed so it is not needed. I have included this link so XP users needing this file have something to work with.

Program: Windows Installer 4.5
Location: http://www.microsoft.com/downloads/d...ng=en#filelist
File name: Windows6.0-KB942288-v2-x86.msu
Remarks: If you get the error message "The update does not apply to your system", check under Installed Updates in Programs and Features for the presence of Hotfix for Microsoft Windows KB942288. Apparently, the error message is meant to imply that the Hotfix has already been installed. No confusion there now is there. :/ If you check out this page from MS http://support.microsoft.com/kb/941720/en-us, near the bottom it explains how it can happen.

If you try to reapply this Hotfix on a computer that already has this Hotfix, you may receive the following error message:
Windows Update Standalone Installer
---------------------------
The update does not apply to your system
Note This error message means that this Hotfix was already applied.

That was my case and I wasted an hour searching for the problem.

Program: Windows PowerShell
Location: http://www.microsoft.com/windowsserv.../download.mspx
File name: Windows6.0-KB928439-x86.msu

Program: Windows SQL Server 2008 Express with Advanced Services
Location:http://www.microsoft.com/express/sql/download/
File name: SQLEXPRADV_x86_ENU.exe
Remarks: Here are the installation steps I took to successfully install SQL Server 2008 Express

THE SETUP

• Under Planning, click on System Configuration Checker. It will run a quick check to make sure all the prerequisites are the. You want a solid column of green check marks. You might see a failure for 'Restart Required' but that's no problem. Simply Cancel the Install, reboot and try it again.
  
• Under Installation, select New Sql Server Stand-Alone Installation or Add Features to an Existing Installation. It will run another brief System Check; click Ok if it is green. Next, you will get the Product Key page. You cannot do anything other then select Next here. When you get to Setup Support Files, click Install. Again, it will run a System check, click Next.
  
• You will now be presented with the Feature Selection page. I checked Select All. The important tools here are Business Intelligence Development Studio, which gives you database import/export features and Management Tools that will give you among other things the very useful Management Studio. Management Studio is the SQL Server GUI that lets you manipulate the databases and set permissions and roles.
  
  
  ^{Feature Selection}
  
  
• On the Instance Configuration page use the defaults provided. It is just for naming and since VS C# defaults to 'SQLExpress' for the Instance name, I just left it as-is.
  
  
  ^{Instance Configuration}
  
  
• Disk Space Requirement. Fine.
  
• Next are the Server Configuration parameters. Now you must setup the accounts that will be used to launch the services. Since this is only a development stand-alone for home use, I just used my Windows Account. You could use a separate account for each, such as a named account, but remember if you change your password, you will need to change them on the services as well or they will not start. See here for more details on account settings: http://technet.microsoft.com/en-us/l.../ms143504.aspx
  

  Important We recommend that you do not use the Network Service account for SQL Server services. Network Service is a shareable account, and is appropriate for use as a SQL Server service account only if you can ensure that no other services that use the account are installed on the computer. Domain User accounts that are not a Windows administrator are more appropriate for SQL Server services. Be aware that the Local Service account is not supported for SQL Server Agent.

  Good to know. I left the Start-up at the default settings. Data Directories and Data Stream tabs I left at default settings.
  
  
  ^{Server Configuration}
  
  
• On the Database Engine Configuration, I selected Mixed Mode as I wanted to set the SA account and give it a password. Remember this password, it's important! This is the SA account that is used for the SQL Server administration. Under Specify SQL Server Administrators, you can set yourself in the Administrators group so you can log into the server with your Windows Account. Click on Add Current User and in a few seconds, the account name you are currently logged in under will appear there. You can also add other accounts.
  
  
  ^{Database Engine Configuration}
  
  
• Under Reporting Services Configuration leave the default setting, unless you need SharePoint then you will need to investigate your particular requirements. The Reporting Services is used in Visual Studio to create Reports based on SQL queries.
  
  
  ^{Reporting Services}
  
  
• Installation Rules runs another quick system check.
  
  
  ^{Installation Rules}
  
  
• Under Error and Usage Reporting...um no thanks.
  
• Ready to Install gives you a summary of your setup. Review it then click Install.
  
  
  ^Summary

ERRORS YOU MAY ENCOUNTER DURING SETUP.

When errors are encountered, I found that the best thing to do is to totally uninstall the SQL package and start over again. It will just save you time in the long run.

-My Services failed to install. So now what?! You can check the Log file that is listed at the top of the Final Page but it will not tell you much except that you need to investigate the failure, uninstall the SQL Server fix the problems then reinstall. I did a quick search on the Internet and found that if a previous version was installed and that the directories were not removed after uninstallation then it will cause a failure. Therefore, I uninstalled, purged the old directories and restarted the computer. I then ran CCleaner, sure enough, the registry contained many orphaned entries for SQL Server, and so I fixed those. I also purged the temp files and such, then rebooted again just to be safe.


^{Error Example}


-On my second attempt, I received only one error, Input String was not in the Correct Format for Reporting Services. Turns out my Performance Counters were corrupted :/ To fix this, open a Command Prompt using the Run As Administrator option (this is another tidbit I discovered that isn't mentioned anywhere I could find and the following command will fail if you don't run it with Administrative rights). Type in "lodctr /R". This will rebuild the Performance Counters

TESTING

If you've arrived at this point, you should now have a working setup of VS C# 2008 Express and SQL Server 2008 Express. Let's test the connection to see if it works.

First, launch the Microsoft SQL Server Management Studio. This is where you'll see if you setup the Datbase Engine Configuration correctly. On the login popup, leave everything at it's default values and click Connect. You should now see a visual representation of your SQL server.

In the left-most pane, expand Databases. Right click on Databases and select New Database. In Database Name, enter a name. I called it Test. Then click OK.

We now need to add permissions so users can access tha database. Right click on the newly created database and select Properties. Click on Permsissions then click on the Search button. Add the user you want to have access to the database then set the permissions for that users.

Expand your database then right click on Tables and select New Table. A table should appear in Design view. Enter 3 columns, one called CustomerID with an Int datatype, another called NameLast with a varchar(50) datatype and a final one called NameFirst also with a varchar(50) datatype. Go back to the CustomerID column and uncheck Allow Nulls. Go to your Column Properties, expand Identity Specification and change Is Identity from No to Yes. This will cause all new entries to auto increment by 1 and you can then use this as your Primary Key. Right click on the CustomerID column and select Set Primary Key. This ensures that every new record entered with always be unique. Right click on the Table's tab and select Save. Save it as Customers. Right click on your new table and select Edit Top 200 Rows. Enter a couple of make believe customers. You'll notice that you cannot enter the CustomerID, that's because it will be set by the database. You'll also notic ethat you don't have to Save your entries as they are saved automatically when a new record is entered.

Launch VS C# 2008 Express and create a new Console Project. For the sake of this test, we'll create a small console app that will pull info from the Test database and display it. Make sure you use the SQLClient (the line in bold)


Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Test
{
    class Program
    {
        static void Main(string[] args)
        {
            //Setup the connection string. 
            string connectionString = "Data Source=your server name\\SQLExpress;Database=Test;Trusted_Connection=True";
            //Create a new SqlConnection and pass the connection string to it
            SqlConnection sqlCon = new SqlConnection(connectionString);
            //Open the connection
            sqlCon.Open();
            //Setup the query
            string commandString = "SELECT * FROM Customers";
            //Create a new command tp pass the command string to the connection
            SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
            //Read the data
            SqlDataReader dataReader = sqlCmd.ExecuteReader();
            //Cycle through the data        
            while (dataReader.Read())
            {
            Console.WriteLine(String.Format("{0} {1} {2} ", dataReader["CustomerID"], dataReader["NameLast"], dataReader["NameFirst"]));
            }
            //Close the connection
            dataReader.Close();
            sqlCon.Close();

        }
    }
}

[Mindwarp]

Wow, 167 views and no comments

Either alot of 'guest users' are using it and not commenting or it's a garbage guide.

[JPM2008]

Hi MindWarp,

I have an easy question for you. That is I hope it's easy.
Yesterday afternoon I was loading SQL Express 2008 on mydesktop and finally got it to load and wound up with that installation list box. Since it was quitting time I immediately closed shop and left work, but I forgot to note where and what name was the file that displayed that setup box for SQL 2008 Express. Can you help me please.

I looked at Recent Items and couldn't find any reference to it there.

Thanks if you can provide any help otherwise I will have to start the whole process again.

[Mindwarp]

Hi JPM, I'd be glad to help. However I'm not quit sure I follow your question. Are you refering to the location of the ini configuration file that's listed on the install summary?

[ValueSize]

Sorry Mindwarp! It was (is) a great looking guide. I would just be more interested in getting mysql++ or connector/++ working in Xcode or VS.

I plan on following your install guide in a month or so when I'm on break though!

[JPM2008]

Hi Mindwarp,

I reran the installation and saw that the Setup file was under the alphanumerical folder created only after you install the download.
So it seems to delete itself after you shut down your computer.

However I do have another question as today I was following your instructions on setting up SQL Express. When it comes to the Server Configuration box since I will be creating my software to access SQL Express under my account name and password, will Microsoft allow me to read/write to the database if I put in a different Account Name and Password in the Server Configuration boxes?

[Mindwarp]

Originally Posted by ValueSize

Sorry Mindwarp! It was (is) a great looking guide. I would just be more interested in getting mysql++ or connector/++ working in Xcode or VS.

I plan on following your install guide in a month or so when I'm on break though!

Thanks, I look forward to hearing how it works out for you.

Originally Posted by JPM2008

Hi Mindwarp,

I reran the installation and saw that the Setup file was under the alphanumerical folder created only after you install the download.
So it seems to delete itself after you shut down your computer.

Ok, I see what you meant. What I did to get the folder was during the install process, I navigated to the directory and copied it to a different location.

Originally Posted by JPM2008

However I do have another question as today I was following your instructions on setting up SQL Express. When it comes to the Server Configuration box since I will be creating my software to access SQL Express under my account name and password, will Microsoft allow me to read/write to the database if I put in a different Account Name and Password in the Server Configuration boxes?

Absoultely, the account given during the config phase is used primarily to manage the SQL server. The default SA account also allows this, that's why the password that was set during the install is critical. If no account was added and the SA account password is forgotten you can no longer manage the SQL databases.

Now then, if you go to START > ALL PROGRAMS > MICROSOFT SQL SERVER 2008 you will find an application called SQL Server Management Studio. This is the important component that I mentioned in the guide. It's a graphical interface that allows admins to manage the databases. Basically, this is where you set the access permissions. As in your case, since you have different accounts managing and accessing the db's, it's simply a case of assigning the user the desired roles.

When you launch the app, a login screen appears. If you set your Windows account as part of the SQL server admins, you'll see your name listed and Windows Authentication selected, in which case you simply click Connect and Windows will provide the necessary credentials to access the server. If you select SQL Server Authentication during the login, you'll use the SA account and the password mentioned above.

As long as the permissions are set, you can allow or deny access to different accounts to the db's. Keep in mind that assigning permissions can become a bit confusing in SQL. I'm still learning the correct methods for applying the permissions so I'm no expert!

[JPM2008]

Thanks Mindwarp for the guidance. I have currently run into one error under Error and Usage Reporting which is that my version of VS 2008 is not the SP1 version. So after I install that I will tackle the rest of the installation and tomorrow check out what you said above in creating a small database and setting up permissions.

Oh by the way do you have C++/CLR version for creating the database you created in C# code above as I will be using C++?

[Mindwarp]

I ran the C# code into a converter and this is the result. Since I am not proficient in C++, I have no clue weather the conversion is accurate or not. I hope it helps.

 using namespace System;
using namespace System::Collections::Generic;
using namespace System::Linq;
using namespace System::Text;
using namespace System::Data::SqlClient;

namespace Test
{
	private ref class Program
	{
		static void Main(array<System::String^> ^args)
		{
			//Setup the connection string. 
			System::String ^connectionString = "Data Source=your server name\\SQLExpress;Database=Test;Trusted_Connection=True";
			//Create a new SqlConnection and pass the connection string to it
			SqlConnection ^sqlCon = gcnew SqlConnection(connectionString);
			//Open the connection
			sqlCon->Open();
			//Setup the query
			System::String ^commandString = "SELECT * FROM Customers";
			//Create a new command tp pass the command string to the connection
			SqlCommand ^sqlCmd = gcnew SqlCommand(commandString, sqlCon);
			//Read the data
			SqlDataReader ^dataReader = sqlCmd->ExecuteReader();
			//Cycle through the data        
			while (dataReader->Read())
			{
			Console::WriteLine(System::String::Format("{0} {1} {2} ", dataReader["CustomerID"], dataReader["NameLast"], dataReader["NameFirst"]));
			}
			//Close the connection
			dataReader->Close();
			sqlCon->Close();

		}
	};
}

[JPM2008]

Thanks again Mindwarp for the C# converted code to C++.

I opened the MS SQL Server Managerment Studio and kind of was overwhelmed when skimming over everything. I am use to using Access DB which was nice and simple to use, but SQL Server is much more complex. I will try that code you gave me and see what happens.
Oh by the way again... can I get a copy of that converter code you have for C# to C++? I have been looking at all the example codes online and text online and most everything is written in C#. So that converter code would at least give me a shot in the right direction when using those samples.

[Mindwarp]

I used a trial version of Instant C++, it works amazingly fast.

http://tangiblesoftwaresolutions.com...rp_Edition.htm

[anthony_chri]

Sorry, but I have VS 2008 Pro and SQL Server 2008 Dev installed.

Also, if you dont have it, this is the greatest .NET tool

http://www.red-gate.com/products/reflector/
It will decompile just about any .NET code into any supported language.

[JPM2008]

Mindwarp,

Under your C++/C# code the setup connection string reads
"Data Source= your server name\\SQLExpress;...."

I put my computer name there (JPMsComputer) but the program
can not find the source still. Do you have any suggestions?
I can't find the database folder where "Test" exists. Is there an Test.mdf file?

[Mindwarp]

The connection string in your case should be:

string connectionString = "Data Source=JPMsComputer\\SQLExpress;Database=Test;Trus ted_Connection=True";

If you created the Test db as mentioned in the guide, it should be found at C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA

If that still fails, try this:

In the management studio, right click on the database and select New Query. In the pane type the following commands:

exec sp_configure 'user instances enabled', 1

and press Execute then type

reconfigure

and again press Execute.

You may have to restart the server to get it going.

[JPM2008]

Mindwarp,

Did what you described above and still get error:
"There is no source code available for the current location" even though the test.mdf exists in the path described above.

Additional Comment:

Mindwarp,

Maybe because I partitioned Vista and SQL Express in my E: drive. ???

[Mindwarp]

Sounds like a reference problem. Try this: create a new project with a different name and recreate the code. Rebuild and test. Even though the code is the same, I think there is a problem with referencing after the access conditions were changed. I will have to investigatge this error, let ne know how it works out. As for partioning, if you can log into the SQL server then it's location is not a problem. I think it has more to do with changes to access after the initial creation.

[anthony_chri]

You might try:

string connectionString = "Data Source=.\\SQLExpress;Initial Catalog=Test;Integrated Security=SSPI";

The error "There is no source code available for the current location" is normally when trying to debug in release mode. In the VS Tool bar, make sure the drop down says Debug and not Release.

[JPM2008]

Hi Mindwarp,

Found the root cause of the problem. It seems like microsoft moved the "Listener" from SQL Server 2000 to a dedicated "Listener" in SQL Server 2005 and I assume to 2008 also.
That service is SQLBrowser. You can find "SQL Server Browser" under "SQL Server 2005 Service" in the SQL Service Configuration Manager and select "Automatic" to make the service "running".

See "http:/blog.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx"

Additional Comment:

Mindwarp,

I am doing a little experiment with you code. I have a laptop that is network connected to my desktop that has SQL Server Express running. I took my source code and am running the application now in my laptop. I want to see if I can read the SQL database from another laptop or remote. I am running into an error which I knew would happen ... Error Locating Server/Instance Specified.
Since I don't have SQL Server Express 2008 in my laptop I am wondering if the DIstributable would work.
The error also states:

Additional Comment:

Disregard the last sentence because my wireless keyboard's batteries just died and I don't remember what I was going to say anyway.

Additional Comment:

Mindwarp,

Do you know of any good books or online resources where I can program SQL Express using C#? I can use the converter program to translate to C++ if needed. I want to be able to create database, Tables, and write columns into the DB Tables as well as delete and copy records. I also of course need to populate the DB tables with data programmatically. Basically a book with good examples (and right syntax). I am also wondering if there is info on how to setup the SQL Server or the application I write to be able to communicate to another computer using Vista sharing instead of having the SQL Server express residing in a dedicated server. Any info would help. Thanks a lot.

[paperclipguy]

Great stuff! Much thanks!

[Mindwarp]

Originally Posted by paperclipguy

Great stuff! Much thanks!

Your welcome! Did the guide work 100% for you or did you run across any problems? If there was an issue, and this goes for anyone reading this, please let me know so I can adjust the guide.