locked
how to create a Model first database in asp.net webforms to log system events? RRS feed

  • Question

  • User2142845853 posted

    In MVC one simply creates a Model with all the columns

    int ID {get; set;}

    [length = 255]

    string Name {get; set;}

    etc

    But in VB.net WebForms?  The simplest thing, but the goal is to store logging information, if a user signs in, send that name w/timestamp to this systemlog database table.  There is space on the SQL server, already have the path/login.  Trying to use the ADD and then various choices like SQL server or entity framework code first its like the wizard is incomplete, where does the Model go?  This is only storing a few fields, 

    Imports System.ComponentModel.DataAnnotations
    
    Public Class LogSystemEvents
        Public Property Id As Int32
    
        <StringLength(128, ErrorMessage:="Maximum length is 128 Characters")>
        Public Property Username As String
    
        '<StringLength(128, ErrorMessage:="Maximum length is 128 Characters")>
        Public Property Logintime As DateTime ' As String
    
        <StringLength(128, ErrorMessage:="Maximum length is 128 Characters")>
        Public Property Description1 As String
    
        <StringLength(255, ErrorMessage:="Maximum length is 255 Characters")>
        Public Property Event1 As String
    
    End Class
    

    so business logic would handle talking to the sql database, and methods would call the BL side and pass 5 parameters, like with ajax and get status back.  But where is an example of that? 

    Sunday, March 31, 2019 4:48 PM

Answers

  • User2142845853 posted

    
    
    
        Protected Sub PopulateStudentGrid()
    
            'Turn on the Dbcontext
            Dim Db As New ContosoModel()
    
            'Query the database
            Dim results As New List(Of Student)
            results = Db.Students.ToList()
    
            'Bind the query results to the GridView
            StudentGrid.DataSource = results
            StudentGrid.DataBind()
    
        End Sub
    
    End Class

    I have an applicationdbcontext and its using the DefaultConnection string.  I use this similar code, and if I say 

    results = db.

    at this point it lists all of the items in that model, but your example has it as one whole unit.  as if it needs a class within a class so that the entire block of variables can be referenced as one unit, and then that can be further referenced for the individual elements which is what is working so far

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 2, 2019 1:54 PM

