Slice of IT

Sunday, December 15, 2013

WIX Installer RemotePayload Check for .NET 4.5.1

To get the RemotePayload to check and download .NET 4.5.1 and to check if you have .NET 4.5.1 installed on a Windows 8.1, 8, 7 or Vista, Use the code below which can be found by going to CodePlex and download Wix38-debug.zip
http://wix.codeplex.com/releases/view/115492

Download and extract all and navigate to src\ext\NetFxExtension\wixlib
In there you will find all files for all .NET frameworks. Pick the NetFx451.wxs and copy all code in there into your Bootstrapper installer application.

The language packs are included with the web exe installer, in the redist. they are not included.
see http://msdn.microsoft.com/en-us/library/5a4x27ek(v=vs.110).aspx

Build and Run, and it will download and install .NET 4.5.1.

Note that you need to replace the attribute values in Bundle element: MyApplicationName, MyCompanyName and MyUpgradeCode.

Also, I am executing my Wix Setup Project after checking .NET 4.5.1 in the Bootstrap project
"<MsiPackage SourceFile="$(var.InstallerSetup.TargetPath)" Compressed="yes" />"

To see how to check the .NET framework version:
http://msdn.microsoft.com/en-us/library/hh925568(v=vs.110).aspx

<?xml version="1.0" encoding="utf-8"?>
<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi" xmlns:util="http://schemas.microsoft.com/wix/UtilExtension" xmlns:bal="http://schemas.microsoft.com/wix/BalExtension">
<Bundle Name="MyApplicationName" Version="1.0.0.0" Manufacturer="MyCompanyName"
UpgradeCode="MyUpgradeCode">
<BootstrapperApplicationRef Id="WixStandardBootstrapperApplication.RtfLicense" />

<Chain>
<PackageGroupRef Id="NetFx451Web"/>
<MsiPackage SourceFile="$(var.InstallerSetup.TargetPath)" Compressed="yes" />
</Chain>
</Bundle>

<?define NetFx451MinReleaseWin8_1 = 378675 ?>

<?define NetFx451WebLink = http://go.microsoft.com/fwlink/?LinkId=322115 ?>

<?define NetFx45EulaLink = http://go.microsoft.com/fwlink/?LinkID=260867 ?>

<Fragment>
<util:RegistrySearch
Id="NETFRAMEWORK45"
Variable="NETFRAMEWORK45"
Root="HKLM"
Key="SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full"
Value="Release"
Result="value" />
</Fragment>
<Fragment>
<util:RegistrySearchRef Id="NETFRAMEWORK45"/>

<PackageGroup Id="NetFx451Web">
<ExePackage
InstallCommand="/q /norestart /ChainingPackage "[WixBundleName]" /log "[NetFx451FullWebLog].html""
RepairCommand="/q /norestart /repair /ChainingPackage "[WixBundleName]" /log "[NetFx451FullWebLog].html""
UninstallCommand="/uninstall /q /norestart /ChainingPackage "[WixBundleName]" /log "[NetFx451FullWebLog].html""
PerMachine="yes"
DetectCondition="NETFRAMEWORK45 >= $(var.NetFx451MinReleaseWin8_1)"
Id="NetFx451Web"
Vital="yes"
Permanent="yes"
Protocol="netfx4"
DownloadUrl="$(var.NetFx451WebLink)"
LogPathVariable="NetFx451FullWebLog"
Compressed="no"
Name="redist\NDP451-KB2859818-Web.exe">
<RemotePayload
Size="1021432"
Version="4.5.50938.18408"
ProductName="Microsoft .NET Framework 4.5.1"
Description="Microsoft .NET Framework 4.5.1 Setup"
CertificatePublicKey="A260A870BE1145ED71E2BB5AA19463A4FE9DCC41"
CertificateThumbprint="108E2BA23632620C427C570B6D9DB51AC31387FE"
Hash="4CBEA1E408DB5B423E130931B9478972E6798431" />
</ExePackage>
</PackageGroup>
</Fragment>




