Connecting html pages + Razor to database

I am doing a learning project in which i wanted to combine Html+Razor and connect it to Database.

Steps I followed.

Created a sample form ( which i intend to use to execute "insert" and "update" queries )
Created Database (Name: TestDatabase.mdf, located inside App_Data)
Put in some sample values in the database with Table name "Student"

updated the webconfig file as follows (ConnectionString was obtained by copying it from Database explorer)

<connectionStrings>
    <add name="TestDatabase" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TestDatabase.mdf;Integrated   Security=True;User Instance=True" providerName="System.Data.SqlServerCe.4.0" />
</connectionStrings>

Inserted the Razor code for connection as follows

@{
   var db = Database.Open("TestDatabase");
   var query = "Select * Student";
}

<html>
    <head>
        <title>Testing For Database Connectivity</title>
    </head>
    <body>
        <div>
            <fieldset>
                <legend>Some Sample Form</legend>
                <form>
                    <div>
                        <label for="FirstName">FirstName</label>
                        <input type="text" name="FirstName" />
                    </div>
                    <div>
                        <label for="LastName">LastName</label>
                        <input type="text" name="LastName" />
                    </div>
                    <div>
                        <label for="SSN">SSN</label>
                        <input type="text" name="SSN" />
                    </div>
                    <div>
                        <input type="submit" value="submit" />
                    </div>
                    <div>
                        @foreach (var row in db.Query(query))
                        {                    
                            @row.FirstName
                            <br />
                        }
                    </div>
                </form>
            </fieldset>
        </div>
    </body>
</html>

But i am getting the following error

Stacktrace

[ArgumentException: Keyword not supported: 'attachdbfilename'.]
System.Data.SqlServerCe.SqlCeConnectionStringBuilder.GetIndex(String keyword) +192
System.Data.SqlServerCe.SqlCeConnectionStringBuilder.set_Item(String keyword, Object         value) +31
System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value) +185
System.Data.SqlServerCe.SqlCeConnectionStringBuilder..ctor(String connectionString) +177
System.Data.SqlServerCe.SqlCeConnection.set_ConnectionString(String value) +239
WebMatrix.Data.DbProviderFactoryWrapper.CreateConnection(String connectionString) +96
WebMatrix.Data.<>c__DisplayClass15.<OpenConnectionStringInternal>b__14() +16
WebMatrix.Data.Database.get_Connection() +19
WebMatrix.Data.Database.EnsureConnectionOpen() +12
WebMatrix.Data.<QueryInternal>d__0.MoveNext() +71
System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +327
System.Linq.Enumerable.ToList(IEnumerable`1 source) +58
WebMatrix.Data.Database.Query(String commandText, Object[] parameters) +103
ASP._Page_TestPage_cshtml.Execute() in d:\ASP.NET\RefreshTesting\TestPage.cshtml:33
System.Web.WebPages.WebPageBase.ExecutePageHierarchy() +209
System.Web.WebPages.WebPage.ExecutePageHierarchy(IEnumerable`1 executors) +68
System.Web.WebPages.WebPage.ExecutePageHierarchy() +152
System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext,   TextWriter writer, WebPageRenderingBase startPage) +78
System.Web.WebPages.WebPageHttpHandler.ProcessRequestInternal(HttpContextBase  httpContext) +121

Error Page Image

I am trying to be very clear and informative but if i have missed out to mention something then please let me know.
I don't know now where i am going wrong or i am making a silly mistake and now i am even doubting that whether this is possible or not. I am new to microsoft languages and i have watched a couple of Youtube videos which use some MVC thing to achieve it. is it possible to do it the way i have done ?? If yes please help me and if NO then what could be my possible options?

回答1:

Are you sure you correctly created a SQL Server CE database?

I believe that should be an .sdf file, not an .mdf file.

What happens if you change the providerName part of your connection string from "System.Data.SqlServerCe.4.0" to "System.Data.SqlClient"?

回答2:

As you created Sql Server Database, then you should set providerName as System.Data.SqlClient.

And I don't see your Initial Catalog setting, I mean you have to set which database you would connect to.

Could you please try to add connection string like this?

<connectionStrings>
   <add name="TestDatabase" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=DBName;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\TestDatabase.mdf" providerName="System.Data.SqlClient" />
</connectionStrings>

where DBName is database name which you want to connect.