-2

Hi am trying to develop the web version of my desktop application using C# asp.net core, but am not sure of how to handle the databases, Here is what i want to achieve, my desktop application is a Point of sales system developed using VB whereby the application user are able to manage multiple stores and every store has it own database, upon the creation of the store the database script is executed which now create the database for the New store. Below is my sample code to create the database.

 'Get DB NAme
        Dim DbNumber As Integer
        ConnectTools()
        cmd.CommandText = "select MAX(StoreCount) as cntt from tblStores"
        rd = cmd.ExecuteReader
        rd.Read()
        If IsDBNull(rd.Item("cntt")) Then
            DbNumber = "1"
        Else
            DbNumber = rd.Item("cntt") + 1
        End If
        rd.Close()
        DatabaseName = "AutemStore" & DbNumber

        cmd.Connection = conn
        'conn.Open()
        conn.ChangeDatabase("Master")
        If conn.State = ConnectionState.Open Then conn.Close()
        conn.Open()
        cmd.CommandText = "Create Database " & DatabaseName
        cmd.ExecuteNonQuery()
        'Save DB Name
        My.Settings.DatabaseName = DatabaseName
        My.Settings.Save()
        'Run DB Script 

        conn.ChangeDatabase(My.Settings.DatabaseName)
        cmd.CommandText = ReadFile(Application.StartupPath & "\dstore.sql")
        cmd.CommandType = CommandType.Text
        cmd.ExecuteNonQuery()

I believe there is a way this task i just explained can be done using Asp.net core but I don't yet have a clue on how to get it done, please is there any suggestion on how to do this or alternative way to get it done. Thanks in advance.

Austin Mrakpor
  • 51
  • 1
  • 9
  • Seems you're using ADO.NET. Note it is available in ASP.NET Core too. However, we don't know your `ConnectTools()` and further details (e.g. the `ReadFile()`), so we can't provide a complete list code for you. But be aware the API is almost the same, you can easily rewrite your VB.NET to C#. – itminus Feb 17 '20 at 08:26
  • Thanks for your reply, the sample code I shared is the one I did on desktop application, right now I want to using ASP.NET CORE and ENTITY FRAMEWORK, it is a multitenant application am trying to build, but am not sure of how to do that with these tool. – Austin Mrakpor Feb 17 '20 at 09:48

1 Answers1

0

Since you're trying to use EF(or maybe EFCore), I assume you've set up the data Models and injected the DbContext as _context. (If you have not, you could follow the official docs to get started).

After that, you could rewrite you VB.NET + ADO.NET code using EFCore like this:

  1. The first query of select MAX(StoreCount) as cntt from tblStores can be done by 

    var cntt = _context.Stores.Max(t => t.StoreCound);

  2. The 2nd command that creates database can be rewritten as:

    var sql= "Create Database @dbname";
var dbname = new SqlParameter("@dbname", "The_DB_Name");
_context.Database.ExecuteSqlCommand(sql, dbname);
itminus
  • 23,772
  • 2
  • 53
  • 88
  • Thank you so much this is really helpful, I have models and DbContext injected, the last part is the database script execution, can you say something on that? – Austin Mrakpor Feb 17 '20 at 10:21
  • @AustinMrakpor You could always execute raw sql statements by `_context.Database.ExecuteSqlCommand(...)`. To prevent SQL injection, we should use parameterized query, that's why I create a @dbname parameter. See [official docs](https://learn.microsoft.com/en-us/ef/core/querying/raw-sql) for more details. See also [this thread for raw sql execution](https://stackoverflow.com/a/53912777/10091607) – itminus Feb 18 '20 at 00:57