Wednesday, March 16, 2016

Tuesday, March 15, 2016

ASP.NET FormView CRUD with Entity Framework

Most of the examples on FormView Web Server control use DataSource wizard controls such as SqlDatasource or ObjectDataSource when assigning value to the FormView's DataSource property.
However, using those controls have drawbacks such as maintainability. I also found samples out there using ADO.NET. Enough with the chit-chat and let's proceed with coding. I'll post the create table statement, code behind and the aspx markup.

SQL Code:
USE [Books]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[BookDetails](
    [BookSerialNo] [int] IDENTITY(1,1) NOT NULL,
    [BookISBN] [nchar](15) NULL,
    [BookTitle] [varchar](120) NULL,
    [BookAuthor] [varchar](60) NULL,
    [BookPublisher] [varchar](50) NULL,
    [BookCategory] [varchar](20) NULL,
PRIMARY KEY CLUSTERED 
(
    [BookSerialNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
  ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO

CodeBehind:
public partial class FormViewDemo : System.Web.UI.Page
    {
        private BooksEntities _context;
 
        public FormViewDemo()
        {
            _context = new BooksEntities();
        }
 
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                BindFormView();
            }
        }
 
        private void BindFormView()
        {
            var bookRecords = _context.BookDetails.ToList();
            FormViewBookDetails.DataSource = bookRecords;
            FormViewBookDetails.DataBind();
        }
 
        protected void FormViewBookDetails_ItemCommand(object sender, FormViewCommandEventArgs e)
        {
            if (e.CommandName == "Cancel")
            {
                FormViewBookDetails.ChangeMode(FormViewMode.ReadOnly);
            }
            else if (e.CommandName == "Edit")
            {
                FormViewBookDetails.ChangeMode(FormViewMode.Edit);
            }
            else if (e.CommandName == "New")
            {
                FormViewBookDetails.ChangeMode(FormViewMode.Insert);
            }
            else if (e.CommandName == "Delete")
            {
                FormViewBookDetails.ChangeMode(FormViewMode.ReadOnly);
                BindFormView();
            }
        }
        
        protected void FormViewBookDetails_PageIndexChanging(object sender, FormViewPageEventArgs e)
        {
            FormViewBookDetails.PageIndex = e.NewPageIndex;
            BindFormView();
        }
 
        protected void FormViewBookDetails_ModeChanging(object sender, FormViewModeEventArgs e)
        {
            if (e.NewMode == FormViewMode.Insert)
            {
                FormViewBookDetails.AllowPaging = false;
            }
            else if (e.NewMode == FormViewMode.Edit)
            {
                FormViewBookDetails.AllowPaging = false;
                BindFormView();
            }
            else
            {
                FormViewBookDetails.AllowPaging = true;
                BindFormView();
            }
        }
 
        protected void FormViewBookDetails_ItemInserting(object sender, FormViewInsertEventArgs e)
        {
            var item = new BookDetail();
            item.BookISBN = ((TextBox)FormViewBookDetails.FindControl("txtInsertBookISBN")).Text;
            item.BookTitle = ((TextBox)FormViewBookDetails.FindControl("txtInsertBookTitle")).Text;
            item.BookAuthor = ((TextBox)FormViewBookDetails.FindControl("txtInsertBookAuthor")).Text;
            item.BookPublisher = ((TextBox)FormViewBookDetails.FindControl("txtInsertBookPublisher")).Text;
            item.BookCategory = ((TextBox)FormViewBookDetails.FindControl("txtInsertBookCategory")).Text;
            _context.BookDetails.Add(item);
            _context.SaveChanges();
            ResetBinding();
        }
 
        protected void FormViewBookDetails_ItemUpdating(object sender, FormViewUpdateEventArgs e)
        {
            int id = Int32.Parse(FormViewBookDetails.DataKey[0].ToString());
            var result = _context.BookDetails.Find(id);
            if (result != null)
            {
                var item = new BookDetail();
                item.BookSerialNo = id;
                item.BookISBN = ((TextBox)FormViewBookDetails.FindControl("txtBookISBN")).Text;
                item.BookTitle = ((TextBox)FormViewBookDetails.FindControl("txtBookTitle")).Text;
                item.BookAuthor = ((TextBox)FormViewBookDetails.FindControl("txtBookAuthor")).Text;
                item.BookPublisher = ((TextBox)FormViewBookDetails.FindControl("txtBookPublisher")).Text;
                item.BookCategory = ((TextBox)FormViewBookDetails.FindControl("txtBookCategory")).Text;
                _context.Entry(result).CurrentValues.SetValues(item);
                _context.SaveChanges();
                ResetBinding();
            }
        }
 
        private void ResetBinding()
        {
            FormViewBookDetails.ChangeMode(FormViewMode.ReadOnly);
            FormViewBookDetails.AllowPaging = true;
            BindFormView();
        }
 
        protected void FormViewBookDetails_ItemDeleting(object sender, FormViewDeleteEventArgs e)
        {
            if (FormViewBookDetails.DataKey[0] != null)
            {
                var id = Int32.Parse(FormViewBookDetails.DataKey[0].ToString());
                var itemDelete = _context.BookDetails.FirstOrDefault(t => t.BookSerialNo == id);
                if (itemDelete != null)
                {
                    _context.Entry(itemDelete).State = EntityState.Deleted;
                    _context.SaveChanges();
                    ResetBinding();
                }
            }
        }
    }

ASPX Markup:
<asp:FormView ID="FormViewBookDetails" runat="server" AllowPaging="True" DataKeyNames="BookSerialNo" GridLines="Both" ClientIDMode="Static"
      OnItemCommand="FormViewBookDetails_ItemCommand" OnPageIndexChanging="FormViewBookDetails_PageIndexChanging" 
      OnModeChanging="FormViewBookDetails_ModeChanging" OnItemInserting="FormViewBookDetails_ItemInserting" 
      OnItemDeleting="FormViewBookDetails_ItemDeleting" PagerSettings-Mode="NumericFirstLast" OnItemUpdating="FormViewBookDetails_ItemUpdating">
      <headertemplate>
          <table class="" id="tblHeader">
            <tr>
              <td colspan="2">
                  <div id="Header">
                      <asp:label id="lbl" Text="GHK Bookshop FormView Create-Update-Delete Demo" runat="server"/> 
                  </div>                                            
              </td>
            </tr>
          </table>
    </headertemplate>
    <EditItemTemplate>
        <table id="tblEdit">
            <tr>
                <td >Serial # : </td>
                <td>
                    <asp:Label ID="lblBookSerialNo" runat="server" Text='<%# Eval("BookSerialNo") %>' />
                </td>
            </tr>
            <tr>
                <td >ISBN : </td>
                <td>
                    <asp:TextBox ID="txtBookISBN" runat="server" required="required"  Text='<%# Bind("BookISBN") %>'></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td >Title : </td>
                <td>
                    <asp:TextBox ID="txtBookTitle" runat="server"  required="required"   Text='<%# Bind("BookTitle") %>'></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td >Author : </td>
                <td>
                    <asp:TextBox ID="txtBookAuthor" runat="server"   required="required"  Text='<%# Bind("BookAuthor") %>'></asp:TextBox>
                </td>
            </tr>
            <tr>
               <td >Publisher : </td>
                <td>
                    <asp:TextBox ID="txtBookPublisher" runat="server"  required="required"   Text='<%# Bind("BookPublisher") %>'></asp:TextBox>
                </td>
            </tr>
            <tr>
               <td >Category : </td>
                <td>
                    <asp:TextBox ID="txtBookCategory" runat="server"  required="required"   Text='<%# Bind("BookCategory") %>'></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:Button ID="btnUpdate" runat="server" CommandName="Update" Text="Update" />
                    <asp:Button ID="btnCancel" runat="server" CommandName="Cancel" Text="Cancel" UseSubmitBehavior="false" CausesValidation="false"/>
                </td>
            </tr>
        </table>
    </EditItemTemplate>
    <ItemTemplate>
        <table id="tblItem">
            <tr>
                <td>Serial # : </td>
                <td>
                    <asp:Label ID="EmployeeIDLabel" runat="server" Text='<%# Eval("BookSerialNo") %>' />
                </td>
            </tr>
            <tr>
                <td >ISBN : </td>
                <td>
                    <asp:Label ID="LastNameLabel" runat="server" Text='<%# Bind("BookISBN") %>' />
                </td>
            </tr>
            <tr>
                <td >Title : </td>
                <td>
                    <asp:Label ID="FirstNameLabel" runat="server" Text='<%# Bind("BookTitle") %>' />
                </td>
            </tr>
            <tr>
                <td >Author : </td>
                <td>
                    <asp:Label ID="CountryLabel" runat="server" Text='<%# Bind("BookAuthor") %>' />
                </td>
            </tr>
            <tr>
                <td>Publisher :</td>
                <td>
                    <asp:Label ID="NotesLabel" runat="server" Text='<%# Bind("BookPublisher") %>' />
                </td>
            </tr>
            <tr>
                <td>Category :</td>
                <td>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("BookCategory") %>' />
                </td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:Button ID="btnInsert" runat="server" Text="Add" CommandName="New" />
                    <asp:Button ID="btnEdit" runat="server" Text="Edit" CommandName="Edit" UseSubmitBehavior="false"/>                        
                    <asp:Button ID="btnDelete" runat="server" Text="Delete" CommandName="Delete" />
                </td>
            </tr>
        </table>
    </ItemTemplate>
    <InsertItemTemplate>
          <table id="tblInsert">
            <tr>
                <td >ISBN : </td>
                <td>
                    <asp:TextBox ID="txtInsertBookISBN"  required="required" runat="server" Text='<%# Bind("BookISBN") %>'></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td >Title : </td>
                <td>
                    <asp:TextBox ID="txtInsertBookTitle" required="required"  runat="server" Text='<%# Bind("BookTitle") %>'></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td >Author : </td>
                <td>
                    <asp:TextBox ID="txtInsertBookAuthor" required="required"  runat="server" Text='<%# Bind("BookAuthor") %>'></asp:TextBox>
                </td>
            </tr>
            <tr>
               <td >Publisher : </td>
                <td>
                    <asp:TextBox ID="txtInsertBookPublisher" required="required"  runat="server" Text='<%# Bind("BookPublisher") %>'></asp:TextBox>
                </td>
            </tr>
            <tr>
               <td >Category : </td>
                <td>
                    <asp:TextBox ID="txtInsertBookCategory" required="required"  runat="server" Text='<%# Bind("BookCategory") %>'></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:Button ID="btnSave" runat="server" CommandName="Insert" Text="Save"/>
                    <asp:Button ID="btnCancelInsert" runat="server" CommandName="Cancel" Text="Cancel"   UseSubmitBehavior="false" CausesValidation="false" />
                </td>
            </tr>
        </table>
    </InsertItemTemplate>
</asp:FormView>    


When creating this project, add an Ado.Net Entity Data Model which points to the specified database. :-)

