unable to add data to SQL database using entity framework in windows service

Question

I wrote a windows service for a project in which I needed to store some data in a database. I created the table with the required columns and created an entity model from the database. Context and mapping are done automatically by VS 2012. In order to test if the data is being saved, I hard coded some values and ran the service but the data doesn't get saved in the database.

Here is a sample service I wrote to test this out:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading.Tasks;

namespace serviceWithDatabase
{
    public partial class testService : ServiceBase
    {
        Database1Entities db = new Database1Entities();

        public testService()
        {
            InitializeComponent();
            test();
        }

        protected override void OnStart(string[] args)
        {

        }

        public void test()
        {
            Table t = new Table();
            t.ticker = "goog";
            t.Day1 = 1234;
            t.Day2 = 4567;
            t.Day3 = 7890.56;
            db.Tables.Add(t);

            db.SaveChanges();

        }

        protected override void OnStop()
        {

        }
    }
}

model for the table:

    namespace serviceWithDatabase
{
    using System;
    using System.Collections.Generic;

    public partial class Table
    {
        public int Id { get; set; }
        public string ticker { get; set; }
        public Nullable<double> Day1 { get; set; }
        public Nullable<double> Day2 { get; set; }
        public Nullable<double> Day3 { get; set; }
    }
}

App.config code:

<?xml version="1.0" encoding="utf-8"?>
<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=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <connectionStrings>
    <add name="Database1Entities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(LocalDB)\v11.0;attachdbfilename=|DataDirectory|\Database1.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>
</configuration>

I can manually go into the database via VS 2012 and add data but it won't save data via the windows service. Anyone have any suggestions ?

Your connection string shows that you're using integrated security, so have you checked that the account that the service is running under has access rights to the database? — DeanOC
– DeanOC, Commented Feb 9, 2014 at 23:44
yes, its the only account that's on this computer. That connection string was added by VS 2012 and I have updated a db using entity framework on this computer in different projects. — user3290903
– user3290903, Commented Feb 9, 2014 at 23:55
As you know, db.savechanges() returns a int, which represents the number of rows updated. I made it output that int to a file and the number was 1. So it is updating the table as per the entity framework but the database doesn't reflect that change. I have tried surrounding it with try and catch (when there is an exception outputs the error to a file) but there aren't any errors when saving. — user3290903
– user3290903, Commented Feb 10, 2014 at 0:12

user3290903 · Accepted Answer · 2014-03-12 05:30:21Z

1

the database was stored within the project and VS was updating the wrong database. When you place the database outside of the project folder (e.g. on your desktop) it works perfectly fine. This issue cost me a weekend lol. Hope it helps others :)

answered Mar 12, 2014 at 5:30

user3290903

211 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jocull · Accepted Answer · 2014-03-07 15:23:01Z

0

Try deleting the App.config and passing in your connection string directly some other way.

The App.config seemed to prevent compilation for me.

answered Mar 7, 2014 at 15:23

jocull

21.4k25 gold badges110 silver badges154 bronze badges

Collectives™ on Stack Overflow

unable to add data to SQL database using entity framework in windows service

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related