Develop application using SQL Compact 3.5 SP1 with Visual Studio 2005

Can MS SQL Compact 3.5 SP1 work together with VS 2005? The answer is yes.

We are using Visual Studio 2005 (C#) and wish to create a window desktop application. After some consideration, the decision is for for MS SQL Compact 3.5 SP1. 

To enable me to work on MS SQL Compact 3.5 SP1:

• Install MS SQL Compact 3.5 SP1 
  
• Install MS SQL 2008 Express - to get SQL Server Management Studio to work with SQL Compact for table creation and scripting.
  
• Add MS SQL Compact 3.5 SP1 engine to VS 2005 project
• Connect to MS SQL Compact 3.5 SP1 engine in VS 2005 (C#) project

Install MS SQL Compact 3.5 SP1

We download the MS SQL Compact 3.5 sp1 from http://www.microsoft.com/sqlserver/2005/en/us/compact-downloads.aspx

The installation is easy and straightforward. I assume there will be no problem facing here.

Install MS SQL 2008 Express - to get SQL Server Management Studio to work with SQL Compact for table creation and scripting

In order to install MS SQL 2008 Express, you need to have below installed prio to that

• Microsoft .NET Framework 3.5 Service Pack 1
  
• Windows Installer 4.5
  
• Windows PowerShell 1.0
  

For more helpful information, you can refer to http://www.symantec.com/community/article/5920/install-and-configure-sql-server-2008-express

After finish installation, you can connect to the MS SQL Compact 3.5 SP1 using SSMS. Open SSMS, choose Server type: "SQL Server Compact Edition" as shown below.

After that you can choose to create a new MS SQL Compact database or use the existing one as shown below:

After the connection, you can create table and etc. 

p/s: if you hit error "'' is not a valid login or you do not have permission" during installation, please refer to my previous post.

Add MS SQL Compact 3.5 SP1 engine to VS 2005 project

Open the project for the application, right click on Reference.

Browse on SQL Server Compact 3.5 sp1 engine (System.Data.SqlServerCe.dll) and add as reference. 

My location is in "C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop\System.Data.SqlServerCe.dll"

Connect to MS SQL Compact 3.5 SP1 engine in VS 2005 (C#) project

remember to add reference to your C# code

using System.Data;

using System.Data.SqlServerCe;  // <-- Add this

SqlCeConnection _connection;

string fileName = "mycompactDB.sdf";
           string password = "AbCd1@3$5^7*";

           string connectionString = string.Format(
               "DataSource=\"{0}\"; Password='{1}'", fileName, password);

           _connection = new SqlCeConnection(connectionString);
           _connection.Open();

DONE! Hope this can help.

For other reading, can refer to 

SQL Server Compact 3.5

http://technet.microsoft.com/en-us/sqlserver/bb671088.aspx

SQL Server Compact 3.5 How-to Tutorials

http://msdn.microsoft.com/en-us/sqlserver/bb895908.aspx 

SQLCE 3.5 Database Tutorial

http://dotnetperls.com/Content/SQLCE-Database-Use.aspx

Dynamic Image in Crystal Report 9 Visual Studio 2005 - C#

I found many developers facing the same problem as i do - dynamic include image to crystal report with Visual Studio 2005 . This is how we solve our problem in C# when developing a desktop window application.

1. In your xsd, create a colomn that does not exist in your dataset, example, [LogoImage], and set the datatype to “System.Byte[]”. You might find the datatype option only have “System.Byte”, you can manually type in “[]”.
   
2. Add the following code in your form.
   
   private void AddImageColumn(DataTable objDataTable, string strFieldName)
   {
   try
   {
   DataColumn objDataColumn = new DataColumn(strFieldName,
   Type.GetType("System.Byte[]"));
   objDataTable.Columns.Add(objDataColumn);
   }
   catch (Exception ex)
   {
   //handler
   }
   }
   
   private void LoadImage(DataRow objDataRow, string strImageField, string FilePath)
   {
   try
   {
   FileStream fs = new FileStream(FilePath, System.IO.FileMode.Open,
   System.IO.FileAccess.Read);
   byte[] Image = new byte[fs.Length];
   fs.Read(Image, 0, Convert.ToInt32(fs.Length));
   fs.Close();
   objDataRow[strImageField] = Image;
   }
   catch (Exception ex)
   {
   //Handler
   }
   }
   
   
3. After your getting your dataset from database and before bind to the report view, do this.
   
   AddImageColumn(myDataTable, "LogoImage");
   
   for (int index = 0; index < myDataTable.Rows.Count; index++)
   {
   if (myDataTable.Rows[index]["LogoLocation"].ToString() != "")
   {
   if(File.Exists(myDataTable.Rows[index]["LogoLocation"].ToString()))
   {
   LoadImage(myDataTable.Rows[index], "LogoImage",
   myDataTable.Rows[index]["LogoLocation"].ToString());
   }
   else
   {
   LoadImage(myDataTable.Rows[index], "LogoImage",
   "C:\\NoImage.jpg");
   }
   }
   else
   {
   LoadImage(myDataTable.Rows[index], "LogoImage", "C:\\NoImage.jpg");
   }
   }
   
4. Then in your Crystal Report, just drag this column into the report.

Hope this solve your problem.

Installation SQL 2008 Express Error: '' is not a valid login or you do not have permission

This is a very strange error. I've received error message "'' is not a valid login or you do not have permission" when trying to install Microsoft SQL Server 2008 Express Edition in my Microsoft XP Pro SP3 Machine.

To know more on installation guide, please check Install and Configure SQL Server 2008 Express by CondorMan.

I've followed the steps and got error message "'' is not a valid login or you do not have permission." and print screen as below.



I have no idea why is this. I've tried few suggestion stated in some of the forum:

1. I've tried to change my user name and try to install.
2. Create a new user "sa" with strong password. Login to window using this account and do installation.

Both methods fail.

Finally, i try to rename my machine/computer name and try to install and the installation completed successfully.




Conclusion:
This may be a bug from microsoft but i'm not sure.

My scenario: computer name = my user account

My Solution: change computer name to != user account then do installation.

p/s: Change user account won't help.

Hope this can help you. Share your solution if you have different one.