Monday, July 23, 2012

ASP.NET: preventing twice click, when AutoGenerateEditButton="True" in gridview control

Custom controls of gridview and detailsview, allow to edit, insert or delete a record very easily.As you know, when user clicks, for example, "update" link, for saving the changes, can happens that the stored procedure takes some seconds to run.



For the end user, it is not easy understanding what the system is doing, nothing happens.

I tried to find over the internet some smart solution, like disabling the "update" link, on client side script. Unfortunately I didn't find any good solution for my scope.

Finally I remembered the great potentialities of <updatePanel> script.
Infact It is possible to put over the popup, developed using Jquery, an image, that denies any click on the page, until the stored procedure is finished.


 

Let's go now inside the code.

- Declare inside the popup window the script manager and update panel tags.
<form runat="server">
<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server" >
</asp:ToolkitScriptManager>
<A3G:UC_Spinner_02 ID="UC_Spinner" runat="server" associatedupdatepanelid="MainUpdatePanel" />
<asp:UpdatePanel runat="server" ID="MainUpdatePanel">
<ContentTemplate>
<asp:DetailsView ID="dwProductFix" runat="server" Height="50px" Width="100%" AutoGenerateRows="False" AutoGenerateEditButton="True" DefaultMode="Edit" ..../>
....

</ContentTemplate>
</asp:UpdatePanel>
</form>


- Create a user control that contains the image of "Loading" feature.
<%@ Control Language="vb" AutoEventWireup="false" CodeBehind="UC_Spinner_02.ascx.vb" Inherits="xxxxx.UC_Spinner_02" %>

<asp:updateprogress ID="updateprogress" runat="server" dynamiclayout="true" DisplayAfter="0">
<progresstemplate>
<div class="progressBackgroundFilter">
<div style="z-index: 10001; position: absolute;width: 124px;height:124px;left: 50%;
top:50%;margin-left: -62px;margin-top: -62px; text-align:center;">
<img src="/images/spinner3.gif" alt="loading..."></div>
</div>
</progresstemplate>
</asp:updateprogress>


- Remember to declare in web.config file the new user control.


<add tagPrefix="A3G" tagName="UC_Spinner_02" src="~/Controls/UC_Spinner_02.ascx" />

That's all.
Fabio Mariano

Saturday, June 23, 2012

SQL Server 2008 CLR Stored Procedure - Connect to remote Oracle Database

Good morning,
Recently I had to connect my Microsoft SQL Server 2008 R2 64Bit installed on Microsoft Windows Server 2008 64bit, with an external Oracle connection using CLR Stored Procedure.
I became crazy how to configure the OLEDB Provider, provided by Oracle (OraOLEDB.Oracle). Indeed Microsoft does not provide a 64-bit OLEDB Provider for Oracle. Thus, if you try to link an external Oracle database with the default installation, forget it.





Below I want to give you the steps that must be followed for running a CLR stored procedure that is linked to a remote Oracle database.
-         Download both 32 and 64 bit version from Oracle web site. It is mandatory to create a login and password. The links and files are these:
Application name: Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64)
File name: win64_11gR2_client.zip
Application name: Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (32-bit)
File name: win32_11gR2_client.zip

-          Create a new folder in the server like “C:\Oracle”

-          Launch the 32bit setup.exe.
-          Select “Custom installation”, otherwise you will consume 600MB for one DLL…
-          Set as default folder installation the new folder created previously (C:\Oracle)
-          Choose the following option to install:

-          Do the same with 64bit Setup.exe
-      Restart the server (it is not required, but I had issues try to connect the provider the first time)
-      If everything went well, you should see now the provider in the linker servers list.

-          If you need to link the server (this was not my case), you must execute the following transact SQL script.
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'DynamicParameters', 1
GO
Fantastic, now we can try to execute the CLR stored procedure.
Two couple of things to remember:
1) Remember to use OLEDBConnection objects in the code

Imports System.Data.OleDb

Dim sqlRemoteConnection As OleDbConnection = New OleDbConnection
Dim sqlRemoteCommand As OleDbCommand = New OleDbCommand


2) Setup the connection string properly. After several error messages coming out during the attempt connection, I finally used:
Provider=OraOLEDB.Oracle; Data Source=94.84.XXX.43;User ID=USERTEST; Password=PASSWORDTTEST
If you try to use the voice “Initial Catalog” you get an error (I read it is not supported in oracle).
Now executing the CLR stored Procedure, no errors come out, data are transferred correctly between two different data source system.

EXEC spIntDBNewTicket
 @IDIntegration = 'ST',
 @IDFromTo = 'COVER-ST',
 @ReturnMessage = null


That’s all.
Fabio