0

I'm having a bit of a mess around at the moment trying to build a console app where I can insert some data into my database and output the sql to a file as an extra. The idea being is that I could use these for data migrations or something similar.

I have several lookup tables that I'd like to pre-populate with data and I think this is nicer than writing out the sql by hand. If someone needs to look up a value it'll be right there in the code.

I'm using EF Tracing and what I've got so far allows me to log the sql entity framework creates by doing the following

I've hooked into their action to get access to the tracing

private void WriteSqlToString(CommandExecutionEventArgs args)
{
    sqlTrace.Append(args.ToTraceString());
}

Which gives something like this

insert [dbo].[Members]([EmailAddress], [FirstName], [LastName])
values (@0, @1, @2)
select [Id]
from [dbo].[Members]
where @@ROWCOUNT > 0 and [Id] = scope_identity()
-- @0 (dbtype=String, size=60, direction=Input) = "email@email.com"
-- @1 (dbtype=String, size=-1, direction=Input) = "TestFirstName"
-- @2 (dbtype=String, size=-1, direction=Input) = "TestLastName"

I'll be the first to hold my hand up because I'm not good at writing sql by hand. I've messed about with this a little to try and get it to execute in sqlserver express but keep getting errors because the variables aren't declared.

Do folk think it's worth continuing trying to get this to work or am I missing a better solution here?

Here's some more of the code for reference

public void CreateSqlFile()
{
    using (MyContext context = tracingContextFactory.Create(WriteSqlToString))
    {
        Migrate(context);
        context.SaveChanges();
    }

    StreamWriter file = new StreamWriter(migrationPath);
    file.WriteLine(sqlTrace.ToString());
    file.Close();
}
Neil
  • 5,179
  • 8
  • 48
  • 87
  • If you want to be a programmer, it's worth your while to learn how to write sql. I've heard good things about the book, Teach Yourself SQL in 10 Minutes. – Dan Bracuk Oct 13 '13 at 15:31
  • I know SQL to a reasonable level but on a day to day basis I would say that I write some maybe once every 6 months. This is just a case of data entry so why not try and write a program so that I don't have to write the sql and also have it go through validation of the compiler and EF's in built data validation provided by attributing? Also the added bonus of proper intellisense. But hey, if manual data entry is your thing then have at it. – Neil Oct 13 '13 at 15:40

1 Answers1

1

The SQL generated from EF is functional, but it is normally overly complex and badly formatted.

Which is fine, for EF, because no-one ever sees the SQL.

However, if you're going to use or try to modify the SQL, I'd go for a different route. If you're trying to do data migrations, for example, I'd look at SQL Server Integration Services (SSIS).

Edit

To generate data to transfer between servers, in SQL Server management studio,

  • Right click on the database,
  • Click generate scripts.
  • Select your table.
  • On the next screen, choose Advanced Options
  • In "types of data to script", choose "Data only". (or Schema and Data, as appropriate)
  • Continue to the end of the wizard
  • Success.
podiluska
  • 50,950
  • 7
  • 98
  • 104
  • I'm really only interested in it being functional. For instance I have a lookup table which has 200 entries. Rather than typing that out in sql I'd like to be able to define that in code then output the sql to a file which will run as part of my database migrations. I doubt many folk would be reading it. – Neil Oct 13 '13 at 15:27
  • Thanks, that's a good solution, just a shame it can't really be automated – Neil Oct 13 '13 at 15:50
  • @Neil It can, if you're so inclined... See http://stackoverflow.com/questions/3488666/how-to-automate-script-generation-using-smo-in-sql-server – podiluska Oct 13 '13 at 15:52