SQL SERVER: Execute SSIS Package Using Store Procedure

Originally, I used 'Microsoft.SqlServer.Dts.Runtime'to execute local SSIS package under asp.net Web application. Under client's environment, SQL Server and web application are hosted on different Windows servers. Now, I use store procedure to execute SSIS package placed in DB server.

 
set @params = '/conn \"a\";"\"' + @a+ '\"" /set \package.variables[b].Value;"\"' + @b+ '\""'
set @cmd = 'C:\dtexec.exe /f ' + @PackagePath + ' ' + @params
EXEC xp_cmdshell @cmd

Dont forget to enable 'xp_cmdshell' option first!

I had to used 32 bit version of dtexec.exe as my package need OLEDB provider Jet 4.0. The provider is not available under 32 bit environment. As a result, I use the x86 version to handle this issue.

Errors when executing SQL Server SSIS Package From Stored Procedure
How to Call SSIS Package from the Stored Procedure

Allow downloading mdb file from ASP.Net page

I would like to let users download a file after they click a button. I just use Response.Redirect("~/123.mdb") to handle this situation. 

But, an error message is shown: 
"There is no build provider registered for the extension '.mdb'. You can register one in the section in machine.config or web.config"

To Deal with this issue, go to web.config, add a fileExtension element in web.config

FileExtensions in web.config

Call SSIS package in C#

I need to call a SSIS package in a web application to import access data into SQL Server. However, I discover the performance is extremely low. And I don't know it's my personal problem or common issue. If possible, don't use it.

Package package = null;
DTSExecResult result;

//connection manager
package = new Application().LoadPackage(packagePath + "\\importData.dtsx", null);
package.Connections["rs"].ConnectionString = connectionStr;

//variable
Variables vars = package.Variables;
vars["TenderNumber"].Value = tenderNum;

result = package.Execute();

if (result == DTSExecResult.Failure)
{
    foreach (var e in package.Errors)
    {
        logHelper.Error("Package Execution Error", new Exception(e.Description));
    }

    throw new Exception(package.Errors[0].ToString());
}

CommandTimeout in Entity Framework

I use Entity Framework in my web application and then use it to execute a stored procedure command in SQL Server. Unluckily, I receive a connection timeout issue. First, I try to add 'Connection Timeout=120;' into connection string. But, the problem still exists. Then, solving the issue by CommandTimeout 

using (RSEntities rEntity = new RSEntities())
{
  //import data from temp tables
  rEntity.CommandTimeout = 120;
  rEntity.ExecuteStoreCommand("EXEC dbo.ImportMdbData");
}

Blank option in ASP.NET DropDownList

I would like to create an blank option in DropDownList by default and don't use append to add options into the list. So, I bind the datasource first and then insert the blank option afterwards.

.aspx.cs

DropDownTenderNumber.DataSource = list;
DropDownTenderNumber.DataBind();
DropDownTenderNumber.Items.Insert(0, new ListItem("----PLEASE SELECT----", "0"));

Further, I use 'InitialValue' in RequiredFieldValidator to exclude the blank option.

.aspx

Freeze/Lock Web Page While Processing in ASP.Net

I would like to create a layer to cover a page while it is processing. First, downloading a loading icon from ajaxload. Then, you just need to paste following code in .aspx file.

UpdateProgress

UpdatePanel

Script Manager

CSS

.overlay  
{
    position: fixed;
    z-index: 98;
    top: 0px;
    left: 0px;
    right: 0px;
    bottom: 0px;
    background-color: #aaa; 
    filter: alpha(opacity=80); 
    opacity: 0.8; 
}
.overlayContent
{
    z-index: 99;
    margin: 250px auto;
    width: 80px;
    height: 80px;
}
.overlayContent h2
{
    font-size: 18px;
    font-weight: bold;
    color: #000;
}
.overlayContent img
{
    width: 60px;
    height: 60px;
}

Use UpdateProgress To Cover Screen

I have tried to browse it use chrome, firefox and ie and it works fine.

globalization Element in web.config

I would like to provide a textbox to user and let them filter data by a given date in (dd/MM/yyyy) format.

query = query.Where(i => i.OperationDate <= end);

Unfortunately, query results seems not working properly. It seems that SQL Server uses date format (MM/dd/yyyy) to do the searching. Then, the question is how to specify the date format in my web application. By adding following line in web.config, the issue is solved.

CultureInfo Class:
Represents information about a specific culture including the names of the culture,the writing system, and the calendar used, as well as access to culture-specific objects that provide information for common operations, such as formatting dates and sorting strings.