Posted By

lmontealegre on 02/06/13


Tagged


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

danfinrud55


Export extracted data to SQL Server using an export script


 / Published in: C#
 

The script will export data into existing tabels in SQL Server. The tables will use identity integer primary keys instead of the unique identifier keys Visual Web Ripper uses by default. New Tables Needed:

  1. //------------------------------------
  2. CREATE TABLE [dbo].[VacationHome](
  3. [VacationHomeId] [int] IDENTITY(1,1) NOT NULL,
  4. [Title] [nvarchar](1000) NOT NULL,
  5. [Description] [ntext] NOT NULL,
  6. CONSTRAINT [PK_VacationHomes] PRIMARY KEY CLUSTERED
  7. (
  8. [VacationHomeId] ASC
  9. ))
  10.  
  11. CREATE TABLE [dbo].[VacationHomeImage](
  12. [VacationHomeImageId] [int] IDENTITY(1,1) NOT NULL,
  13. [VacationHomeId] [int] NOT NULL,
  14. [Photo] [image] NOT NULL,
  15. [PhotoFileName] [nvarchar](500) NOT NULL,
  16. CONSTRAINT [PK_VacationHomeImage] PRIMARY KEY CLUSTERED
  17. (
  18. [VacationHomeImageId] ASC
  19. ))
  20. //----------------------------------
  21. using System;
  22. using mshtml;
  23. using VisualWebRipper;
  24. public class Script
  25. { //See help for a definition of WrExportArguments. public static bool ExportData(WrExportArguments args) { try { args.Database.SetSqlAndPrepare("insert into VacationHome (Title, Description) values (@title,@description)"); WrSharedDatabase idCommand = args.Database.SetNewSqlAndPrepare("SELECT @@IDENTITY"); WrSharedDatabase imageCommand = args.Database.SetNewSqlAndPrepare ("insert into VacationHomeImage (VacationHomeId, Photo, PhotoFileName) values (@vacationHomeId,@photo, @photoFileName)"); WrExportTableReader vacationHomeReader = args.ExportData.GetTableReader( "VacationhomesSqlServer"); try { while (vacationHomeReader.Read()) { args.Database.SetParameterTextValue("@title", vacationHomeReader.GetStringValue("title")); args.Database.SetParameterTextValue("@description", vacationHomeReader.GetStringValue("description")); args.Database.ExecuteNonQuery(); int vacationHomeId = (int)((decimal) idCommand.ExecuteScalar()); WrExportTableReader vacationHomeImageReader = vacationHomeReader.GetChildTableReader( "photos"); try { while (vacationHomeImageReader.Read()) { imageCommand.SetParameterIntValue("@vacationHomeId", (int)vacationHomeId); imageCommand.SetParameterBinaryValue("@photo", (byte[]) vacationHomeImageReader.GetFieldValue("photo")); imageCommand.SetParameterTextValue("@photoFileName", vacationHomeImageReader.GetStringValue("photo File Name")); imageCommand.ExecuteNonQuery(); } } finally { vacationHomeImageReader.Close(); } } } finally { vacationHomeReader.Close(); } return true; } catch(Exception exp) { args.WriteDebug("Custom script error: " + exp.Message); return false; } }
  26. }

Report this snippet  

You need to login to post a comment.