<Fragment>
<Property Id="NETFRAMEWORK45" Secure="yes">
<RegistrySearch Id="NetFramework45" Root="HKLM" Key="SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_AR_SA_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45ArSaLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1025" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_ZH_CN_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45ZhCnLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\2052" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_ZH_TW_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45ZhTwLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1028" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_CS_CZ_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45CsCzLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1029" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_DA_DK_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45DaDkLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1030" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_NL_NL_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45NlNlLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1043" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_FI_FI_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45FiFiLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1035" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_FR_FR_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45FrFrLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1036" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_DE_DE_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45DeDeLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1031" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_EL_GR_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45ElGrLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1032" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_HE_IL_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45HeIlLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1037" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_HU_HU_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45HuHuLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1038" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_IT_IT_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45ItItLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1040" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_JA_JP_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45JaJpLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1041" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_KO_KR_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45KoKrLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1042" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_NB_NO_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45NbNoLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1044" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_PL_PL_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45PlPlLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1045" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_PT_BR_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45PtBrLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1046" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_PT_PT_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45PtPtLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\2070" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_RU_RU_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45RuRuLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1049" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_ES_ES_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45EsEsLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\3082" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_SV_SE_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45SvSeLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1053" Name="Release" Type="raw" />
</Property>
</Fragment>


<Fragment>
<Property Id="NETFRAMEWORK45_TR_TR_LANGPACK" Secure="yes">
<RegistrySearch Id="NETFRAMEWORK45TrTrLp" Root="HKLM" Key="Software\Microsoft\NET Framework Setup\NDP\v4\Full\1055" Name="Release" Type="raw" />
</Property>
</Fragment>
</Wix>

Monday, July 29, 2013

Eager Loading in Linq To Sql : Two ways

Two ways for Eager Loading in Linq to SQL

The first option is the DataLoadOptions, and the second is to utilize a ViewModel class.

DataLoadOptions

The first one is easy and simple to code, but not very good if you want to write back data to the database.

[HttpGet]
public ActionResult WebsiteImages()
{
    Web.WebDB db = new Web.WebDB();
    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<Web.Model.WebsiteImage>(x => x.Website);
    options.LoadWith<Web.Model.WebsiteImage>(c => c.WebsiteLanguage);
    db.LoadOptions = options;

    IEnumerable<Web.Model.WebsiteImage> websiteImages = db.WebsiteImages;
    return View(websiteImages);
}

ViewModel Class
The second option is a little bit more work, but you can easily persist data to the database on post backs. This option requires you to create a custom class to hold the reference data.

[HttpGet]
public ActionResult WebsiteImages()
{
    Web.WebDB db = new Web.WebDB();
    WebViewModel webVM = new WebViewModel();
    webVM.WebsiteImagesLinking = (from wi in db.WebsiteImages
                         select new WebsiteImagesVM
                         {
                             ID = wi.ID,
                             WebsiteImage = wi.WebsiteImage1,
                             Website = wi.Website,
                             WebsiteID = wi.WebsiteID,
                             WebsiteLanguage = wi.WebsiteLanguage,
                             Image = wi.Image,
                             ImageID = wi.ImageID,
                             MinWidth = wi.MinWidth,
                             MinHeight = wi.MinHeight,
                             MaxWidth = wi.MaxWidth,
                             MaxHeight = wi.MaxHeight,
                             Notes = wi.Notes
                         }).ToList();
      return View(webVM);
}

public List<WebsiteImagesVM> WebsiteImagesLinking { get; set; }

public class WebsiteImagesVM
    {
        public int ID { get; set; }
        public string WebsiteImage { get; set; }
        public int WebsiteID { get; set; }
        public Web.Model.Website Website { get; set; }
        public Web.Model.WebsiteLanguage WebsiteLanguage { get; set; }
        public int? ImageID { get; set; }
        public Web.Model.Image Image { get; set; }
        public int? MinWidth { get; set; }
        public int? MinHeight { get; set; }
        public int? MaxWidth { get; set; }
        public int? MaxHeight { get; set; }
        public string Notes { get; set; }
        public bool Mapped { get; set; }
    }

Thursday, May 30, 2013

Getting column value - Bad vs. Good Linq to Sql

Bad
int mainDepID = db.Departments.SingleOrDefault(y => y.ID == depID).MainDepartmentID;

