Introduction:

In this article I will introduce a way to create excel files from datagrid in windows forms. Plus I will introduce a way to close the excel process which is created with the creation of the excel file

On to creation:

First thing we need to do is reference Excel. Go to project-.add reference ->.net and select Microsoft.Office.Tools.Excel
Now we are ready to go.
Let us create a class (let’s call it CreateExcel). The basic function in this class will be for the creation of the Excel and we will pass a GridView and the full name of the excel to be created. Now lets get our hands into code:

        public void createIt(System.Windows.Forms.DataGridView myGridView, String FullFileName)
        {
            System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            try
            {

                Microsoft.Office.Interop.Excel.Application oXL;
                Microsoft.Office.Interop.Excel._Workbook oWB;
                Microsoft.Office.Interop.Excel._Worksheet oSheet;
                Microsoft.Office.Interop.Excel.Range oRng;
                oXL = new Microsoft.Office.Interop.Excel.Application();
                oXL.Visible = false;

                //Get a new workbook.
                oWB = (Microsoft.Office.Interop.Excel.Workbook)(oXL.Workbooks.Add(XlWBATemplate.xlWBATWorksheet));
                oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
                Int32 i, j;
                //header
                for (j = 0; j < myGridView.ColumnCount; j++)
                {
                    oSheet.Cells[1, j + 1] = myGridView.Columns[j].HeaderText;
                }
                //Rows
                for (i = 0; i < myGridView.RowCount; i++)
                {
                    for (j = 0; j < myGridView.ColumnCount; j++)
                    {
                        oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i + 2, j + 1];
                        oRng.NumberFormat = "@";
                        oSheet.Cells[i + 2, j + 1] = Convert.ToString(myGridView.Rows.Cells[j].Value);
                    }
                }
                //save it
                oWB.SaveAs(FullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel4Workbook, Type.Missing, 
                                                                        Type.Missing, Type.Missing, Type.Missing, 
                                                                        Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, 
                                                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            catch (Exception ex)
            {
                    throw ex;
            }
            finally
            {
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
            }
        }

What we do basically is after some initializations iterate to get the headers of columns and then iterate through rows and columns to create the excel cells. We also set the format of each cell to text so we don’t have issues with big numbers
( 

oRng.NumberFormat = "@";

)


Now on to the big question.  There are several approaches to stop the created excel proccess. Flushing, closing, using garbage collector. None of these seems to do our trick . So taking the hard road we will make sure processes gets killed … by killing it :D
We will need a Hashtable to store the process Ids of excel processes before creating our process.  GetExcellProcesses can do the trick

        private void GetExcellProcesses()
        {

            try
            {
                //lets get all excel processes
                Process[] AllProcesses = Process.GetProcessesByName("excel");
                myExcelHashtable = new Hashtable();
                int iCount = 0;
                foreach (Process ExcelProcess in AllProcesses)
                {
                    myExcelHashtable.Add(ExcelProcess.Id, iCount);
                    iCount = iCount + 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
            }
        }

Then we will need to kill our created process when we are finished

        private void KillMyExcelProcess()
        {

            try
            {
                Process[] AllProcesses = Process.GetProcessesByName("excel");

                // we need to kill the right process
                foreach (Process ExcelProcess in AllProcesses)
                {
                    if (myExcelHashtable.ContainsKey(ExcelProcess.Id) == false)
                        ExcelProcess.Kill();
                }

                AllProcesses = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
            }

        }

So here it is , the complete class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Collections;

namespace xxx
{
    class CreateExcel
    {
        Hashtable myExcelHashtable;
        public void createIt(System.Windows.Forms.DataGridView myGridView, String FullFileName)
        {
            System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            try
            {
                // get process ids before excel creation
                GetExcellProcesses();
                //on to excel creation
                Microsoft.Office.Interop.Excel.Application oXL;
                Microsoft.Office.Interop.Excel._Workbook oWB;
                Microsoft.Office.Interop.Excel._Worksheet oSheet;
                Microsoft.Office.Interop.Excel.Range oRng;
                oXL = new Microsoft.Office.Interop.Excel.Application();
                oXL.Visible = false;

                //Get a new workbook.
                oWB = (Microsoft.Office.Interop.Excel.Workbook)(oXL.Workbooks.Add(XlWBATemplate.xlWBATWorksheet));
                oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
                Int32 i, j;
                //header
                for (j = 0; j < myGridView.ColumnCount; j++)
                {
                    oSheet.Cells[1, j + 1] = myGridView.Columns[j].HeaderText;
                }
                //Rows
                for (i = 0; i < myGridView.RowCount; i++)
                {
                    for (j = 0; j < myGridView.ColumnCount; j++)
                    {
                        oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i + 2, j + 1];
                        oRng.NumberFormat = "@";
                        oSheet.Cells[i + 2, j + 1] = Convert.ToString(myGridView.Rows.Cells[j].Value);
                    }
                }
                //save it
                oWB.SaveAs(FullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel4Workbook, Type.Missing, 
                                                                        Type.Missing, Type.Missing, Type.Missing, 
                                                                        Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, 
                                                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            catch (Exception ex)
            {
                    throw ex;
            }
            finally
            {
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
                // kill the right process after export completed
                KillMyExcelProcess();
            }
        }

        private void GetExcellProcesses()
        {
            try
            {
                //lets get all excel processes
                Process[] AllProcesses = Process.GetProcessesByName("excel");
                myExcelHashtable = new Hashtable();
                int iCount = 0;
                foreach (Process ExcelProcess in AllProcesses)
                {
                    myExcelHashtable.Add(ExcelProcess.Id, iCount);
                    iCount = iCount + 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
            }
        }
        private void KillMyExcelProcess()
        {

            try
            {
                Process[] AllProcesses = Process.GetProcessesByName("excel");

                // we need to kill the right process
                foreach (Process ExcelProcess in AllProcesses)
                {
                    if (myExcelHashtable.ContainsKey(ExcelProcess.Id) == false)
                        ExcelProcess.Kill();
                }

                AllProcesses = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
            }

        }


    }
}

Design patterns could be divided in three groups, Creational, Structural and Behavioral. The Factory pattern is a creational pattern. But what is really in simple words the factory pattern? Let us consider the following example.

Suppose there is a disc wheel factory. A variety of different disc wheels can be produced. The management of the factory does not really have to know how each type of disc wheel is made. The workers on the other hand need to know how. In the beginning of the day the manager decides which disc wheel will be created today and tells to the workers to make it. So in order to produce a specific disc wheel the manager has to simply say “Let’s produce today the x disc wheel”. Moreover the customers that buy the wheels don’t care and do not know about this process. They simply say we want to buy this kind of disc wheel.

In bottom line we want a generic disc wheel form which all disc wheels have to follow, but each kind of disc wheel can have each specific characteristics. And we want the manager to say “Produce the x wheel type”. These can be represented in UML as following (with one method :

But now let us move to the Data Access Layer. Let’s implement the same logic in the existing layer (explained in a previous article). We want a Factory class to produce to us the desired layer (Sql Server specific, Oracle specific and so forth). So we simply create the class and set a public enum for the providers. And of course we have a CreateDataAccessLayer function that returns the layer. This function can have parameters (for connection string and provider) or can read from the Web config file to get the desired elements.

We also had a sql server specific layer developed. But we want an oracle specific too and some others. So we design an Interface called IDataAccess. Each of the layers that we are going to develop will implement this interface.

The whole code will not be provided (it’s not the case of this article), the code only for one function will be.

It is time now for the code:

 

Here is our Factory class:

Public Enum Enum_Providers
    SqlServer
    Oracle
    Oledb
End Enum
Public Class Factory
    Private _mProvider As String = System.Configuration.ConfigurationSettings.AppSettings.Item("Provider")
    Private _mConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings.Item("ConnectionString")
    Public Function CreateDataAccessLayer() As IDataAccess
        Select Case _mProvider
            Case "Oledb"
                'do something here
            Case "Oracle"
                Dim ms As New OracleDataAccess(_mConnectionString)
                Return ms
            Case "SqlServer"
                'do something here
        End Select
    End Function
End Class

The interface:

Public Interface IDataAccess
    Property myCommand() As IDbCommand
    Property myConnection() As IDbConnection
    Property ConnectionString() As String
    Property myDataAdapter() As IDbDataAdapter
    Property myprovider() As Enum_Providers
    Property myConnectionString() As String
  
    Function Get_record_byID(ByVal myFieldsCollection As FieldsCollection, _
                                               ByVal storedprocedurename As String) As DataSet
    Function Get_Dataset(ByVal myFieldsCollection As FieldsCollection, _
                            ByVal storedprocedurename As String, _
                             ByVal datatable As String) As DataSet
    Function Get_record_byID(ByVal myFieldsCollection As FieldsCollection, _
                                ByVal storedprocedurename As String, _
                                ByVal datatable As String) As DataSet
    Function Get_record_byIDs(ByVal myFieldsCollection As FieldsCollection, _
                                            ByVal storedprocedurename As String, _
                                            ByVal datatable As String) As DataSet
    Function Delete_record(ByVal myFieldsCollection As FieldsCollection, _
                                ByVal storedprocedure As String) As String
    Function Update_Record(ByVal storedprocedure As String, _
                                ByVal myFieldsCollection As FieldsCollection) As String
    Function Insert_Record(ByVal storedprocedure As String, _
                           ByVal myFieldsCollection As FieldsCollection) As String
    Function Get_records_by_fields(ByVal myFieldsCollection As FieldsCollection, _
                                       ByVal storedprocedurename As String, _
                                       ByVal datatable As String) As DataSet
End Interface

And a simple Oracle specific data access layer (methods and properties are missing).

Imports System.Data.OracleClient
Public Class OracleDataAccess
    Implements IDataAccess
    'a lot here ...
Public Function Get_Dataset(ByVal myFieldsCollection As FieldsCollection, _
                                        ByVal storedprocedurename As String, _
                                            ByVal datatable As String) As System.Data.DataSet Implements IDataAccess.Get_Dataset
        Dim sqlDataAdapter As New OracleDataAdapter
        Try
            sqlDataAdapter.SelectCommand = New OracleCommand
            If _conn.ConnectionString = "" Then
                _conn.ConnectionString = _mConnectionString
            End If
            sqlDataAdapter.SelectCommand.Connection = _conn
            If Not _conn.State = ConnectionState.Open Then
                _conn.Open()
            End If
            sqlDataAdapter.SelectCommand.CommandText = storedprocedurename
            sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
            If Not IsNothing(_trans) Then
                sqlDataAdapter.SelectCommand.Transaction = _trans
            End If
            'Parameters
            Dim myparam As OracleParameter
            For i As Integer = 0 To myFieldsCollection.Count - 1
                Dim obj As Field
                obj = myFieldsCollection.Item(i)
                If obj.ColumnLength > 0 Then
                    myparam = New OracleParameter(obj.ColumnName, _
                                                                    obj.columnTypeOracle, obj.ColumnLength)
                Else
                    myparam = New OracleParameter(obj.ColumnName, _
                                                                    obj.columnTypeOracle)
                End If
                myparam.Direction = obj.Direction
                If myparam.Direction = ParameterDirection.Input Then
                    If obj.ColumnValue Is Nothing Then
                    Else
                        myparam.Value = obj.ColumnValue
                    End If
                End If
                sqlDataAdapter.SelectCommand.Parameters.Add(myparam)
            Next
            Dim myDataSet As DataSet = New DataSet
            sqlDataAdapter.Fill(myDataSet, datatable)
            Return myDataSet
        Catch ex As Exception
            Throw ex
        Finally
            If IsNothing(_trans) Then
                _conn.Close()
            End If
        End Try
    End Function

Limitations:

Note that when we call a layer and run a procedure from our business layer we must add parameters. But oracle, sql server e.t.c. do not have the same parameters. Some define their own. So we have to re-design the class Field of the existing data layer. We have to set an enum for the  Columntype and during the creation of a column set the type to the specific type.

Imports System.Data.OracleClient
Imports System.Data.SqlClient
<AttributeUsage(AttributeTargets.Parameter, AllowMultiple:=True, Inherited:=True)> _
Public Class Field
    Inherits System.Attribute
    Public Enum myColumnType
        Sql_Varchar
        SqlDb_BigInt
        SqlDb_Bit
        SqlDb_Char
        SqlDb_DateTime
        SqlDb_Decimal
        SqlDb_Int
        SqlDb_NChar
        SqlDb_NVarChar
        SqlDb_SmallDateTime
        SqlDb_Text
        SqlDb_VarChar
        'Oracle
        Oracle_Cursor
        Oracle_Byte
        Oracle_Char
        Oracle_DateTime
        Oracle_Double
        Oracle_Float
        Oracle_Int32
        Oracle_NChar
        Oracle_LongVarChar
        Oracle_Number
        Oracle_VarChar
    End Enum
    Private _columnName As String = ""
    Private _columnTypeOracle As OracleClient.OracleType
    Private _columnTypeSqlServer As SqlDbType
    Private _columnLength As Integer = 0
    Private _columnValue As Object
    Private _direction As ParameterDirection = ParameterDirection.Input
    Public Property ColumnName() As String
        Get
            Return _columnName
        End Get
        Set(ByVal Value As String)
            _columnName = Value
        End Set
    End Property
    Public Property Direction() As ParameterDirection
        Get
            Return _direction
        End Get
        Set(ByVal Value As ParameterDirection)
            _direction = Value
        End Set
    End Property
    Public Property columnTypeSqlServer() As SqlDbType
        Get
            Return _columnTypeSqlServer
        End Get
        Set(ByVal Value As SqlDbType)
            _columnTypeSqlServer = Value
        End Set
    End Property
    Public Property columnTypeOracle() As OracleClient.OracleType 'OracleClient.OracleType
        Get
            Return _columnTypeOracle
        End Get
        Set(ByVal Value As OracleClient.OracleType) 'OracleClient.OracleType)
            _columnTypeOracle = Value
        End Set
    End Property
    Public Property ColumnLength() As Integer
        Get
            Return _columnLength
        End Get
        Set(ByVal Value As Integer)
            _columnLength = Value
        End Set
    End Property
    Public Property ColumnValue() As Object
        Get
            Return _columnValue
        End Get
        Set(ByVal Value As Object)
            _columnValue = Value
        End Set
    End Property
    Public Property SourceColumn() As String
        Get
        End Get
        Set(ByVal Value As String)
        End Set
    End Property
    Public Sub New(ByVal colName As String, ByVal colType As myColumnType, _
                        ByVal colValue As Object, ByVal colLength As Integer)
        Me.ColumnName = colName
        Me.ColumnValue = colValue
        Select Case colType
            'Sql Server
        Case myColumnType.Sql_Varchar
                Me.columnTypeSqlServer = SqlDbType.VarChar
                'A lot here
                'SqlDbType.BigInt()
                'SqlDbType.Bit()
                'SqlDbType.Char()
                'SqlDbType.DateTime()
                'SqlDbType.Decimal()
                'SqlDbType.Int()
                'SqlDbType.NChar()
                'SqlDbType.NVarChar()
                'SqlDbType.SmallDateTime()
                'SqlDbType.Text()
                'SqlDbType.VarChar()
            Case myColumnType.Oracle_Cursor
                Me.columnTypeOracle = OracleType.Cursor
            Case myColumnType.Oracle_Byte
                Me.columnTypeOracle = OracleType.Byte
            Case myColumnType.Oracle_Char
                Me.columnTypeOracle = OracleType.Char
            Case myColumnType.Oracle_DateTime
                Me.columnTypeOracle = OracleType.DateTime
            Case myColumnType.Oracle_Double
                Me.columnTypeOracle = OracleType.Double
            Case myColumnType.Oracle_Float
                Me.columnTypeOracle = OracleType.Float
            Case myColumnType.Oracle_Int32
                Me.columnTypeOracle = OracleType.Int32
            Case myColumnType.Oracle_NChar
                Me.columnTypeOracle = OracleType.NChar
            Case myColumnType.Oracle_LongVarChar
                Me.columnTypeOracle = OracleType.LongVarChar
            Case myColumnType.Oracle_Number
                Me.columnTypeOracle = OracleType.Number
            Case myColumnType.Oracle_VarChar
                Me.columnTypeOracle = OracleType.VarChar
        End Select
    End Sub
End Class

So for our business layer we just:

        Dim mycol As New Factory_dal.FieldsCollection
        Dim fakeobject As String = ""
        Dim myfactory As New Factory_dal.Factory
        Dim mydataccesslayer As Factory_dal.IDataAccess
        mydataccesslayer = myfactory.CreateDataAccessLayer()
        Try
            Dim myField1 As New Field("return_rec", Field.myColumnType.Oracle_Cursor, fakeobject, 0)
            myField1.Direction = ParameterDirection.Output
            mycol.Add(myField1)
            Me.DataGrid1.DataSource = mydataccesslayer.Get_Dataset(mycol, "xxx", "xxx")
            Me.DataGrid1.DataBind()
        Catch ex As Exception
        End Try

That’s all. Remember I could use the feedback.

Ok we know that when passing "byVal" in methods strings,  booleans e.t.c their values do not change, but when passing "ByRef" they do change. However when passing objects it does not matter if we pass it "ByVal" or "ByRef". Either way the object changes!! Lets see that in practice:

Create a Web project and place a button on the form. Then create a class and the following methods:

Public Class Class1
    Private _mytitle As String
    Public _mytitlee As String
    Public Property mytitle() As String
        Get
            Return _mytitle
        End Get
        Set(ByVal Value As String)
            _mytitle = Value
        End Set
    End Property
    Public Sub setTitle(ByVal myob As Class1)
        myob.mytitle = "new title"
    End Sub
    Public Sub setTitlee(ByVal str As String)
        str = "this the new title"
    End Sub
End Class


You see we pass "ByVal" a string and a class. Now let's go to the form. Write the following for the click event of the button:

'the object that we are going to pass
        Dim myobj As New Class1
        '.....
        Dim myclasss As New Class1
        Dim str As String = ""
        myclasss.setTitle(myobj)
        myclasss.setTitlee(str)
        Response.Write(myobj.mytitle)
        Response.Write(str)


Now run the project ... you will see the unexpected. The object does change value allthough it's beeing passed by value...

    Well since I couldn’t use a different datagrid than the one provided by Microsoft I decided to build a datagrid with some basic functionality. At least delete, insert, update e.t.c. should be supported. So this is my extended datagrid. Please if you have suggestions send me a message. I will appreciate the feedback. And sorry for the not so good writing.
    The developed grid is a web server control and is coded in Visual Basic (sorry my C# friends). So let us take it form the beginning.
    Just start Visual Studio and point to Visual Basic projects and to “Web Control Library”. Let name our project CustomDatagrid. Delete all the default code which is generated by Visual Studio. It is common wisdom that our grid will inherit from the Datagrid so just after the necessary imports define the class inherited form Datagrid. 


Imports System.ComponentModel
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Public Class CustomdataGrid
    Inherits DataGrid
End Class

    We want also the grid to fire up some events when doing some action and catch those events from the form. These events are commanddelete (for deleting a record), commandedit (for editing a record) and so forth. Moreover we are interested of some properties like CacheString (it is going to be used for caching the datasource), MyDataSource (used to set the datasource as a datatable), deletecolumnNumber (to set which column is the “delete” column).

'Declare Events for trapping them from the form
    Public Event CommandDelete(ByRef deleted As Boolean, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)
    Public Event CommandEdit()
    Public Event Cancel()
    Public Event Addrecord(ByRef successfull As Boolean, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)
    Public Event Updaterecord(ByRef successfull As Boolean, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)
    Public Event SetDataSource()
    'For private use
    Private table As New DataTable
    '.....
    Private _MyDataSource As DataTable
    Private _DeleteColumnNumber As Integer
    Private _EditColumnNumber As Integer
    Private _CacheString As String
    'Properties
    Public Property CacheString() As String
        Get
            Return _CacheString
        End Get
        Set(ByVal Value As String)
            _CacheString = Value
        End Set
    End Property
    Public Property EditColumnNumber() As Integer
        Get
            Return _EditColumnNumber
        End Get
        Set(ByVal Value As Integer)
            _EditColumnNumber = Value
        End Set
    End Property
    Public Property DeleteColumnNumber() As Integer
        Get
            Return _DeleteColumnNumber
        End Get
        Set(ByVal Value As Integer)
            _DeleteColumnNumber = Value
        End Set
    End Property
    Public Property MyDataSource() As DataTable
        Get
            Return _MyDataSource
        End Get
        Set(ByVal Value As DataTable)
            _MyDataSource = Value
        End Set
    End Property

We simply want the grid to be render in the beginning so:


'Render the grid
    Protected Overrides Sub Render(ByVal output As System.Web.UI.HtmlTextWriter)
        MyBase.Render(output)
    End Sub


We want ot be able to clear the Cache:

Public Sub ClearCache()
        'Clear cache
        HttpContext.Current.Cache.Remove(_CacheString)
    End Sub

To bind the grid:

Sub BindGrid()
        'bind the grid
        If Not HttpContext.Current.Cache.Get(_CacheString) Is Nothing Then
            Dim dgCache As DataSet
            dgCache = HttpContext.Current.Cache.Get(_CacheString)
            'grid
            Me.MyDataSource = dgCache.Tables(0)
        Else
            'Me.MyDataSource = Me.DataSource.tables(0)
        End If
        Me.DataSource = CreateDataSource()
        Me.DataBind()
        Me.Visible = True
    End Sub

And to handle events like insert, delete, update e.t.c.

So the whole class becomes the following:


Imports System.ComponentModel
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Public Class CustomdataGrid
    Inherits DataGrid
    'Declare Events for trapping them from the form
    Public Event CommandDelete(ByRef deleted As Boolean, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)
    Public Event CommandEdit()
    Public Event Cancel()
    Public Event Addrecord(ByRef successfull As Boolean, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)
    Public Event Updaterecord(ByRef successfull As Boolean, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)
    Public Event SetDataSource()
    'For private use
    Private table As New DataTable
    '.....
    Private _MyDataSource As DataTable
    Private _DeleteColumnNumber As Integer
    Private _EditColumnNumber As Integer
    Private _CacheString As String
    'Properties
    Public Property CacheString() As String
        Get
            Return _CacheString
        End Get
        Set(ByVal Value As String)
            _CacheString = Value
        End Set
    End Property
    Public Property EditColumnNumber() As Integer
        Get
            Return _EditColumnNumber
        End Get
        Set(ByVal Value As Integer)
            _EditColumnNumber = Value
        End Set
    End Property
    Public Property DeleteColumnNumber() As Integer
        Get
            Return _DeleteColumnNumber
        End Get
        Set(ByVal Value As Integer)
            _DeleteColumnNumber = Value
        End Set
    End Property
    Public Property MyDataSource() As DataTable
        Get
            Return _MyDataSource
        End Get
        Set(ByVal Value As DataTable)
            _MyDataSource = Value
        End Set
    End Property
    'Render the grid
    Protected Overrides Sub Render(ByVal output As System.Web.UI.HtmlTextWriter)
        MyBase.Render(output)
    End Sub
    'create the datasoure for the grid
    Function CreateDataSource() As ICollection
        Try
            If _MyDataSource.Rows.Count > 0 Then
                Dim dv As DataView
                dv = _MyDataSource.DefaultView
                CreateDataSource = dv
            Else
            End If
        Catch ex As Exception
            Throw ex
        Finally
        End Try
    End Function
    'inserting a new row in the grid for new record
    Public Sub NewRecord()
        Me.EditItemIndex = 0
        'Move to the first page of the grid
        Me.CurrentPageIndex = 0
        'Hide Delete
        Me.Columns(_DeleteColumnNumber).Visible = False
        'Change the text of the "edit" column
        Dim ecc As EditCommandColumn
        ecc = Me.Columns(EditColumnNumber)
        ecc.UpdateText = "Insert"
        'setting up a private datatable
        Fill()
        'inserting a new row to the datatable
        InsertEmptyRow(table)
        'bind to grid
        Bind()
    End Sub
    Private Function InsertEmptyRow(ByVal dttable As DataTable) As DataTable
        'Insert empty row in the beginning of the grid
        dttable.Rows.InsertAt(table.NewRow(), 0)
        For i As Integer = 0 To dttable.Columns.Count - 1
            dttable.Rows(0).Item(i) = ""
        Next
        Return dttable
    End Function
    Private Sub Fill()
        'please clear tin cache
        ClearCache()
        'Please give me my datasource again in the Cache
        RaiseEvent SetDataSource()
        Dim myds As DataSet
        myds = HttpContext.Current.Cache.Get(_CacheString)
        table = myds.Tables(0)
    End Sub
    Private Sub Bind()
        'Bind the grid to the datasource
        Me.DataSource = table
        Me.DataBind()
    End Sub
    Private Sub CustomdataGrid_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles MyBase.PageIndexChanged
        Me.CurrentPageIndex = e.NewPageIndex
        BindGrid()
    End Sub
    Public Sub CustomdataGrid_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles MyBase.EditCommand
        '$$$$$$$$
        'I am going to edit the record (do you want something to be done before that?)
        RaiseEvent CommandEdit()
        '$$$$$$$
        Dim myds As DataSet
        myds = HttpContext.Current.Cache.Get(_CacheString)
        Me.MyDataSource = myds.Tables(0)
        ' begin editing
        Me.EditItemIndex = e.Item.ItemIndex
        'Bind the grid
        BindGrid()
        'Delete should not be visible
        Me.Columns(_DeleteColumnNumber).Visible = False
    End Sub
    Public Sub CustomdataGrid_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles MyBase.CancelCommand
        'Please clear the cache
        ClearCache()
        'I am going to cancel (do you want something to be done before that?)
        RaiseEvent Cancel()
        'Please give me my datasource again
        RaiseEvent SetDataSource()
        Me.EditItemIndex = -1
        'Bind the grid
        BindGrid()
        'Delete should be visible now
        Me.Columns(_DeleteColumnNumber).Visible = True
        'Change the column text
        Dim ecc As EditCommandColumn
        ecc = Me.Columns(_EditColumnNumber)
        ecc.UpdateText = "Update"
    End Sub
    Public Overridable Sub CustomdataGrid_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles MyBase.DeleteCommand
        Dim isLastOfThePage As Boolean
        'Is this the last record?
        If Me.Items.Count = 1 Then
            isLastOfThePage = True
        End If
        Dim isdeleted As Boolean
        'To know if the record has been deleted
        '$$$$$
        'Please delete the record
        RaiseEvent CommandDelete(isdeleted, e)
        '$$$$$
        If isdeleted Then
            'Deleted succesfully so lets go now
            If isLastOfThePage Then Me.CurrentPageIndex = 0
            'clear cache
            ClearCache()
            Me.EditItemIndex = -1
            'Give me my know datasource
            RaiseEvent SetDataSource()
            'Bind the grid
            BindGrid()
            'Delete is now visible
            Me.Columns(_DeleteColumnNumber).Visible = True
        End If
    End Sub
    Private Sub CustomdataGrid_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles MyBase.UpdateCommand
        Dim successfull As Boolean
        Dim myds As DataSet
        Dim mydataTable As DataTable
        myds = HttpContext.Current.Cache.Get(_CacheString)
        mydataTable = myds.Tables(0)
        Me.MyDataSource = myds.Tables(0)
        If _MyDataSource.Rows(e.Item.ItemIndex).RowState = DataRowState.Added Then
            'New record - add it please
            RaiseEvent Addrecord(successfull, e)
        Else
            'update record - update it please
            RaiseEvent Updaterecord(successfull, e)
        End If
        If successfull Then
            'All done succesfully
            Me.EditItemIndex = -1
            'clear cache
            ClearCache()
            'Give me my new datasource
            RaiseEvent SetDataSource()
            'Bind the grid
            BindGrid()
            'Delete is now visible
            Me.Columns(_DeleteColumnNumber).Visible = True
            'Change the edit column text
            Dim ecc As EditCommandColumn
            ecc = Me.Columns(_EditColumnNumber)
            ecc.UpdateText = "Update"
        End If
    End Sub
    Public Sub ClearCache()
        'Clear cache
        HttpContext.Current.Cache.Remove(_CacheString)
    End Sub
    Sub BindGrid()
        'bind the grid
        If Not HttpContext.Current.Cache.Get(_CacheString) Is Nothing Then
            Dim dgCache As DataSet
            dgCache = HttpContext.Current.Cache.Get(_CacheString)
            'grid
            Me.MyDataSource = dgCache.Tables(0)
        Else
            'Me.MyDataSource = Me.DataSource.tables(0)
        End If
        Me.DataSource = CreateDataSource()
        Me.DataBind()
        Me.Visible = True
    End Sub
End Class


    Please note that when an action occurs our grid fires some events that should be cached from the form. Like the “SetDataSource” which as it says sets the datasource. Now that we are done just build the project and the custom grid is ready for production.

    Now let us use it in a simple project. Create a new web project and reference the grid. Select "add existing project" to the solution and add the grid project. To add the grid in the web form just register it:


<%@ Register TagPrefix="MyGrid" Namespace="CustomDataGrid" Assembly="CustomDataGrid" %>

And add it to the page:


<MyGrid:CustomDataGrid id="CustomdataGrid1" runat="server"></MyGrid:CustomDataGrid>


Next set some columns (delete and insert columns too). Let's say we will add three columns categoryid, categoryname and description and the edit and delete column.

Don’t forget to clear the “Create columns automatically at run time”.


There are some basic things which we have to point if we want to use this grid. First of all every time we load the web form we must set the numbers of the delete, edit columns, the string in which name we will store the datasource in cache and to bind the grid if there is already a datasource.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        If Not IsPostBack Then
            'Do something here
        End If
        'set grid
        SetGrid()
    End Sub

'Setting the grid
    Private Sub SetGrid()
        Me.CustomdataGrid1.EditColumnNumber = 3 'the number of the edit column
        Me.CustomdataGrid1.DeleteColumnNumber = 4 'the number of the delete column
        Me.CustomdataGrid1.CacheString = xxxxxx 'xxx: Cachestring
        If Not Cache(xxxx) Is Nothing Then
            Dim myds As DataSet
            myds = Cache(xxxxx) 'xxx: Cachestring
            Me.CustomdataGrid1.MyDataSource = myds.Tables(0)
        End If
    End Sub



Notice that we clear the cache, then get the datasource (for example a select statement (which will also handle the CustomdataGrid1.SetDataSource event) and inside there we will cache the datasource and the we bind the grid. A typical GetDataSource function would be:

Private Sub GetDatSource() Handles CustomdataGrid1.SetDataSource
        Try
            'Here the code for getting the datasource
            If xxxxx Then  'if we got the datasource
                '!!!!!!!!!!!!!!!!!!!!!!!
                'Inserting into cache
                Cache.Insert("xxxxx", myds, Nothing, _
                                        DateTime.Now.AddMinutes(15), TimeSpan.Zero)
                'xxx: Cachestring
                'myds: the dataset
            Else
                myds = Cache("xxx") 'here the cachestring
            End If
        Catch ex As Exception
        Finally
        End Try
    End Sub

Following the same we write functions for the events thrown by the grid:


Private Sub Cancel() Handles CustomdataGrid1.Cancel
        'Something here
    End Sub
    Private Sub Edit() Handles CustomdataGrid1.CommandEdit
        'Something here
    End Sub
    Private Sub Delete(ByRef Deleted As Boolean, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles CustomdataGrid1.CommandDelete
        If ‘delete the record here Then
            Deleted = True
        Else
            Deleted = False
        End If
    End Sub
    Private Sub AddsRecord(ByRef successfull As Boolean, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles CustomdataGrid1.Addrecord
        If ‘Add the record here Then
            successfull = True
        End If
    End Sub
    Private Sub Updaterecord(ByRef successfull As Boolean, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles CustomdataGrid1.Updaterecord
        If ‘Update the record here Then
            successfull = True
        End If
    End Sub

   


And a find button to get the datasource

Private Sub find_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles find.Click
        'clear cache
        Me.CustomdataGrid1.ClearCache()
        GetDatSource()
        'Kano bind to grid
        Me.CustomdataGrid1.BindGrid()
    End Sub

That was all. Please let me remind you again if you have suggestions send me a message.

Introduction
We often read or hear about n-tier applications, layers e.t.c. The benefits of implementing such a logic in the code is not a subject of this article, but most of developers are aware of that. Since my beginning in .net I had in mind that maybe I could develop a Data Access Layer so I would not have to ever code that layer again and only code stored rocedures and the layer just above the data access layer.

My Solution
The first part of my solution comes with this article. I developed in the beginning just the basic parts of the layer and functions that retrieve records, datasets, update tables, delete records.
My basic Data Access Layer consists from 3 classes (AccessingData, Field, FieldsCollection).

Field Class
The field class is used to represent a field from a table (and to later add it to a collection and pass it to the command). It simply has 4 properties ColumnName,ColumnType, ColumnLength, ColumnValue (each the desired type)
This is the code.

Public Class Field
    Private _columnName As String = ""
    Private _columnType As SqlDbType = SqlDbType.VarChar
    Private _columnLength As Integer = 0
    Private _columnValue As Object
    Public Property ColumnName() As String
        Get
            Return _columnName
        End Get
        Set(ByVal Value As String)
            _columnName = Value
        End Set
    End Property

    Public Property ColumnType() As SqlDbType
        Get
            Return _columnType
        End Get
        Set(ByVal Value As SqlDbType)
            _columnType = Value
        End Set
    End Property
    Public Property ColumnLength() As Integer
        Get
            Return _columnLength
        End Get
        Set(ByVal Value As Integer)
            _columnLength = Value
        End Set
    End Property

    Public Property ColumnValue() As Object
        Get
            Return _columnValue
        End Get
        Set(ByVal Value As Object)
            _columnValue = Value
        End Set
    End Property

    Public Sub New(ByVal colName As String, ByVal colType As SqlDbType, _
                       
ByVal colValue As Object, ByVal colLength As Integer)
        Me.ColumnName = colName
        Me.ColumnType = colType
        Me.ColumnLength = colLength
        Me.ColumnValue = colValue
    End Sub
End Class

Fields Collection Class
This class is simply used as a collection of fields. Nothing so much special abou that. Here is the code:

Public Class FieldsCollection
    Inherits System.Collections.CollectionBase

    Public Sub Add(ByVal objItemToAdd As Field)
        Me.List.Add(objItemToAdd)
    End Sub
    Public ReadOnly Property Item(ByVal iIndex As Integer) As Field
        Get
            Return Me.List(iIndex)
        End Get
    End Property
End Class

Accessing Data Class
This class is the most important one. Here we have properties for the connection, the connection string, the sqlcommand, the transaction and the functions to retrieve datasets (whole table - Get_Dataset), to retrieve specific record by id (Get_record_byId), the sub to delete record (Delete_record) and the sub to update records (Update_Record).
We can set if we want transaction or not from the above layer. If we do not want to then this layer opens connections does the job and then closes the connection.
Here is the code:

Imports System.Data.SqlClient
Public Class AccessingData
    Private _conn As New SqlConnection
    Private _trans As SqlTransaction
    Private _connectionstring As String = System.Configuration.ConfigurationSettings.AppSettings("SQLconn")
    Private _command As New SqlCommand
    Public Property SqlCommand() As SqlCommand
        Get
            Return _command
        End Get
        Set(ByVal Value As SqlCommand)
            _command = Value
        End Set
    End Property
    Public Property SqlConnection() As SqlConnection
        Get
            Return _conn
        End Get
        Set(ByVal Value As SqlConnection)
            _conn = Value
        End Set
    End Property
    Public Property Transaction() As SqlTransaction
        Get
            Return _trans
        End Get
        Set(ByVal Value As SqlTransaction)
            _trans = Value
        End Set
    End Property
    Public Property SqlConnectionString() As String
        Get
            Return _connectionstring
        End Get
        Set(ByVal Value As String)
            _connectionstring = Value
        End Set
    End Property
    Public Function Get_Dataset(ByVal storedprocedurename As String, _
                            
ByVal datatable As String) As DataSet
        Dim sqlDataAdapter As New SqlDataAdapter
        Try
            sqlDataAdapter.SelectCommand = New SqlCommand
            If _conn.ConnectionString = "" Then
               
_conn.ConnectionString = _connectionstring
            End If
            sqlDataAdapter.SelectCommand.Connection = _conn
            If Not _conn.State = ConnectionState.Open Then
                _conn.Open()
            End If
           
sqlDataAdapter.SelectCommand.CommandText = storedprocedurename
           
sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
            If Not IsNothing(_trans) Then
               
sqlDataAdapter.SelectCommand.Transaction = _trans
            End If

            'sqlDataAdapter.SelectCommand.ExecuteNonQuery()
            Dim myDataSet As DataSet = New DataSet
            sqlDataAdapter.Fill(myDataSet, datatable)
            Return myDataSet
        Catch ex As Exception
            Throw ex
        Finally
            If IsNothing(_trans) Then
                _conn.Close()
            End If

        End Try
    End Function
    Public Function Get_record_byID(ByVal myField As Field, ByVal storedprocedurename As String, _
                               
ByVal datatable As String) As DataSet
        Dim sqlDataAdapter As New SqlDataAdapter
        Try
            sqlDataAdapter.SelectCommand = New SqlCommand
            If _conn.ConnectionString = "" Then
               
_conn.ConnectionString = _connectionstring
            End If

            sqlDataAdapter.SelectCommand.Connection = _conn
            If Not _conn.State = ConnectionState.Open Then
                _conn.Open()
            End If

           
sqlDataAdapter.SelectCommand.CommandText = storedprocedurename
           
sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
            If Not IsNothing(_trans) Then
               
sqlDataAdapter.SelectCommand.Transaction = _trans
            End If
            'Parameters
            Dim myparam As SqlParameter
            myparam = New SqlParameter("@" & myField.ColumnName, _
                                                               
myField.ColumnType, myField.ColumnLength)
            myparam.Value = myField.ColumnValue
            myparam.Direction = ParameterDirection.Input
            sqlDataAdapter.SelectCommand.Parameters.Add(myparam)

            sqlDataAdapter.SelectCommand.ExecuteNonQuery()
            Dim myDataSet As DataSet = New DataSet
            sqlDataAdapter.Fill(myDataSet, datatable)
            Return myDataSet
        Catch ex As Exception
            Throw ex
        Finally
            If IsNothing(_trans) Then
                _conn.Close()
            End If
        End Try
    End Function
    Public Sub Delete_record(ByVal myField As Field, _
                               
ByVal storedprocedure As String)

        Dim mycommand As New SqlCommand
        mycommand.CommandType = CommandType.StoredProcedure
        Try
            mycommand.Connection = _conn
            If _conn.ConnectionString = "" Then
               
_conn.ConnectionString = _connectionstring
            End If
            If Not IsNothing(_trans) Then
                mycommand.Transaction = _trans
            End If
            mycommand.CommandText = storedprocedure

            If Not _conn.State = ConnectionState.Open Then
                _conn.Open()
            End If

            'Parameters
            Dim myparam As SqlParameter
            myparam = New SqlParameter("@" & myField.ColumnName, _
                                                               
myField.ColumnType, myField.ColumnLength)
            myparam.Value = myField.ColumnValue
            myparam.Direction = ParameterDirection.Input
            mycommand.Parameters.Add(myparam)

            mycommand.ExecuteNonQuery()
        Catch ex As Exception
            If IsNothing(_trans) Then
                _conn.Close()
            End If
        End Try
    End Sub
    Public Sub Update_Record(ByVal storedprocedure As String, _
                               
ByVal myFieldsCollection As FieldsCollection)
        Try
            Dim mycommand As New SqlCommand
            mycommand.CommandType = CommandType.StoredProcedure
            mycommand.CommandText = storedprocedure
            mycommand.Connection = _conn
            If _conn.ConnectionString = "" Then
               
_conn.ConnectionString = _connectionstring
            End If
            If Not IsNothing(_trans) Then
                mycommand.Transaction = _trans
            End If
            If Not _conn.State = ConnectionState.Open Then
                _conn.Open()
            End If
            'Parameters
            Dim myparam As SqlParameter
            For i As Integer = 0 To myFieldsCollection.Count - 1
                Dim obj As Field
                obj = myFieldsCollection.Item(i)

               
myparam = New SqlParameter("@" & obj.ColumnName, _
                                                               
obj.ColumnType, obj.ColumnLength)
                myparam.Value = obj.ColumnValue

               
myparam.Direction = ParameterDirection.Input
                mycommand.Parameters.Add(myparam)
            Next
            mycommand.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            If IsNothing(_trans) Then
                _conn.Close()
            End If
        End Try
    End Sub
End Class

To use all of the above we simply need stored procedures to retrieve values , insert records e.t.c.
Let us use the Northwind database. Here is a stored procedure for updating the Region table.

CREATE  PROCEDURE DBO.UPDATE_REGION
@REGIONDESCRIPTION VARCHAR (50),
@REGION_ID INT

AS
BEGIN
    UPDATE REGION
        SET REGIONDESCRIPTION = @REGIONDESCRIPTION
    WHERE REGIONID = @REGION_ID
END
GO

 

Wrap it up!
To use all of the above we reference the dll in our project. Then for example to update the Region table we use the following code:

Dim mx As New AccessingData
Try
       Dim myField1 As New Field("REGIONDESCRIPTION", SqlDbType.NChar, "REGION1", 50)
       Dim myCollection As New FieldsCollection
       myCollection.Add(myField1)
       myField1 = Nothing
       Dim myField2 As New Field("REGION_ID", SqlDbType.Int, 1, 4)
       myCollection.Add(myField2)
       myField2 = Nothing

       mx.Update_Record("UPDATE_REGION", myCollection)
Catch ex As Exception

End Try

We could easily use transaction simply by adding this code before the "Update_record" line:

'With transaction
mx.SqlConnection.ConnectionString = mx.SqlConnectionString
mx.SqlConnection.Open()
mx.Transaction = mx.SqlConnection.BeginTransaction()
'But after that dont forget to close the connection:
mx.SqlConnection.Close()

Comming Next
In the next article I will go a little bit deeper and use custom attributes :-)