create table #tmp (
SQLText varchar(8000) )
create table #tmp2 (
Id int identity,
SQLText varchar(8000) )
set nocount on
delete #tmp
delete #tmp2
declare @vsSQL varchar(8000),
@vsCols varchar(8000),
@vsTableName varchar(40)
declare csrTables cursor for
select name
from sysobjects
where type in ('u')
and name in ('Customers')
order by name
open csrTables
fetch next from csrTables into @vsTableName
while (@@fetch_status = 0)
begin
select @vsSQL = '',
@vsCols = ''
select @vsSQL = @vsSQL +
CASE when sc.type in (39,47,61,111) then
'''''''''+' + 'isnull(rtrim(replace('+ sc.name + ','''''''','''''''''''')),'''')' + '+'''''',''+'
when sc.type = 35 then
'''''''''+' + 'isnull(rtrim(replace(substring('+ sc.name + ',1,1000),'''''''','''''''''''')),'''')' + '+'''''',''+'
else
'isnull(convert(varchar,' + sc.name + '),''null'')+'',''+'
end
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID
select @vsCols = @vsCols + sc.name + ','
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID
select @vsSQL = substring(@vsSQL,1,datalength(@vsSQL)-1)
select @vsCols = substring(@vsCols,1,datalength(@vsCols)-1)
insert #tmp
exec ('select ' + @vsSQL + ' from ' + @vsTableName)
update #tmp
set sqltext = 'insert ' + @vsTableName + '(' + @vsCols + ') values(' + substring(sqltext,1,datalength(sqltext)-1) + ')'
insert #tmp2
select 'DELETE from ' + @vsTableName
insert #tmp2 values ('GO')
if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert #tmp2
select 'set identity_insert ' + @vsTableName + ' on'
end
insert #tmp2
select * from #tmp
if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert #tmp2
select 'set identity_insert ' + @vsTableName + ' off'
end
insert #tmp2 values ('GO')
insert #tmp2
select 'update statistics ' + @vsTableName
insert #tmp2 values ('GO')
delete #tmp
fetch next from csrTables into @vsTableName
end
close csrTables
deallocate csrTables
update #tmp2
set sqltext = substring(sqltext,1,charindex(',)',sqltext)-1) + ',NULL)'
where not(charindex(',)',sqltext) = 0)
update #tmp2
set sqltext = replace(sqltext, ',''''',',null')
where not (charindex(',''''',sqltext) = 0)
update #tmp2
set sqltext = replace(sqltext, '(''''',',null')
where not (charindex('(''''',sqltext) = 0)
set nocount off
select sqltext from #tmp2 order by id
go
drop table #tmp
drop table #tmp2
Overview Ever needed to generate Word documents from your ASP.NET applications? There are a couple of components which will help to generate Word documents, but using these components may have some overhead such as Component Registration, setting permissions, licenses, etc., Sometimes, it may even become difficult to understand their features and use them. Generating word document in ASP.NET with HTML is incredibly easy. The following sample demonstrates how to place Heading and Table dynamically in the word document in ASP.NET web applications using HTML. Requirements Microsoft Visual Web Developer 2005 Express Edition Create the Web Application project Add the new Web Site Application project (with name as Generate Word Document) and place the button (with name as btnGenerateDocument) in the Default.aspx web form as shown below. Double click the highlighted code behind file from the Solution Explorer and add the following namespaces. Listing 1 using System...
Comments
Post a Comment