Good
int mainDepID = 0;

Web.Model.Department currentDepartment = db.Departments.SingleOrDefault(y => y.ID == depID);

if (currentDepartment != null)
{
mainDepID = currentDepartment.MainDepartmentID;
}

Friday, May 24, 2013

Cool Stuff: Use array to query values using Linq to Sql

You can use an array to query for records using linq to sql...

 int[] myList = new int[] { 2625, 2666 };

 var myResult = from st in db.SomeTable
         

               where myList.Contains(st.ID)
     

               select st;

Might be useful sometimes?

Thursday, May 23, 2013

ValidateAntiForgeryToken with postback and json in MVC

ValidateAntiForgeryToken as explained here from Stack Overflow

"MVC's Anti-Forgery Token support writes a unique value to an HTTP-only cookie and then the same value is written to the form. When the page is submitted, an error is raised if the cookie value doesn't match the form value.

It's important to note that the feature prevents cross site request forgeries. That is, a form from another site that posts to your site in an attempt to submit hidden content using an authenticated user's credentials. The attack involves tricking the logged in user into submitting a form.

The feature doesn't prevent any other type of data forgery or tampering based attacks."

To use this feature in MVC, you need to add the [HttpPost, Authorize, ValidateAntiForgeryToken] attribute to your HttpPost methods.

Example:

