Replace SqlServerCE With SqlServer

Aug 18, 2011 at 11:59 AM

Hi, Guys

Thanks for your helpful project silks. But i have a problem when develop Web application base on your structure, that is How to replace SqlServerCE with SqlServer in DataTier MileageStats.Data.SqlCe and when replacing that whether i have implemetation of all class in MileageStats.Data.SqlCe such as : CreateIfNotExistsSqlCeInitializer.cs,  DropCreateAlwaysSqlCeInitializer.cs, DropCreateAlwaysSqlCeInitializer.cs, ISeedDatabase.cs, MileageStatsDbContext.cs, MileageStatsDbContextSeedData.cs, SqlCeInitializer.cs ?

please give me some guides, im getting stuck in that thing. Because the sever that i'm using which are not supported SqlServer CE.

Thanks In advance !

Developer
Aug 18, 2011 at 6:34 PM

Hi,

The MileageStats Reference Implementation is a means to provide guidance on how to fulfill certain requirements by following proven practices, using the latest web standards. Therefore, it's not a rigid structure which should be used when developing such applications, but rather a demonstration of a possible way to achieve certain use cases, with the patterns aforementioned.

Considering that, the MileageStats Reference Implementation abstracts the data access functionality in the MileageStats.Data project (which contains interfaces such as ICountryRepository), and implements those interfaces in the MileageStats.Data.SqlCe project, which contains the data provider-specific logic. If you wish to use a different data provider, you could create a whole new project (e.g. MileageStats.Data.SqlServer), in which you implement those interfaces taking the specific features and implementation details of SqlServer in consideration. One of the benefits of abstracting the data provider through the use of a separate project, and a set of interfaces, is that you don't need to replace anything else than the MileageStats.Data.SqlCe project in order to replace your database implementation.

If you need guidance on Sql Server specific details (in order to prepare your own implementation of the data layer for your application), it's likely that you will obtain better support in the Sql Server forums.

I hope you find this helpful.

Guido Leandro Maliandi
http://blogs.southworks.net/gmaliandi

Aug 21, 2011 at 12:52 PM

I had this project work in SQL Server by changing the connection string

from:

<add name="MileageStatsDbContext" connectionString="Data Source=|DataDirectory|MileageStatsData.sdf" providerName="System.Data.SqlServerCe.4.0" />

To: 

< add name="MileageStatsDbContext "connectionString="Server=MySqlServerName;Database=MileageStatsData;Trusted_Connection=True;" providerName="System.Data.SqlClient" />

 I got runtime error the first time I ran since I am assuming the database was being initialized with "unsupported" features. I can't be sure. In anycase the app ran fine the second time without any problems. I can add/edit and browse the application.

-Nazik

Aug 21, 2011 at 12:59 PM

Hi,

Check out this viedio link.

http://www.pluralsight-training.net/microsoft/players/PSODPlayer.aspx?author=scott-allen&name=mvc3-building-data-i&mode=live&clip=0&course=aspdotnet-mvc3-intro

Aug 30, 2011 at 12:28 PM

Hi Ne7ven,

Did you solved the problem. I was solved that.

Aug 30, 2011 at 12:55 PM
ThaiCarrot wrote:

Hi Ne7ven,

Did you solved the problem. I was solved that.

Hi ThaiCarrot,

I have not been solved my problem yet. Could you post your code as example ? and i attempt changing to use Webform and replacing SQLCE to SQL, keeping implementation of these class in MileageStats.Data.SqlCe  but it do not create database in my folder. How do you do to replace SQLCE to SQL ?

Aug 31, 2011 at 9:13 AM
Edited Aug 31, 2011 at 2:58 PM

Hi,

(DI)

I was fix in WPF and Silverligt in those plate form i wrote a few class.

There's nothings change except two part in MVC.

1. RepositoryInitializer's code Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlClient");

 

2. Cofig files.

<add name="IFSolutionFXDbContext"
   connectionString="Data Source=mac-pc;Initial Catalog=IFSolutionFXDB;User ID=sa;Password=123456789*;Persist Security Info=True"
         providerName="System.Data.SqlClient" />

 

mac-pc;Initial is pc name

IFSolutionFXDB is database name

 

add this Persist Security Info=True" when develop.

More link. http://blogs.msdn.com/b/adonet/archive/2011/01/27/using-dbcontext-in-ef-feature-ctp5-part-2-connections-and-models.aspx

Dec 29, 2012 at 5:47 AM

Hi, 

I also changed database of silk project code to sql server 2008 express using advice from here. And I am facing a error in sql server.

"SELECT INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES;"   as INFORMATION_SCHEMA.INDEXES object not available there. I found following code :

SELECT    [Table] = QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id]))      + '.' + QUOTENAME(OBJECT_NAME(i.[object_id])),    [Index]  = i.name,    [Column] = c.Name,     [Type]   = i.type_desc,    PK = i.is_primary_key,    [Unique] = i.is_unique,    [Unique Constraint] = i.is_unique_constraint,    [DESC] = ic.is_descending_key,    [INCLUDE] = ic.is_included_column,    [Filtered] = i.filter_definition -- only for SQL Server 2008+FROM    sys.indexes AS i INNER JOIN     sys.index_columns AS ic     ON i.[object_id] = ic.[object_id]     AND i.index_id = ic.index_idINNER JOIN     sys.columns c    ON ic.column_id = c.column_id    AND ic.[object_id] = c.[object_id]ORDER BY [Table], [Index], ic.index_column_id;

this returns indexes and primary keys also.

Is there any better way to look for indexes in sql server.

 

 

 

 

 

 

Developer
Jan 3, 2013 at 12:37 PM

Hi,

Based on my understanding this error appears when calling the Initialize method of the RepositoryInitializer in the Global.asax.cs. Which is used to request some data to ensure that the database is created before user requests are processed as the database won't be created until the first request for data occurs.

In particular, I believe the cause of this error is that the SQL query executed there ("SELECT INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES;") may not be valid for a SQL Server Express edition. In my case I replaced the query with this similar one: "SELECT name FROM sys.indexes;" and so far I didn't face additional errors. Nevertheless, If you need guidance on Sql Server specific details or the differences that could be found between the different versions of SQL, I believe you could find better support regarding this for example in the Sql Server forums.

Regards,

Agustin Adami
http://blogs.southworks.net/aadami