none
DB not showing up in SQL server, but table records seem to be recorded. RRS feed

  • Question

  • I'm trying to create a database using code first, by creating POCO classes

    and having the database automatically generated in code.  I have SQL Server 2012 installed

    and am using Visual Studio 2010.  I am programming this in ASP.NET, but the same problem happens when I do this in a Console application.  Apparently, it is recording the data somewhere because

    items which were added to the database appear after re-running the program, even after closing

    Visual Studio and rerunning.  But my database is not visible anywhere.  Can anyone tell me how to make this data visibly stored where I can access it from SQL Server Management Studio?  Even after the database is created, my code is showing that it was created (if I uncomment the commented part in my code listed to test if the database has been created or not) and everything is being stored, but I can't find out where.  I have included my web.config, my POCO class c# file, and the web page file for the starting web page where the error occurs.  Nothing else related to the POCO classes or the database is handled in other pages in the project, so I really don't think those files apply, so I haven't included them.  I can't post pictures right now, but basically what happens after running the program a few times is that my listbox is populated with data that was entered when the program was previously run.  Don't have a UI to enter data now, it's all hard coded, I know that's not the best practice, but that's how I'm doing it at this point.  And when I go to SQL management studio, to server explorer, or to solution explorer, there is no record of my database.  Any help would be appreciated.

    NOTE: the Database is not showing up here, or when I go to server explorer and try to add a database, or when I go to my solution explorer.  I double checked that the location in my connection string is consistent with where my databases are stored in SQL Server 2012.

    POCO c# file JamesPoco.cs

    using System.Web;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity;
    using System.Data.Objects;
    using System.Data.Objects.DataClasses;
    
    namespace JamesPoco
    {
        // table for benefits applicants
        [Table("Application_")]
        public class Application_
        {
            [Key]
            public Guid ApplicationID { get; set; }
            public DateTime SubmittedDate { get; set; }
            public virtual List<Individual> Individuals { get; set; }
        }
    // table for individuals applying for benefits (not yet used in example)
        [Table("Individual")]
        public class Individual
        {
            [Key]
            public Guid IndividualID { get; set; }
            public virtual Application_ Application_ { get; set; }
            [Required]
            [StringLength(15, ErrorMessage = "Name must be no more than 15 characters long.")]
            public string FirstName { get; set; }
            [Required]
            [StringLength(21, ErrorMessage = "Name must be no more than 21 characters long.")]
            public string LastName { get; set; }
            public bool IsHeadOfHousehold { get; set; }
            [Required(ErrorMessage = "SSN required.")]
            public string SSN { get; set; }
            public DateTime DOB { get; set; }
            [StringLength(1)]
            public string Gender { get; set; }
            public virtual List<IndividualBenefit> IndividualBenefits { get; set; }
    
        }
    // table for specific benefits that individuals can have 
        [Table("IndividualBenefit")]
        public class IndividualBenefit
        {
            [Key]
            public Guid BenefitID { get; set; }
            public virtual Individual Individual { get; set; }
            [Required]
            [StringLength(3)]
            public string BenefitCode { get; set; }
        }
    // class so that the poco classes will generate to database
        public class BenefitsContext : DbContext
        {
    // CodeFirstBenefitsDatabase is the name of the Database
            public BenefitsContext() : base("CodeFirstBenefitsDatabase") { }
            public DbSet<Application_> Applications { get; set; }
            public DbSet<Individual> Individuals { get; set; }
            public DbSet<IndividualBenefit> IndividualBenefits { get; set; }
        }
    }


    web page c# file Default.aspx.cs

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using JamesPoco;
    
    
    namespace CodeFirstEntitiesWebApplication
    {
        public partial class _Default : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                //using (var db = new BloggingContext())
                //{
                //    var name = "James";
                //    var blog = new Blog { Name = name };
                //    db.Blogs.Add(blog);
                //    db.SaveChanges();
                //    var query = from b in db.Blogs
                //                orderby b.Name
                //                select b;
    
                //    //NumAdded.Text = query.First() ;
                //    foreach (var item in query)
                //    {
                //        NumAdded.Text = item.Name.ToString();
                //    }
    
                //}
    
                using (var db = new BenefitsContext())
                {
                    //if (db.Database.Exists())
                    //{
                    //    Result1.Items.Add("exists");
                    //}
                    //else
                    //{
                    //    Result1.Items.Add("DNE");
                    //}
                    //return;
                    var app = new Application_ { ApplicationID = Guid.NewGuid(), SubmittedDate = new DateTime(2006, 7, 22) };
                    if (!db.Database.Exists())
                    {
                        db.Database.Initialize(true);
                    }
                    db.Applications.Add(app);
                    int i = db.SaveChanges();
    
    
                    
                    var query = from b in db.Applications
                           orderby b.SubmittedDate
                           select b;
    
                    
                    foreach (var item in query)
                    {
                        
                        Result1.Items.Add(item.SubmittedDate.ToString());
                       
                    }
                   
                    
                       
                }
            }
        }
    }

    web.config:

    <!--
      For more information on how to configure your ASP.NET application, please visit
      http://go.microsoft.com/fwlink/?LinkId=169433
      -->
    <configuration>
      <configSections>
        <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
        <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.4.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
        <sectionGroup name="devExpress">
          <section name="themes" type="DevExpress.Web.ASPxClasses.ThemesConfigurationSection, DevExpress.Web.v12.1, Version=12.1.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" requirePermission="false" />
          <section name="compression" type="DevExpress.Web.ASPxClasses.CompressionConfigurationSection, DevExpress.Web.v12.1, Version=12.1.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" requirePermission="false" />
          <section name="settings" type="DevExpress.Web.ASPxClasses.SettingsConfigurationSection, DevExpress.Web.v12.1, Version=12.1.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" requirePermission="false" />
          <section name="errors" type="DevExpress.Web.ASPxClasses.ErrorsConfigurationSection, DevExpress.Web.v12.1, Version=12.1.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" requirePermission="false" />
        </sectionGroup>
      </configSections>
      <connectionStrings>
        <add name="ApplicationServices3" connectionString="Data Source=USCMPDEPC44;Initial Catalog=CodeFirstBenefits;Integrated Security=True" providerName="System.Data.SqlClient" />
      </connectionStrings>
      <system.web>
        <compilation debug="true" targetFramework="4.0" >
          <assemblies>
            <add assembly="DevExpress.Web.v12.1, Version=12.1.6.0 , Culture=neutral, PublicKeyToken=b88d1754d700e49a" />
          </assemblies>
        </compilation>
        <authentication mode="Forms">
          <forms loginUrl="~/Account/Login.aspx" timeout="2880" />
        </authentication>
        <membership>
          <providers>
            <clear />
            <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices3" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />
          </providers>
        </membership>
        <profile>
          <providers>
            <clear />
            <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices3" applicationName="/" />
          </providers>
        </profile>
        <roleManager enabled="false">
          <providers>
            <clear />
            <add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="ApplicationServices3" applicationName="/" />
            <add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/" />
          </providers>
        </roleManager>
        <httpModules>
          <add type="DevExpress.Web.ASPxClasses.ASPxHttpHandlerModule, DevExpress.Web.v12.1, Version=12.1.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" name="ASPxHttpHandlerModule" />
        </httpModules>
        <httpHandlers>
          <add type="DevExpress.Web.ASPxClasses.ASPxHttpHandlerModule, DevExpress.Web.v12.1, Version=12.1.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" verb="GET" path="DX.ashx" validate="false" />
        </httpHandlers>
      </system.web>
      <system.webServer>
        <modules runAllManagedModulesForAllRequests="true">
          <add type="DevExpress.Web.ASPxClasses.ASPxHttpHandlerModule, DevExpress.Web.v12.1, Version=12.1.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" name="ASPxHttpHandlerModule" />
        </modules>
        <validation validateIntegratedModeConfiguration="false" />
        <handlers>
          <add type="DevExpress.Web.ASPxClasses.ASPxHttpHandlerModule, DevExpress.Web.v12.1, Version=12.1.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" verb="GET" path="DX.ashx" name="ASPxHttpHandlerModule" preCondition="integratedMode" />
        </handlers>
      </system.webServer>
      <entityFramework>
        <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
      </entityFramework>
      <devExpress>
        <themes enableThemesAssembly="true" styleSheetTheme="" theme="" />
        <compression enableHtmlCompression="false" enableCallbackCompression="true" enableResourceCompression="true" enableResourceMerging="true" />
        <settings rightToLeft="false" />
        <errors callbackErrorRedirectUrl="" />
      </devExpress>
    </configuration>

    Thursday, November 8, 2012 5:25 PM

