SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
drop PROC InsertGenerator
go
CREATE PROC InsertGenerator
(@tableName varchar(100)) as
--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
SET @string='INSERT '+@tableName+'('
SET @stringData=''
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @colName,@dataType
IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END
WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
--SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE
IF @dataType='datetime'
BEGIN
--SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
--SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
--SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
-- 'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
--SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
--SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END
SET @string=@string+@colName+','
FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
exec sp_executesql @query
--select @query
CLOSE cursCol
DEALLOCATE cursCol
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Tuesday, June 17, 2008
Thursday, February 28, 2008
Load and display page contents asynchronously with full postback support
Introduction
I was very interested in reading the article Incremental Page Display Pattern for User Controls [^] by Acoustic. He built a solution for real asynchronous loading of partial content.
His solution was good but had one weakness: he was unable to react to postbacks. Therefore controls were static. They could only display initial content, users were unable to interact with this controls.
I decided to write a completely new control that should support the following features:
- Developers should not have to write one line of Javascript
- Load user controls (.ascx) really asynchronously
- The user should be able to interact with the user controls
- It should not be necessary to render the whole page on updates like the AJAX UpdatePanel does.
To save server resources only the user control should be rendered - There should be only small communication overhead. That means that only the part of viewstate should be transfered back to the server that belongs to the user control. AJAX UpdatePanel transferes the viewstate of the whole page and all controls back to the server which could be a large one
- The control should support some minor features like auto updating every n milliseconds
Basic concepts
The complete control consists of a serverside control, a client side script part and a HttpHandler.
The rendering scenario of a ascx-Control is described in the following image:
(1) The aspx-Page contains a PartialUpdatePanel which has it's
UserControlPath-Property set to the .ascx-Control.<iucon:PartialUpdatePanel runat="server" ID="Panel1" UserControlPath="~/PostBackSample.ascx">
<ErrorTemplate>
Unable to refresh content
</ErrorTemplate>
<LoadingTemplate>
<div style="margin-left: 84px; margin-top: 10px;">
<asp:Image ID="Image1" runat="server" ImageUrl="~/images/loading.gif" />
</div>
<div style="text-align: center">
Updating...
</div>
</LoadingTemplate>
</iucon:PartialUpdatePanel>
(2) When the page loads, some JavaScript calls the PartialUpdatePanelHandler. It sends the path to the usercontrol and also other data loke the viewstate of the control via HTTP-POST.
// create request
var request = new Sys.Net.WebRequest();
request.set_url('PartialUpdatePanelLoader.ashx' + this._createQueryString());
request.set_httpVerb('POST');
request.set_body(this._createRequestBody(eventTarget, eventArgument));
request.set_userContext(this);
request.add_completed(this._loadingComplete);
(3) The user control needs a Page-object to be rendered in. This job is done by PanelHostPage.
// this code is part of PartialUpdatePanelHandler
if (context.Request.Form["__USERCONTROLPATH"] != null)
{
PanelHostPage page = new PanelHostPage(context.Request.Form["__USERCONTROLPATH"], context.Request.Form["__CONTROLCLIENTID"]);
((IHttpHandler)page).ProcessRequest(context);
context.Response.Clear();
context.Response.Write(page.GetHtmlContent());
}
(4) The user control get's normally rendered by simply adding it to the Control-Collection of the page
protected override void CreateChildControls()
{
// Load Control
if (_controlPath != null)
_mainForm.Controls.Add(LoadControl(ResolveUrl(_controlPath)));
base.CreateChildControls();
}
(5) The page output is send back to the HttpHandler.
(6) The contents are transfered to the client and inserted into the active HTML-document via DOM-operations.
contentPanel.innerHTML = sender.get_responseData();
That's all ;-)
ViewState
The contents of a PartialUpdatePanel are handled like this were the whole page. So it has it's own viewstate which is stored in a HiddenField.
The hidden field is transfered to the HttpHandler and loaded by PanelHostPage. The logic of loading serializing and unserializing the viewstate is done in two overridden methods in PanelHostPage.
private string _pageViewState;
protected override object LoadPageStateFromPersistenceMedium()
{
if (!string.IsNullOrEmpty(Request.Form["__VIEWSTATE"]))
{
LosFormatter format = new LosFormatter();
return format.Deserialize(Request.Form["__VIEWSTATE"]);
}
return null;
}
protected override void SavePageStateToPersistenceMedium(object state)
{
base.SavePageStateToPersistenceMedium(state);
LosFormatter format = new LosFormatter();
StringWriter writer = new StringWriter();
format.Serialize(writer, state);
_pageViewState = writer.ToString();
}
The contents of _pageViewState is transfered back to the client again as hidden input field.
Event handling
In ASP.NET event handling is quite simple. When a user clicks on a button for example, the ClientID of this button is passed back to the page in the __EVENTTARGET-field. This behaviour could be easily reproduced in out scenario. All we have to do is to add OnClientClick-calls for eventy button, call there a method that loads the partial content and adds the event source to the __EVENTTARGET-field that is passed to our HttpHandler. We have nothing more to do, the rest is up to the ASP.NET event pipeline.
Using the control
Before using the Control, you have to register the HttpHandler in your web.config:
<httpHandlers>
<add verb="*" path="*.ashx" validate="false" type="iucon.web.Controls.PartialUpdatePanelHandler"/>
</httpHandlers>
Then simply add a PartialUpdatePanel to your page and set the UserControlPath-Property.
Add some controls to the LoadingTemplate and ErrorTemplate. The contents of the LoadingTemplate become visible when update operations are in progress. Contents of the ErrorTemplate are shown, if an error occured during the update (e.g. server timeout).
If you set to AutoRefreshInterval a value greater then 0, the panel refreshes every n milliseconds automatically.
There is also a mechanism to communicate with the control via javascript or server side code.Parameters is a collection that takes key-value-pairs. There are passed to the control via HTTP-GET when it is rendered.
The following sample shows how to provide some parameters in the aspx-page, set values via JavaScript and read them in your ascx-Control.
<%-- PartialUpdatePanel with named parameters --%>
<iucon:PartialUpdatePanel runat="server" ID="PartialUpdatePanel4" UserControlPath="~/ParameterSample.ascx">
<Parameters>
<iucon:Parameter Name="MyParameter" Value="Hello world" />
<iucon:Parameter Name="Counter" Value="0" />
</Parameters>
<ErrorTemplate>
Unable to refresh content
</ErrorTemplate>
</iucon:PartialUpdatePanel>
<%-- Change the value of the parameter "Counter" and refresh the panel --%>
<script type="text/javascript">
var counter = 0;
function updateParameterSample()
{
$find("PartialUpdatePanel4").get_Parameters()["Counter"] = ++counter;
$find("PartialUpdatePanel4").refresh();
}
</script>
<input type="button" onclick="updateParameterSample(); return false;" value="Click to update panel with counter" />
The code behind of the ascx reads the parameter values from the QueryString
public partial class ParameterSample : System.Web.UI.UserControl
{
protected void Page_Load(object sender, EventArgs e)
{
Label1.Text = "Called " + Request.QueryString["Counter"] + " times";
}
}
The property InitialRenderBehaviour controls if the control should be rendered by the server during the normal page rendering (InitialRenderBehaviour.Serverside).
If this property is set to InitialRenderBehaviour.Clientside the client sends a request to the server to render the control when the page is already transfered to the browser. This is useful to the user if a part of the page needs long time to render, but the user should be able to see other parts already. He does not need to wait for the whole page, but can already play with some parts while others are still loading.InitialRenderBehaviour.None causes the control not to render until it is requested via the JScript call $find("PartialUpdatePanel4").refresh(); as shown in one of the above samples. In this case the <InitialTemplate> contents are shown until a refresh-call occurs.
<iucon:PartialUpdatePanel runat="server" ID="PartialUpdatePanel1" UserControlPath="~/ExternalRefreshSample.ascx"
InitialRenderBehaviour="None">
<ErrorTemplate>
Unable to refresh content
</ErrorTemplate>
<LoadingTemplate>
Updating...
</LoadingTemplate>
<InitialTemplate>
Nothing useful here until refresh() gets called
</InitialTemplate>
</iucon:PartialUpdatePanel>
History & Updates
| 28 Feb 2008 | Version 1.2.1
|
| 27 Feb 2008 | Version 1.2
|
| 20 Feb 2008 | Initial release |
The project is hosted on CodePlex. The updates there uploaded in smaller intervals. So if you are interested in this project, you should visit this site regularly.
http://www.codeplex.com/PartialUpdatePanel [^]
One last word
If you use this control I would be happy, if you sent me the URL of your project so I can see the control in action.
And... please don't forget to vote for this article.
Subscribe to:
Posts (Atom)