All replies

  • User475983607 posted

    Trying to use the ADD and then various choices like SQL server or entity framework code first its like the wizard is incomplete, where does the Model go?

    A model is a class.  Add a model folder to your project to keep the models together.

    like with ajax and get status back.  But where is an example of that? 

    Are you looking for an example of using jQuery/AJAX in Web Forms?

    Sunday, March 31, 2019 6:00 PM
  • User2142845853 posted

    Looking for any example.  The model is defined, am trying to generate the physical table from the code; but it can be done manually;  And then find the mechanism to write and read to that sql table.  think Ive used ajax in the past to send the request and fill the table but dont have any reference examples. 

    If a user logs in, in the LogIn method, onSuccess()   code would call a method that would take the parameters and pass to the business logic that only did I/O to the sql table.  Unless there's a better way.   Then an admin page can call up that info with a search feature.

    Sunday, March 31, 2019 6:08 PM
  • User475983607 posted

    Looking for any example.  The model is defined, am trying to generate the physical table from the code; but it can be done manually;  And then find the mechanism to write and read to that sql table.  think Ive used ajax in the past to send the request and fill the table but dont have any reference examples. 

    If a user logs in, in the LogIn method, onSuccess()   code would call a method that would take the parameters and pass to the business logic that only did I/O to the sql table.  Unless there's a better way.   Then an admin page can call up that info with a search feature.

    Web Forms jQuery/AJAX example.

    <%@ Page Language="vb" AutoEventWireup="false" CodeBehind="AjaxDemo.aspx.vb" Inherits="VbWebApplication.AjaxDemo" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
        <script src="Scripts/jquery-3.3.1.js"></script>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:Button ID="Button1" runat="server" Text="Button" />
            </div>
        </form>
    
        <script>
            $('#<%=Button1.ClientID%>').click(function(e) {
                e.preventDefault();
                var data = {
                    Log: {
                        Id: 1,
                        Username: "username",
                        Logintime: "03/31/2019",
                        Description1: "Description",
                        Event1: "Event"
                    }
    
                }
                    $.ajax({
                        type: "POST",
                        url: '/AjaxDemo.aspx/LogData',
                        data:  JSON.stringify(data),
                        dataType: 'json',
                        contentType: "application/json; charset=utf-8",
                    }).done(function (data) {
                        console.log(data.d);
                    });
            });
        </script>
    </body>
    </html>
    
    Imports System.Web.Services
    
    Public Class AjaxDemo
        Inherits System.Web.UI.Page
    
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
        End Sub
    
        <WebMethod>
        Public Shared Function LogData(ByVal Log As LogSystemEvents) As LogSystemEvents
            'Invoke BL
            Return Log
        End Function
    
    End Class
    Imports System.ComponentModel.DataAnnotations
    
    Public Class LogSystemEvents
        Public Property Id As Int32
    
        <StringLength(128, ErrorMessage:="Maximum length is 128 Characters")>
        Public Property Username As String
    
        Public Property Logintime As DateTime ' As String
    
        <StringLength(128, ErrorMessage:="Maximum length is 128 Characters")>
        Public Property Description1 As String
    
        <StringLength(255, ErrorMessage:="Maximum length is 255 Characters")>
        Public Property Event1 As String
    
    End Class

    Code first works the same in Web Forms as it does in MVC.  So, I'm not sure what issue you are struggling with.

    Sunday, March 31, 2019 6:47 PM
  • User2142845853 posted

    Great!  This all runs just fine but cannot get LogData to be hit by breakpoint

    Sunday, March 31, 2019 8:44 PM
  • User475983607 posted
    The code posted is a working and tested snippet. Please share your sample code that is not working as expected.
    Sunday, March 31, 2019 11:35 PM
  • User2142845853 posted

    I traced the ajax code here is the info at the very END of the $.ajax call, 

    }).done(function (data) { ddata = {Message: "Authentication failed.", StackTrace: null, ExceptionType: "System.InvalidOperationException"}
    
                        console.log(data.d);

    It says authentication failed?  In the folder I disabled authentication, and it is a logged in user

    https://stackoverflow.com/questions/20032240/authentication-failed-during-call-webmethod-from-jquery-ajx-with-aspnet-friendly

    applying this, now it works.  lands on the debug point on the public shared function LogData

    Sunday, March 31, 2019 11:41 PM
  • User475983607 posted

    rogersbr

    It says authentication failed?  In the folder I disabled authentication, and it is a logged in user

    If this is a login request then the request is not authenticated and the expected result.

    rogersbr

    https://stackoverflow.com/questions/20032240/authentication-failed-during-call-webmethod-from-jquery-ajx-with-aspnet-friendly

    applying this, now it works.  lands on the debug point on the public shared function LogData

    The SO post is refers to friendly ASPX pages which is more related to MVC style route requests. Basically the ".aspx" bits are ignored in the URL.  This is an application configuration.   If you prefer to use friendly URLs then remove the file extension in the AJAX URL.

    Monday, April 1, 2019 12:36 AM
  • User2142845853 posted

    Well I tried many alternatives to fix the authentication error problem including every possible path with and without .aspx.  but for now its working.

    The database has a table, but how to get from the code into the table?  in MVC you can work with the db adapter but in webforms how to go from the code, in the function to send data into the method that talks to the database?

    Monday, April 1, 2019 1:45 AM
  • User-1174608757 posted

    Hi rogersbr, 

    in webforms how to go from the code, in the function to send data into the method that talks to the database

    In webform we could use ado.net and EF to send data into database which you could insert or delete or update data in table. 

    For using EF, you could  add ADO.NET Entity Data Model then add connection to database ,else three are two options we could set 

    1. Generate from database To generate a model from existing database.
    2. Empty Model To generate a database from model.

    Finally ,In aspx we could use model to operate database.Here is the link , you could just directly follow the steps to accomplish it.

    https://www.c-sharpcorner.com/article/introduce-entity-framework-with-ado-net-entity-data-model/

    For using ado.net 

    Firstly you should add connections to database in web.config by using code as below:

    <configuration>
      <connectionStrings>
        <add name="mssqlserver" connectionString="Data Source=localhost;Initial Catalog=MyDatabase2;Integrated Security=True" providerName="System.Data.SqlClient" />
      </connectionStrings>

    Then ,you could add connections to database then use sql query code like insert ,delete so that you could operate table in database. which as below:

    Private Sub BindGrid()
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim query As String = "SELECT * FROM Customers"
        Using con As SqlConnection = New SqlConnection(constr)
            Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    GridView1.DataSource = dt
                    GridView1.DataBind()
                End Using
            End Using
        End Using
    End Sub

    Here is the link, I hope it could help you.

    https://www.aspsnippets.com/Articles/Simple-CRUD-operations-in-ASPNet-Web-Forms-using-C-and-VBNet.aspx

    Best Regards

    Wei
     

    Monday, April 1, 2019 8:40 AM
  • User2142845853 posted

    Thank you Wei,

    It is helpful but also I want to directly send data to the SQL database. in MVC we create the dbapplicationcontext and just specify a connection string, create the new context  usually as just 'db' which represents the model -> table.  so wed say applicationcontext db = new applicationcontext() now I can work with db, like:

    db.somereport.Add(somereportinstance);

    db.saveChanges();

    so now I took the instance of the model and wrote it to the sql table.  But thats c# in MVC where its easy, modern.   This is vb.net and its the old webforms.  Do you (or anyone) know what the equivalent would be to talk to the database? or even to some stored procedure?

    thanks!

    Monday, April 1, 2019 2:20 PM
  • User475983607 posted

    so now I took the instance of the model and wrote it to the sql table.  But thats c# in MVC where its easy, modern.   This is vb.net and its the old webforms.  Do you (or anyone) know what the equivalent would be to talk to the database? or even to some stored procedure?

    thanks!

    You are making assumptions again.  MVC and Web Forms are part of same ASP.NET framework.   Entity Framework and ADO.NET works exactly the same in Web Forms as in MVC.  the following link explains how to create a DbContext and scaffold an existing DB.

    Create a database

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/database-first-development/setting-up-database

    Create the ADO.NET Entity Data Model

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/database-first-development/creating-the-web-application

    While the instructions are for MVC it still works with Web Forms.  I used the SSMS to create the database (first link)

    All you have to do is NuGet EF 6.  Then add the "ADO.NET Entity Data Model" template (second link).  Be sure to choose the Code First option.

    Here an example of getting data and populating a DataGrid

    <%@ Page Title="" Language="vb" AutoEventWireup="false" MasterPageFile="~/Site.Master" CodeBehind="StudentPage.aspx.vb" Inherits="VbWebApplication.StudentPage" %>
    <asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
        <asp:GridView ID="StudentGrid" runat="server"></asp:GridView>
    </asp:Content>
    Public Class StudentPage
        Inherits System.Web.UI.Page
    
    
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            If Not Page.IsPostBack Then
                PopulateStudentGrid()
            End If
        End Sub
    
    
    
        Protected Sub PopulateStudentGrid()
    
            'Turn on the Dbcontext
            Dim Db As New ContosoModel()
    
            'Query the database
            Dim results As New List(Of Student)
            results = Db.Students.ToList()
    
            'Bind the query results to the GridView
            StudentGrid.DataSource = results
            StudentGrid.DataBind()
    
        End Sub
    
    End Class

    Monday, April 1, 2019 4:39 PM
  • User753101303 posted

    Hi,

    You can use EF as well in Web Forms. I'm not sure at which step you are and even if you really need to use Ajax. Rather than going from one topic to another it can be best to close the thread and open a new one.

    For now my understanding is that you just want to log an event each time a user logs into your app ? You are using which authentication API ?

    Monday, April 1, 2019 4:58 PM
  • User2142845853 posted

    Hi,

    You can use EF as well in Web Forms. I'm not sure at which step you are and even if you really need to use Ajax. Rather than going from one topic to another it can be best to close the thread and open a new one.

    For now my understanding is that you just want to log an event each time a user logs into your app ? You are using which authentication API ?

    Using the out of the box Identity Manager that is partly defined with a new project using Individual Accounts.   At first its user name and date they logged in.  Later it will be other info from other methods sending in the time of the event and some info about it.

    Monday, April 1, 2019 8:12 PM
  • User475983607 posted

    Using the out of the box Identity Manager that is partly defined with a new project using Individual Accounts.   At first its user name and date they logged in.  Later it will be other info from other methods sending in the time of the event and some info about it.

    Then you already have a DbContext.  What is stopping you from using the existing DbContext?

    Monday, April 1, 2019 9:39 PM
  • User2142845853 posted

    
    
    
        Protected Sub PopulateStudentGrid()
    
            'Turn on the Dbcontext
            Dim Db As New ContosoModel()
    
            'Query the database
            Dim results As New List(Of Student)
            results = Db.Students.ToList()
    
            'Bind the query results to the GridView
            StudentGrid.DataSource = results
            StudentGrid.DataBind()
    
        End Sub
    
    End Class

    I have an applicationdbcontext and its using the DefaultConnection string.  I use this similar code, and if I say 

    results = db.

    at this point it lists all of the items in that model, but your example has it as one whole unit.  as if it needs a class within a class so that the entire block of variables can be referenced as one unit, and then that can be further referenced for the individual elements which is what is working so far

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 2, 2019 1:54 PM
  • User475983607 posted

    I have an applicationdbcontext and its using the DefaultConnection string.  I use this similar code, and if I say 

    results = db.

    at this point it lists all of the items in that model, but your example has it as one whole unit.  as if it needs a class within a class so that the entire block of variables can be referenced as one unit, and then that can be further referenced for the individual elements which is what is working so far

    I honestly have no idea what you're asking.  Glad it is working though.

    Tuesday, April 2, 2019 2:25 PM
  • User2142845853 posted

    mgebhard

    I honestly have no idea what you're asking.  Glad it is working though.

    So not sure what Im missing.  There is a Model with 5 cast variables that match a table in the database.  There is the instance of something as applicationdbcontext, which invokes the connection string, in this case the right one, "DefaultConnection" in web.config.  so its pointed to the right table.  I can see the columns or the variables individually, but cannot manipulate the item, the instance OF that model.

    I realized I have to try adding the ADO.entity so I have a handle to work with to get/set the data.  Simply cannot send/get from the table

    I was trying your examples, but dbset and dbcontext?  were totally unknown to visual studio and the solution.  No way to use them.  I run the ADO wizard and try to setup something, at the end it fails, cannot create (whatever) when another thing is open in viewer. I close everything.  Now I can see the class like you described and it uses dbset and dbcontext.  typical.  still doesnt work right yet

    Tuesday, April 2, 2019 2:53 PM
  • User475983607 posted

    rogersbr

    it does NOT work and Im hoping this can be done, finished ENDED NOW! Its like trying to push a tissue paper in my shirt pocket, and I put 200 pounds of force upon it, pushing down and it wont move.  Thats what it seems like.

    So not sure what Im missing.  There is a Model with 5 cast variables that match a table in the database.  There is the instance of something as applicationdbcontext, which invokes the connection string, in this case the right one, "DefaultConnection" in web.config.  so its pointed to the right table.  I can see the columns or the variables individually, but cannot manipulate the item, the instance OF that model.

    I realized I have to try adding the ADO.entity so I have a handle to work with to get/set the data.  Simply cannot send/get from the table

    I was trying your examples, but dbset and dbcontext?  were totally unknown to visual studio and the solution.  No way to use them.  I run the ADO wizard and try to setup something, at the end it fails, cannot create (whatever) when another thing is open in viewer. I close everything.  Now I can see the class like you described and it uses dbset and dbcontext.  typical.  still doesnt work right yet

    The previous post shows how to handle an existing DB.  The following is how to handle a new Project using Identity.  Unfortunately, these steps cause the Identity tables to get build then dropped.

    Create a new VB.NET Web Forms Application with the Individual Account option.  Run the app and create an account to create the Identity DB. 

    Note: at this point you can enable and create a migration rather than then running the app to create the account.  This order will fix the drop/recreate behavior.  However, you are probably passed this point.

    Enable-Migrations
    Add-Migration InitialDbContext
    Update-Database

    Add the LogSystemEvent class to your Models folder.  Note: I removed the "s".

    Imports System.ComponentModel.DataAnnotations
    
    Public Class LogSystemEvent
        Public Property Id As Int32
    
        <StringLength(128, ErrorMessage:="Maximum length is 128 Characters")>
        Public Property Username As String
    
        '<StringLength(128, ErrorMessage:="Maximum length is 128 Characters")>
        Public Property Logintime As DateTime ' As String
    
        <StringLength(128, ErrorMessage:="Maximum length is 128 Characters")>
        Public Property Description1 As String
    
        <StringLength(255, ErrorMessage:="Maximum length is 255 Characters")>
        Public Property Event1 As String
    
    End Class

    Open the IdentityModel folder and make the following updates.

    Public Class ApplicationDbContext
        Inherits IdentityDbContext(Of ApplicationUser)
        Public Sub New()
            MyBase.New("DefaultConnection", throwIfV1Schema:=False)
        End Sub
    
        Public Overridable Property LogSystemEvents As DbSet(Of LogSystemEvent)
    
        Protected Overrides Sub OnModelCreating(ByVal modelBuilder As DbModelBuilder)
            modelBuilder.Entity(Of LogSystemEvent)().HasKey(Function(e) e.Id)
    
            MyBase.OnModelCreating(modelBuilder)
    
        End Sub
    
        Public Shared Function Create() As ApplicationDbContext
            Return New ApplicationDbContext()
        End Function
    End Class

    Be sure to add the Imports

    Imports System.Data.Entity

    Run the Enable migrations command in the Package Manager Console

    Enable-Migrations

    Create a migration 

    Add-Migration LogSystemEvent

    Update the database.  This step will drop and recreate the Identity tables.

    Update-Database

    If you have user accounts in the DB and want to keep them then write an INSERT script to insert the users once the tables are recreated.  

    Tuesday, April 2, 2019 5:55 PM
  • User2142845853 posted

    rogersbr

    it does NOT work and Im hoping this can be done, finished ENDED NOW! Its like trying to push a tissue paper in my shirt pocket, and I put 200 pounds of force upon it, pushing down and it wont move.  Thats what it seems like.

    So not sure what Im missing.  There is a Model with 5 cast variables that match a table in the database.  There is the instance of something as applicationdbcontext, which invokes the connection string, in this case the right one, "DefaultConnection" in web.config.  so its pointed to the right table.  I can see the columns or the variables individually, but cannot manipulate the item, the instance OF that model.

    I realized I have to try adding the ADO.entity so I have a handle to work with to get/set the data.  Simply cannot send/get from the table

    I was trying your examples, but dbset and dbcontext?  were totally unknown to visual studio and the solution.  No way to use them.  I run the ADO wizard and try to setup something, at the end it fails, cannot create (whatever) when another thing is open in viewer. I close everything.  Now I can see the class like you described and it uses dbset and dbcontext.  typical.  still doesnt work right yet

    The previous post shows how to handle an existing DB.  The following is how to handle a new Project using Identity.  Unfortunately, these steps cause the Identity tables to get build then dropped.

    Create a new VB.NET Web Forms Application with the Individual Account option.  Run the app and create an account to create the Identity DB. 

    Note: at this point you can enable and create a migration rather than then running the app to create the account.  This order will fix the drop/recreate behavior.  However, you are probably passed this point.

    Enable-Migrations
    Add-Migration InitialDbContext
    Update-Database

    Add the LogSystemEvent class to your Models folder.  Note: I removed the "s".

    Imports System.ComponentModel.DataAnnotations
    
    Public Class LogSystemEvent
        Public Property Id As Int32
    
        <StringLength(128, ErrorMessage:="Maximum length is 128 Characters")>
        Public Property Username As String
    
        '<StringLength(128, ErrorMessage:="Maximum length is 128 Characters")>
        Public Property Logintime As DateTime ' As String
    
        <StringLength(128, ErrorMessage:="Maximum length is 128 Characters")>
        Public Property Description1 As String
    
        <StringLength(255, ErrorMessage:="Maximum length is 255 Characters")>
        Public Property Event1 As String
    
    End Class

    Open the IdentityModel folder and make the following updates.

    Public Class ApplicationDbContext
        Inherits IdentityDbContext(Of ApplicationUser)
        Public Sub New()
            MyBase.New("DefaultConnection", throwIfV1Schema:=False)
        End Sub
    
        Public Overridable Property LogSystemEvents As DbSet(Of LogSystemEvent)
    
        Protected Overrides Sub OnModelCreating(ByVal modelBuilder As DbModelBuilder)
            modelBuilder.Entity(Of LogSystemEvent)().HasKey(Function(e) e.Id)
    
            MyBase.OnModelCreating(modelBuilder)
    
        End Sub
    
        Public Shared Function Create() As ApplicationDbContext
            Return New ApplicationDbContext()
        End Function
    End Class

    Be sure to add the Imports

    Imports System.Data.Entity

    Run the Enable migrations command in the Package Manager Console

    Enable-Migrations

    Create a migration 

    Add-Migration LogSystemEvent

    Update the database.  This step will drop and recreate the Identity tables.

    Update-Database

    If you have user accounts in the DB and want to keep them then write an INSERT script to insert the users once the tables are recreated.  

    The identity part works great, and on this project already has other peoples' logins.  I do want to use this to generate the script to build the table because it has to be moved eventually;

    As it sits, I created a new Gridview but instead of making the source which shouldve worked but didnt, just defaulted to creating a new sqldatasource1 and pointing to the table, so now the table comes up and populates.  In this case its only reading the table.  

    What I cannot make work no matter what is writing into that table from code.  Even the date, even making the declaration to  create a model instance, write a value to one of the items, then ADD, then SaveChanges() and have it hit the database.  

    Simply cannot get a handle to write with.  have tried adding EF Dbcontext generator, DataSet, ADO.net entity data model, empty SQL database, and cannot solve this.  just polluted the project beyond belief but thanks to version control will back off changes.  

    just some global method to instantiate and write something to the table, thats it...

    Wednesday, April 3, 2019 12:58 PM
  • User475983607 posted

    The identity part works great, and on this project already has other peoples' logins.  I do want to use this to generate the script to build the table because it has to be moved eventually;

    As it sits, I created a new Gridview but instead of making the source which shouldve worked but didnt, just defaulted to creating a new sqldatasource1 and pointing to the table, so now the table comes up and populates.  In this case its only reading the table.  

    What I cannot make work no matter what is writing into that table from code.  Even the date, even making the declaration to  create a model instance, write a value to one of the items, then ADD, then SaveChanges() and have it hit the database.  

    Simply cannot get a handle to write with.  have tried adding EF Dbcontext generator, DataSet, ADO.net entity data model, empty SQL database, and cannot solve this.  just polluted the project beyond belief but thanks to version control will back off changes.  

    just some global method to instantiate and write something to the table, thats it...

    Inserting data in a table is one of the most basic function in web development.  I showed two ways to get started with EF; from an existing DB and from a new DB. 

    Do you need to separate the Identity DB and application DB and need an example?  Do you need an ADO.NET example?

    Wednesday, April 3, 2019 2:24 PM
  • User2142845853 posted

    Thanks.  with the input I was able to recall and apply how to set it up. It is fine now

    Friday, April 5, 2019 6:42 PM