Answers

  • Unless I missed something you want to use ApplicationServices3 connection string ? (I assumed this as you shown us your web.config file that includes a connection string named this way).

    Anyway I suggest first to just show the db.Database.ConnectionString value to see what is the connection your code is really using at this time. From there you'll can easily try ApplicationServices3. For now you are using a database name but then it DOES use the local instance (and likely even LocalDb) which is perhaps not what you really want ? (you want to use the USCMPDEPC44 database server or is this the name of your own machine ?)

    Anyway start first by showing db.Database.ConnectionString to see the connection string you are currently using and can see if it matches what you want...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".






    • Edited by Patrice ScribeMVP Friday, November 9, 2012 10:15 AM
    • Marked as answer by jamesiw Friday, November 9, 2012 3:27 PM
    Friday, November 9, 2012 9:50 AM

All replies

  • Hi,

    The parameter is the name of the connection string, not the database name so you likely end up with the default connection string which is likely the LocalDb engine : http://msdn.microsoft.com/en-us/library/hh510202.aspx

    The quickest way to make sure where you are working is likely to get the Database.ConnectionString from your context to see what is the actual connection you are using...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    • Marked as answer by jamesiw Friday, November 9, 2012 4:00 PM
    • Unmarked as answer by jamesiw Friday, November 9, 2012 4:00 PM
    Thursday, November 8, 2012 5:43 PM
  • Hi,

      Do you mean the parameter found passed through base in the BenefitsContext class?

    (just making sure - that's a call to the parent constructor, right?)

    Thursday, November 8, 2012 8:01 PM
  • An important note-

    In the tutorial I found, which someone else at work followed, the parameter in

    base was given as the database name.  And he was able to find his database.  He used the database

    name for a console project and I used the database name in a console project, and my console project had this problem and his didn't.  We even followed the exact same tutorial (which this current code I'm posting is based on) and I got this problem and he didn't.  The main difference between the way we did things is he is using SQLExpress and I am using SQL Server 2012.  (This is a work practice test which we are allowed to get help on.)

    Thursday, November 8, 2012 8:14 PM
  • By SQL Server 2012, I mean not the Express version
    Thursday, November 8, 2012 8:24 PM
  • Oh yeah, but for my console version, I did not supply any connection string in the web.config, but neither did the other person and I still did it almost exactly like the other guy (except that my poco classes were in their own .cs file and that namespace was included in the file where the main function was) and my database was not showing up, even though data seemed to be recorded in a database.
    Thursday, November 8, 2012 8:31 PM
  • Unless I missed something you want to use ApplicationServices3 connection string ? (I assumed this as you shown us your web.config file that includes a connection string named this way).

    Anyway I suggest first to just show the db.Database.ConnectionString value to see what is the connection your code is really using at this time. From there you'll can easily try ApplicationServices3. For now you are using a database name but then it DOES use the local instance (and likely even LocalDb) which is perhaps not what you really want ? (you want to use the USCMPDEPC44 database server or is this the name of your own machine ?)

    Anyway start first by showing db.Database.ConnectionString to see the connection string you are currently using and can see if it matches what you want...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".






    • Edited by Patrice ScribeMVP Friday, November 9, 2012 10:15 AM
    • Marked as answer by jamesiw Friday, November 9, 2012 3:27 PM
    Friday, November 9, 2012 9:50 AM
  • That did it.  I checked the connection string and it was still giving the Default SQL Express connection string,

    so I supplied ApplicationServices3 (defined in web.config above for those who are trying to learn from this example)

    as the parameter to the base constructor called from the BenefitsContext constructor, and it works.

    Thanks a lot!

    Friday, November 9, 2012 3:31 PM