Sample screenshots (Create, Details, Update)

DataKey value is null in ItemDeleting event of ASP.NET FormView Control

Hello,
Normally, you can access the DataKey object value of a FormView control to it's wired events. Since I'm using Entity Framework as Datasource of a FormView control instead of DataSource control wizards, the DataKey object value of the FormView control returns null instead of an ID in the ItemDeleting event of that object.
After putting several breakpoints to it's events, I came up with a fix that is to query the DB again and then bind it's result to the DataSource property of the FormView Control. That is call BindFormView() method when CommandName is equal to "Delete" in the ItemCommand event of the control.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
protected void FormViewBookDetails_ItemCommand(object sender,
 FormViewCommandEventArgs e)
{
    if (e.CommandName == "Cancel")
    {
        FormViewBookDetails.ChangeMode(FormViewMode.ReadOnly);
    }
    else if (e.CommandName == "Edit")
    {
        FormViewBookDetails.ChangeMode(FormViewMode.Edit);
    }
    else if (e.CommandName == "New")
    {
        FormViewBookDetails.ChangeMode(FormViewMode.Insert);
    }
    else if (e.CommandName == "Delete")
    {
        FormViewBookDetails.ChangeMode(FormViewMode.ReadOnly);
        BindFormView();
    }
}
 
