Create database from SQL script from .NET 6 WinForm application

Question

Hi All,

We recently started working on a .NET 6 WinForm application which will be deployed locally on the client machine.

This application will depend on the SQL database for persisting data.

The SQL instance will be already available on the machine (but not the database).

So, my question is what is the best way to create an empty database from the application?

(we have a SQL script for database creation, but not sure how to execute it)

It's important that we can delete/recreate the database from the application as these actions can occur often. (hence we need to provide an option in the app instead of external solutions)

For some extra background, we use Entity Framework Core to interact with the database once the database is configured.

Any pointers, assistance or link which provides insights on this scenario will be highly appreciated.

Answer 1

Hi @Wadiwala, Rohan ，Welcome to Microsoft Q&A.

You have several ways to achieve your needs.

One way is to use Entity Framework Core to execute your sql script.

Install the correct nuget package

1. Create a DbContext class that inherits from . This class will represent the application's data model and provide methods for interacting with the database. Here is an example: DbContext

using Microsoft. EntityFrameworkCore;

public class YourDbContext : DbContext
{
     public DbSet<YourEntity> YourEntities { get; set; }

     protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
     {
         optionsBuilder.UseSqlServer("your_connection_string_here");
     }
}

Use ExecuteSqlRaw to execute your sql statement.

public void CreateDatabase()
{
     using (var context = new YourDbContext())
     {
         context.Database.ExecuteSqlRaw("your_sql_script_here");
     }
}

You can of course directly open localsqlserver to create your database.

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The following uses Entity Framework Core, with two models (keeping it simple but with related tables).

The two tables defined in the DbContext

internal partial class Context : DbContext
{
    public virtual DbSet<ContactType> ContactType { get; set; }
    public virtual DbSet<Contacts> Contacts { get; set; }

A class kept simple to delete the database if it exists than creates the database and populates it with some data. Note that in this case Contacts model depends on the ContactType model which when created has new keys so when populating Contacts we know the ContactTypeIdentifier which points back to ContactType. There is no exception handling which is easy to add and if needed I can provide that too.

public class InitializeDatabase
{
    public static void Create()
    {
        using var context = new Context();
        // delete the database if it exists
        context.Database.EnsureDeleted();
        // create an empty database according to the models
        context.Database.EnsureCreated();

        List<ContactType> contactTypes = new()
        {
            new () { ContactTitle = "Accounting Manager" },
            new () { ContactTitle = "Owner" },
            new () { ContactTitle = "Sales Agent" },
            new () { ContactTitle = "Sales Manager" }
        };

        context.AddRange(contactTypes);
        context.SaveChanges();

        List<Contacts> contacts = new()
        {
            new () { ContactTypeIdentifier = 2, FirstName = "Karen", LastName = "Payne" },
            new () { ContactTypeIdentifier = 1, FirstName = "Mary", LastName = "Jones" },
            new () { ContactTypeIdentifier = 4, FirstName = "John", LastName = "Gallagher" },
            new () { ContactTypeIdentifier = 2, FirstName = "Kim", LastName = "Jones" },
        };

        context.AddRange(contacts);
        context.SaveChanges();
    }
}