Custom EntityFramework DbContext with VB.NET – Code first approach

First of all, let me say that I have used EntityFramework many times already but I want to know its inner workings and decided to do it for the starters. That is why I created this blog to share my experience when I did it in vb.net.For the remainder of this blog EF is EntityFramework.

So, now let’s get EF from nuget using package manager console in visual studio. At the time of this writing I am using Visual Studio 2013.

1. Go to package manager console.
PackageManagerConsole

2. Install the latest EF package which currently is 6.1.1.

PackageManagerConsoleConsole

3. Time to create our DbContext. What Im doing here is that I am inheriting from System.Data.Entity.DbContext,

Imports System.Data.Entity
Public Class EFContext
     Inherits DbContext
End Class

4. Lets Assign our connection string to our context.

Public Sub New()
     MyBase.New(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
End Sub

5. Lets create our entity

Public Class Users
     Property UserId As Integer
     Property Username As Integer
     Property FirstName As String
     Property LastName As String
End Class

6. Add our entity to our context

Private _systemUsers As DbSet(Of Users)
Public Property SystemUsers() As DbSet(Of Users)
     Get
          Return _systemUsers
     End Get
     Set(value As DbSet(Of Users))
          _systemUsers = value
     End Set
End Property

7. And finally lets override OnModelCreating method of our base class DbContext. Why I am doing this? Because I am telling our custom DbContext that I have an existing database and I don’t what you to touch it.

Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
     MyBase.OnModelCreating(modelBuilder)
     Database.SetInitializer(Of EFContext)(Nothing)
End Sub

And that is pretty much about it. You now have your custom DbContext using the code first approach.

The complete source is available in github. https://github.com/francorobles/Blog/tree/master/EntityFrameworkVB

Advertisements

Saving Blob Files direct to SQL Server

The common thinking of a developer if asked for file upload and download component is to put it in a shared directory and upload it as part of a file system. But there is a more neater way of doing it and Sharepoint does it. Now, ill show you how I do it with ASP.NET.

First lets setup our page to support vb.net function call from client side using PageMethods.

.....
<form id="frmStrive" runat="server">
<asp:ScriptManager ID="scriptManager" runat="server" EnablePageMethods="true" />
<asp:FileUpload ID="fileUpload" runat="server" Width="100%" onchange="PutDescOnParent(this.value);"/>
...

right after our form tag is we created a scriptmanager and the most important thing is that we set EnablePageMethods=”true” which will do the trick.

Next we set up our client side code.

function onUploadClick(){
            if ($j("#txtUploadDesc").val() == '') {
                alert("Please put a description for this file.");
                return;
            }
    
            var vaild = fileUpload.value.length > 0;
            if(vaild){
                //  disable the upload button
                $get('upload').disabled = 'disabled';
                
                //  update the message
                updateMessage('info', 'Initializing upload ...');
                PageMethods.SaveFileToDb(document.getElementById("txtUploadDesc").value);
                //  submit the form containing the fileupload
                
                form.submit();
....

Now if you’ll notice on the 14th line i called PageMethods.SaveFileToDb where it accepts one parameter and call a server side code. Lets see what it does from behind.

<System.Web.Services.WebMethod()> _
    <System.Web.Script.Services.ScriptMethod()> _
    Public Shared Sub SaveFileToDb()
        Dim files As New System.Collections.Generic.Dictionary(Of String, String)
        files = DirectCast(HttpContext.Current.Session("Files"), System.Collections.Generic.Dictionary(Of String, String))

        For Each kvp As KeyValuePair(Of String, String) In files
            Using myConnection As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection2").ConnectionString)
                Const SQL As String = "INSERT INTO [tblFiles] (filename, filedescription, mainfile,parent) VALUES (@fileName, @fileDescription, @mainFile,@parent)"
                Dim myCommand As New System.Data.SqlClient.SqlCommand(SQL, myConnection)
                myCommand.Parameters.AddWithValue("@fileName", kvp.Key)
                myCommand.Parameters.AddWithValue("@fileDescription", kvp.Value)
                Dim im As New _HR001
                Dim fle As New System.IO.FileInfo(im.GetPath(kvp.Key))
                im.Dispose()
                Dim imageBytes(fle.Length) As Byte
                Dim s As System.IO.Stream
                s = fle.OpenRead
                s.Read(imageBytes, 0, imageBytes.Length)
                myCommand.Parameters.Add("@mainFile", Data.SqlDbType.VarBinary)
                myCommand.Parameters("@mainFile").Value = imageBytes
                myCommand.Parameters.AddWithValue("@parent", New _HR001().GetGuid())
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                myConnection.Close()
            End Using
        Next kvp
    End Sub

Now what this block of code does is that it simply retrieves the path of the file(s) the we temporarily store on a session then inserts those files to a particular table which in my case named tblFiles containing a varbinary(max) field which is the container of binary files. Oh by the way during the time we started our session, we created a temporary directory on the server which is identified via creating a unique id and is achieved by the below line.

Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
        Dim dir As New System.IO.DirectoryInfo(Server.MapPath("~/uploads/"))
        dir.CreateSubdirectory(Session("TempDir"))
        Session("Files") = New System.Collections.Generic.Dictionary(Of String, String)
End Sub 

NOTE: In sql server 2000, you can upload up to 8mb files only, which is upgraded in sql server 2005 up. In sql server 2005 up? It depends on your disk space. Now let me show you the structure of my table.

You might be asking, after saving to sql server how will i retrieve it? Here’s how.

Using myConnection As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection2").ConnectionString)

                Const SQL As String = "SELECT * from tblFiles where fileId=@fileId"
                Dim myCommand As New System.Data.SqlClient.SqlCommand(SQL, myConnection)
                myCommand.Parameters.AddWithValue("@fileId", HttpContext.Current.Request.QueryString("fileId"))

                myConnection.Open()
                Dim myReader As System.Data.SqlClient.SqlDataReader = myCommand.ExecuteReader

                If myReader.Read Then

                    Response.ClearContent()
                    Response.AddHeader("Content-Disposition", "attachment; filename=""" & myReader.Item("fileName") & """")

                    Dim bw As New System.IO.BinaryWriter(Response.OutputStream)
                    bw.Write(DirectCast(myReader("mainFile"), Byte()))
                    bw.Close()

                    Response.End()

                End If

                myReader.Close()
                myConnection.Close()


            End Using

See the picture? Its not quite complex. Have to continue doing my project. My next blog will be about how to convert a lousy windows forms to WPF the silverlight way.