private void BindFormView()
{
    var bookRecords = _context.BookDetails.ToList();
    FormViewBookDetails.DataSource = bookRecords;
    FormViewBookDetails.DataBind();
}

Saturday, March 12, 2016

DataGridview check for duplicate values and group code in separate column

Hi, I've always been a desktop developer ever since Visual Basic 6.0 came into existence. While I'm busy doing projects for the web platform, I always go back to my roots of solving desktop issues in .NET. :-D

Well, going back to the issue, a certain member in Visual Basic forums posted an issue on how to check duplicate values in a particular datagridview column with DateTime as type. Once a duplicate value has been detected, the code number will be grouped in another datagridview column with label total. See sample screenshot for the desired output:
After creating the logic, I proceed to converting it to code.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
private void SpotDuplicates()
{
    int i = 0;
    int j = 0;
 
    for (i = 0; i <= dgvDates.RowCount - 1; i++)
    {
        for (j = 0; j <= dgvDates.RowCount - 1; j++)
        {
            if (i > j)
            {
                if (Convert.ToDateTime(dgvDates[0, i].Value) == Convert.ToDateTime(dgvDates[0, j].Value))
                    dgvDates[3, i].Value += String.Format(" {0}", dgvDates[1, j].Value.ToString());
            }
            if (i < j)
            {
                if (Convert.ToDateTime(dgvDates[0, i].Value) == Convert.ToDateTime(dgvDates[0, j].Value))
                    dgvDates[3, i].Value += String.Format(" {0}", dgvDates[1, j].Value.ToString());
            }
            if (i == j)
            {
                if(!dgvDates.Rows[i].IsNewRow)
                    dgvDates[3, i].Value += String.Format(" {0}", dgvDates[1, i].Value.ToString());
            }
        }
    }
}
The method will be called after binding the collection to the datagridview's itemsource property. :-)