[HttpPost, Authorize, ValidateAntiForgeryToken]
public ActionResult MyPostBackMethod(string MyTextInput, string MyDropDown){

   //Do some stuff

In your view, you also need to add the following, if we are speaking Razor

@using (Html.BeginForm())

{
    @Html.AntiForgeryToken()

    @*Some HTML*@

Now, if we have some client side scripts, this will not work as the HttpPost method is expecting to use the ValidateAntiForgeryToken attribute.

What we need to do is simply add the following value to our json response

__RequestVerificationToken: $('[name=__RequestVerificationToken]').val()

Example:

<script type="text/javascript">

        var data = {
            MyTextInput: $('#txbMyTextInput' + id).val(),
            MyDropDown: $('#cbMyDropDown' + id).is(':checked'),
            __RequestVerificationToken: $('[name=__RequestVerificationToken]').val()
        };
 
        $.post('MyPostBackMethod', data,
        function (result) {
           //do something with result
        }, 'json');

<script />

There is no need to add any parameters on our method on the server side.

Wednesday, March 27, 2013

Validating Linq to Sql Model

If you have created a Linq to Sql class as your model and need to add validation to it, simply create an interface and add the columns needed to validate.

ILog interface class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations;
 
namespace Test1.Models
{
    interface ILog
    {
        [Required]
        [RegularExpression(@"(\s|.){1,50}$", ErrorMessage = "Field cannot contain more than 50 characters")]
        string Name { get; set; }
 
        [Required]
        [RegularExpression(@"[0-9]*", ErrorMessage = "Field must be an integer")]
        int TableKey { get; set; }
 
        [Required]
        [RegularExpression(@"(\s|.){1,50}$", ErrorMessage = "Field cannot contain more than 500 characters")]
        string Value { get; set; }
    }
}

Log LinqToSql class:

using Test1.Models;
using System.ComponentModel.DataAnnotations;
 
namespace Log.Model
{
    [MetadataType(typeof(ILog))]
    partial class Log : ILog
    {
    }
}


Simple enough?

For larger models, I suggest creating a ViewModel and handle everything in there...

Friday, January 4, 2013

The Connect Class ... that ppl tend to 'Create and Forget'

Here is a class that people usually create to take care of all the database calls.

To use the connect class, you can call it using the following example:

     masterPage.Connect.StartTransaction()
  
     Dim parameters As New List(Of SqlParameter)
  
     parameters.Add(New SqlParameter("@UserName", SqlDbType.VarChar, 75, ParameterDirection.Input, False, 0, 0, String.Empty, DataRowVersion.Current, "Test"))
  
     parameters.Add(New SqlParameter("@Password", SqlDbType.VarChar, 25, ParameterDirection.Input, False, 0, 0, String.Empty, DataRowVersion.Current, 0))
  
     Dim ds = masterPage.Connect.FillDataset("ValidateUser", parameters, True)

I have added transaction to the class where you can create a transaction and then call all your stored procedures and tell them if you want them to be part of this transaction or not.

 masterPage.Connect.StartTransaction()
  
 masterPage.Connect.EndTransaction()
  
 masterPage.Connect.RollbackTransaction()

And the Connect class...

 Imports System
  
 Imports System.IO
  
 Imports System.Text
  
 Imports System.Data
  
 Imports System.Data.SqlClient
  
 Namespace Connect
  
   ''' <summary>
  
   ''' <para>Connect class provides connection for various type of external data. Class is not inheritable.</para>
  
   ''' </summary>
  
   Public Class ConnectMe
  
 #Region "Enum"
  
     Public Enum SQLCall
  
       DataSet = 0
  
       DataTable = 1
  
       NonQuery = 2
  
       Reader = 3
  
       Scalar = 4
  
       BulkCopy = 5
  
     End Enum
  
 #End Region
  
 #Region "Constructor"
  
     Public Sub New(ByVal _connString As String)
  
       If Not String.IsNullOrEmpty(_connString) Then Connection = New SqlConnection(_connString)
  
     End Sub
  
     ''' <summary>
  
     ''' Initializes a new instance of the Connect class.
  
     ''' </summary>
  
     Private Sub New()
  
     End Sub
  
 #End Region
  
 #Region "Transaction"
  
     Private transaction As SqlTransaction = Nothing
  
     Public Sub StartTransaction()
  
       Try
  
         If Not Connection Is Nothing AndAlso transaction Is Nothing Then
  
           Connection.Open()
  
           transaction = Connection.BeginTransaction()
  
         End If
  
       Catch ex As Exception
  
         CleanupErrorConnections()
  
         MsgBox(ex.ToString)
  
       End Try
  
     End Sub
  
     Public Sub EndTransaction()
  
       Try
  
         If Not transaction Is Nothing Then transaction.Commit()
  
         If Not transaction Is Nothing Then transaction.Dispose()
  
         If Not Connection Is Nothing AndAlso (Connection.State = ConnectionState.Open) Then Connection.Close()
  
       Catch ex As Exception
  
         MsgBox(ex.ToString)
  
       End Try
  
     End Sub
  
     Public Sub RollbackTransaction()
  
       Try
  
         If Not transaction Is Nothing Then transaction.Rollback()
  
         If Not transaction Is Nothing Then transaction.Dispose()
  
         If Not Connection Is Nothing AndAlso (Connection.State = ConnectionState.Open) Then Connection.Close()
  
       Catch ex As Exception
  
         MsgBox(ex.ToString)
  
       End Try
  
     End Sub
  
 #End Region
  
 #Region "Cleanup"
  
     Public Sub CleanupErrorConnections()
  
       If Not transaction Is Nothing Then transaction.Rollback()
  
       If Not transaction Is Nothing Then transaction.Dispose()
  
       If Not Connection Is Nothing AndAlso (Connection.State = ConnectionState.Open) Then Connection.Close()
  
     End Sub
  
     Public Sub CleanupConnections()
  
       If Not transaction Is Nothing Then transaction.Dispose()
  
       If Not Connection Is Nothing AndAlso (Connection.State = ConnectionState.Open) Then Connection.Close()
  
     End Sub
  
 #End Region
  
 #Region " Public Properties "
  
     ''' <summary>
  
     ''' Gets or sets the string used to open a SQL Server database.
  
     ''' </summary>
  
     ''' <returns>The connection string that includes the source database name, and other parameters needed to establish the initial connection.</returns>
  
     Public Property Connection() As SqlConnection
  
       Get
  
         Return _connection
  
       End Get
  
       Set(ByVal value As SqlConnection)
  
         _connection = value
  
       End Set
  
     End Property
  
     Private _connection As SqlConnection = Nothing
  
 #End Region
  
 #Region " Private Methods "
  
     Private Sub AssignParameters(ByRef cmd As SqlCommand, ByRef cmdParameters As List(Of SqlParameter))
  
       If (cmdParameters Is Nothing) Then Exit Sub
  
       For Each p As SqlParameter In cmdParameters
  
         cmd.Parameters.Add(p)
  
       Next
  
     End Sub
  
 #End Region
  
 #Region "Dataset"
  
     ''' <summary>
  
     ''' Adds or refreshes rows in the System.Data.DataSet to match those in the data source using the System.Data.DataSet name, and creates a System.Data.DataTable named "Table."
  
     ''' DataGridView1.DataSource = sqldb.FillDataset("Select * From dbo.Users", CommandType.Text).Tables(0)
  
     ''' </summary>
  
     ''' <param name="cmd">The Transact-SQL statement or stored procedure to execute at the data source.</param>
  
     ''' <param name="cmdType">A value indicating how the System.Data.SqlClient.SqlCommand.CommandText property is to be interpreted.</param>
  
     ''' <param name="parameters">The parameters of the Transact-SQL statement or stored procedure.</param>
  
     ''' <returns>A System.Data.Dataset object.</returns>
  
     Public Function FillDataSet(ByVal _spName As String, Optional ByVal _parameters As List(Of SqlParameter) = Nothing, Optional ByVal _useTransaction As Boolean = False) As DataSet
  
       Dim sqlda As SqlDataAdapter = Nothing
  
       Dim resultObj As DataSet = Nothing
  
       Try
  
         Using command As New SqlCommand(_spName, Connection)
  
           resultObj = New DataSet
  
           command.CommandType = CommandType.StoredProcedure
  
           ''Assign parameters to command
  
           Me.AssignParameters(command, _parameters)
  
           ''Check if using transaction
  
           If _useTransaction Then command.Transaction = transaction
  
           sqlda = New SqlDataAdapter(command)
  
           sqlda.Fill(resultObj)
  
         End Using
  
       Catch ex As SqlException
  
         CleanupErrorConnections()
  
         MsgBox(ex.ToString)
  
       Catch ex As Exception
  
         CleanupErrorConnections()
  
         MsgBox(ex.ToString)
  
       Finally
  
         If Not _useTransaction Then CleanupConnections()
  
       End Try
  
       Return resultObj
  
     End Function
  
 #End Region
  
 #Region "DataTable"
  
     Public Function FillDataTable(ByVal _spName As String, Optional ByVal _parameters As List(Of SqlParameter) = Nothing, Optional ByVal _useTransaction As Boolean = False) As DataTable
  
       Dim sqlda As SqlDataAdapter = Nothing
  
       Dim resultObj As DataTable = Nothing
  
       Try
  
         Using command As New SqlCommand(_spName, Connection)
  
           resultObj = New DataTable
  
           command.CommandType = CommandType.StoredProcedure
  
           ''Assign parameters to command
  
           Me.AssignParameters(command, _parameters)
  
           ''Check if using transaction
  
           If _useTransaction Then command.Transaction = transaction
  
           sqlda = New SqlDataAdapter(command)
  
           sqlda.Fill(resultObj)
  
         End Using
  
       Catch ex As SqlException
  
         CleanupErrorConnections()
  
         MsgBox(ex.ToString)
  
       Catch ex As Exception
  
         CleanupErrorConnections()
  
         MsgBox(ex.ToString)
  
       Finally
  
         If Not _useTransaction Then CleanupConnections()
  
       End Try
  
       Return resultObj
  
     End Function
  
 #End Region
  
 #Region "NonQuery"
  
     Public Function ExecuteNonQuery(ByVal _spName As String, Optional ByVal _parameters As List(Of SqlParameter) = Nothing, Optional ByVal _useTransaction As Boolean = False) As Integer
  
       Dim resultObj As Integer = Nothing
  
       Try
  
         Using command As New SqlCommand(_spName, Connection)
  
           command.CommandType = CommandType.StoredProcedure
  
           ''Assign parameters to command
  
           Me.AssignParameters(command, _parameters)
  
           ''Check if using transaction
  
           If _useTransaction Then command.Transaction = transaction
  
           resultObj = command.ExecuteNonQuery()
  
         End Using
  
       Catch ex As SqlException
  
         CleanupErrorConnections()
  
         MsgBox(ex.ToString)
  
       Catch ex As Exception
  
         CleanupErrorConnections()
  
         MsgBox(ex.ToString)
  
       Finally
  
         If Not _useTransaction Then CleanupConnections()
  
       End Try
  
       Return resultObj
  
     End Function
  
 #End Region
  
 #Region "Reader"
  
     Public Function ExecuteReader(ByVal _spName As String, Optional ByVal _parameters As List(Of SqlParameter) = Nothing, Optional ByVal _useTransaction As Boolean = False) As IDataReader
  
       Dim resultObj As SqlDataReader = Nothing
  
       Try
  
         Using command As New SqlCommand(_spName, Connection)
  
           command.CommandType = CommandType.StoredProcedure
  
           ''Assign parameters to command
  
           Me.AssignParameters(command, _parameters)
  
           ''Check if using transaction
  
           If _useTransaction Then command.Transaction = transaction
  
           resultObj = command.ExecuteReader(CommandBehavior.CloseConnection)
  
         End Using
  
       Catch ex As SqlException
  
         CleanupErrorConnections()
  
         MsgBox(ex.ToString)
  
       Catch ex As Exception
  
         CleanupErrorConnections()
  
         MsgBox(ex.ToString)
  
       Finally
  
         If Not _useTransaction Then CleanupConnections()
  
       End Try
  
       Return CType(resultObj, IDataReader)
  
     End Function
  
 #End Region
  
 #Region "Scalar"
  
     Public Function ExecuteScalar(ByVal _spName As String, Optional ByVal _parameters As List(Of SqlParameter) = Nothing, Optional ByVal _useTransaction As Boolean = False) As Object
  
       Dim resultObj As Object = Nothing
  
       Try
  
         Using command As New SqlCommand(_spName, Connection)
  
           command.CommandType = CommandType.StoredProcedure
  
           ''Assign parameters to command
  
           Me.AssignParameters(command, _parameters)
  
           ''Check if using transaction
  
           If _useTransaction Then command.Transaction = transaction
  
           resultObj = command.ExecuteScalar()
  
         End Using
  
       Catch ex As SqlException
  
         CleanupErrorConnections()
  
         MsgBox(ex.ToString)
  
       Catch ex As Exception
  
         CleanupErrorConnections()
  
         MsgBox(ex.ToString)
  
       Finally
  
         If Not _useTransaction Then CleanupConnections()
  
       End Try
  
       Return CType(resultObj, IDataReader)
  
     End Function
  
 #End Region
  
 #Region "BulkCopy"
  
     Public Function ExecuteBulkCOpy(ByVal _spName As String, ByVal _desTableName As String, ByVal _sourceTable As DataTable, Optional ByVal _useTransaction As Boolean = False) As Object
  
       Dim resultObj As Object = Nothing
  
       Try
  
         If _useTransaction Then
  
           Using bulkCopy As New SqlBulkCopy(Connection, SqlBulkCopyOptions.Default, transaction)
  
             bulkCopy.DestinationTableName = _desTableName
  
             For Each c As DataColumn In _sourceTable.Columns
  
               Dim map As New SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)
  
               bulkCopy.ColumnMappings.Add(map)
  
             Next
  
             bulkCopy.WriteToServer(_sourceTable)
  
           End Using
  
         Else
  
           Using bulkCopy As New SqlBulkCopy(Connection)
  
             bulkCopy.DestinationTableName = _desTableName
  
             For Each c As DataColumn In _sourceTable.Columns
  
               Dim map As New SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)
  
               bulkCopy.ColumnMappings.Add(map)
  
             Next
  
             bulkCopy.WriteToServer(_sourceTable)
  
           End Using
  
         End If
  
       Catch ex As SqlException
  
         CleanupErrorConnections()
  
         MsgBox(ex.ToString)
  
       Catch ex As Exception
  
         CleanupErrorConnections()
  
         MsgBox(ex.ToString)
  
       Finally
  
         If Not _useTransaction Then CleanupConnections()
  
       End Try
  
       Return CType(resultObj, IDataReader)
  
     End Function
  
 #End Region
  
   End Class
  
 End Namespace