Thursday, March 10, 2016

Migrate SQL Server Compact Database (SDF) to MDF

As I was migrating an old project from a client to a new platform, his database was a SQL Server Compact 4.0. Lol, I haven't used this type of database in my entire career. :-D His objective was to migrate the schema and it's data to SQL Server 2012. Given the task, I made some research and came up with a tool created by ErikEJ on Scripting API samples. That is integrate some assemblies to the project and create some functions to write the schema and it's data to an external file.

Here's an example on how to use the tool.
1. First, make sure that the database file's Read-Only property is unchecked.
2. Create a C# console project then add the following assemblies ISqlCeScripting.dll, SqlCeScripting.dll and SqlCeScripting40.dll
3. Reference ErikEJ namespace in your project.
1
using ErikEJ.SqlCeScripting;
4. Function to generate table scripts including their constraints.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
private static void GenerateCreateTableScript()
{
    using (IRepository repository = new DB4Repository(@"Data Source=C:\Codes\School.sdf"))
    {
        Generator generator = new Generator(repository, null);
        foreach (var tableName in repository.GetAllTableNames())
        {
            generator.GenerateTableScript(tableName);
        }
 
        System.IO.File.WriteAllText(@"C:\Codes\script.sqlce", generator.GeneratedScript);
    }
}
5. Function to generate insert scripts.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
private static void GenerateInsertData()
{
    using (IRepository repository = new DB4Repository(@"Data Source=C:\Codes\School.sdf"))
    {
        Generator generator = new Generator(repository, null);
        foreach (var tableName in repository.GetAllTableNames())
        {
            generator.GenerateTableContent(tableName, false);
        }
        System.IO.File.WriteAllText(@"C:\Codes\scriptInsertsqlce", generator.GeneratedScript);
    }
}

Note: Make sure to change the path with correct values.

Reference: Scripting API Documentation

Tuesday, March 8, 2016

Enable a control after validation of prior control succeeded using JavaScript in ASP.NET

Given you have a simple form below with two TextBox controls. The second TextBox is disabled during pageload. If you want to enable the second TextBox control if the validation of the first/prior control succeeded, the solution for this is using client side approach which is to check the visibility property through JavaScript/jQuery.
HTML Code
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
<div id="ContactForm">    
        <asp:Label id="lblEmail" Text="Email Address:" AssociatedControlID="txtEmail" Runat="server" />
        <asp:TextBox id="txtEmail" Runat="server" /> 
        <asp:RegularExpressionValidator id="regEmail"
            ControlToValidate="txtEmail" Text="(Invalid email)" 
             ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
            Runat="server" />    
        <asp:Label id="lblPhone" Text="Phone Number:" AssociatedControlID="txtPhone" Runat="server" />
        <asp:TextBox id="txtPhone" Runat="server" Enabled="false" />     
        <asp:Button id="btnSubmit" Text="Submit" Runat="server" />    
    </div>

The script below will trigger an onblur event of the first TextBox control. It then checks the visibility of the validator control if it's hidden which means successful validation. If true, the second TextBox control is enabled.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
$(document).ready(function() {
        $('#' + '<%= txtEmail.ClientID %>').blur(function(evt) {
            checkSingleValidators();
        });

        function checkSingleValidators() {
            var isHidden = $('#' + '<%= regEmail.ClientID %>').css('visibility');
            if (isHidden == "hidden") {
                $('#' + '<%= txtPhone.ClientID %>').prop('disabled', false);
            }
        }
    )
};

Friday, March 4, 2016

An error occurred creating the configuration section handler for spring/context: Could not load file or assembly 'Spring.Web' or one of its dependencies.

As I was integrating Spring.NET via NuGet, I had difficulties in referencing the assemblies to work with an ASP.NET MVC application. Such that I encountered the error specifically in web.config namespace Spring.Context.Support.WebContextHandler.

The workaround for this issue is to uninstall the Spring.NET which I installed earlier through NuGet and instead download Spring.NET1.3.1 from Spring Framework website and reference the assemblies below to my project.
List of assemblies:
Spring.Core.dll
Spring.Data.dll
Spring.Web.dll
Spring.Web.Mvc.dll
Common.Logging.dll

An object with the same key already exists in the ObjectStateManager. (Entity Framework 6.0)

The code below for updating of objects worked with Entity Framework 5 and below. But using this with Entity Framework 6.0 and Castle Windsor as IOC Container, I get an error message with is the title of this post.
1
2
3
4
5
6
7
8
public void Update(BookDetail item)
{
    if(item!= null)
    {
        _context.Entry(item).State = System.Data.Entity.Entitystate.Modified;
        _context.SaveChanges();
    }
}

So to fix this, replace the code above with DBContext.Entry(T Entity).CurrentValues.SetValues(T Entity).
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
public void Update(BookDetail item)
{
    if(item != null)
    {
        var result = _context.BookDetails.Find(item.BookSerialNo);
        if (result != null)
        {
            _context.Entry(result).CurrentValues.SetValues(item);
            _context.SaveChanges();
        }
    }
}

Thursday, March 3, 2016

Wrap text inside a JqGrid Cell

When showing records from a datasource, you may wonder why a certain JqGrid cell does not wrap the entire text into it's cell. An example would be a lengthy description, notes or remarks, and address. After doing some research, the fix is to add a cellattr to the grid's cell and set it's white space property to normal.

JqGrid Address Cell without cellattr property
1
{ name: 'Address', index: 'Address', width: 200, align: 'left' }



JqGrid Address Cell with cellattr property
1
2
3
{ name: 'Address', index: 'Address', width: 200, align: 'left', cellattr: 
function (rowId, tv, rawObject, cm, rdata) 
{ return 'style="white-space: normal;"' }},

JqGrid with ASP.NET MVC 5

Here's a simple demo using jqGrid in ASP.NET MVC 5 Visual Basic.NET. The detailed instructions are elaborated in VBForums ASP.NET Codebank. The NuGet packages needed are:
1. Dynamic LINQ Library
2. jqGrid 4.6.0

Sample Screenshot:

Setup and Source Code: Using jqGrid with ASP NET-MVC 5 Visual Basic.NET