perl 脚本解析sqlserver导出的sql语句为C#所用

最新推荐文章于 2021-07-02 10:02:13 发布

青水

最新推荐文章于 2021-07-02 10:02:13 发布

阅读量837

点赞数

分类专栏： perl C#

本文链接：https://blog.csdn.net/qingshuiaishui/article/details/22494543

版权

perl 同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

10 篇文章 0 订阅

订阅专栏

1、处理sqlserver导出的查询。

例：导出的sql 为:

SELECT [LogEntryId]
,[InstanceName]
,[ReportID]
,[UserName]
,[ExecutionId]
,[RequestType]
,[Format]
,[Parameters]
,[ReportAction]
,[TimeStart]
,[TimeEnd]
,[TimeDataRetrieval]
,[TimeProcessing]
,[TimeRendering]
,[Source]
,[Status]
,[ByteCount]
,[RowCount]
,[AdditionalInfo]
FROM [ReportServer].[dbo].[ExecutionLogStorage]

期望处理后为:

SELECT [LogEntryId],[InstanceName],[ReportID],[UserName],[ExecutionId],[RequestType],[Format],[Parameters],

[ReportAction],[TimeStart],[TimeEnd],[TimeDataRetrieval],[TimeProcessing],[TimeRendering],[Source],[Status],

[ByteCount],[RowCount],[AdditionalInfo] FROM [ReportServer].[dbo].[ExecutionLogStorage].

2、处理sqlserver导出的插入。

例：导出的sql 为:

INSERT INTO [ReportServer].[dbo].[ExecutionLogStorage]
([InstanceName]
,[ReportID]
,[UserName]
,[ExecutionId]
,[RequestType]
,[Format]
,[Parameters]
,[ReportAction]
,[TimeStart]
,[TimeEnd]
,[TimeDataRetrieval]
,[TimeProcessing]
,[TimeRendering]
,[Source]
,[Status]
,[ByteCount]
,[RowCount]
,[AdditionalInfo])
VALUES
(<InstanceName, nvarchar(38),>
,<ReportID, uniqueidentifier,>
,<UserName, nvarchar(260),>
,<ExecutionId, nvarchar(64),>
,<RequestType, tinyint,>
,<Format, nvarchar(26),>
,<Parameters, ntext,>
,<ReportAction, tinyint,>
,<TimeStart, datetime,>
,<TimeEnd, datetime,>
,<TimeDataRetrieval, int,>
,<TimeProcessing, int,>
,<TimeRendering, int,>
,<Source, tinyint,>
,<Status, nvarchar(40),>
,<ByteCount, bigint,>
,<RowCount, bigint,>
,<AdditionalInfo, xml,>)
GO

期望处理后为:

INSERT INTO [ReportServer].[dbo].[ExecutionLogStorage] ([InstanceName],[ReportID],[UserName],[ExecutionId],[RequestType],

[Format],[Parameters],[ReportAction],[TimeStart],[TimeEnd],[TimeDataRetrieval],[TimeProcessing],[TimeRendering],[Source],

[Status],[ByteCount],[RowCount],[AdditionalInfo]) VALUES (@InstanceName,@ReportID,@UserName,@ExecutionId,

@RequestType,@Format,@Parameters,@ReportAction,@TimeStart,@TimeEnd,@TimeDataRetrieval,@TimeProcessing,

@TimeRendering,@Source,@Status,@ByteCount,@RowCount,@AdditionalInfo)

List<DbParameter> dbParamList = new List<DbParameter>();
dbParamList.Add(new SqlParameter("@InstanceName", ));
dbParamList.Add(new SqlParameter("@ReportID", ));
dbParamList.Add(new SqlParameter("@UserName", ));
dbParamList.Add(new SqlParameter("@ExecutionId", ));
dbParamList.Add(new SqlParameter("@RequestType", ));
dbParamList.Add(new SqlParameter("@Format", ));
dbParamList.Add(new SqlParameter("@Parameters", ));
dbParamList.Add(new SqlParameter("@ReportAction", ));
dbParamList.Add(new SqlParameter("@TimeStart", ));
dbParamList.Add(new SqlParameter("@TimeEnd", ));
dbParamList.Add(new SqlParameter("@TimeDataRetrieval", ));
dbParamList.Add(new SqlParameter("@TimeProcessing", ));
dbParamList.Add(new SqlParameter("@TimeRendering", ));
dbParamList.Add(new SqlParameter("@Source", ));
dbParamList.Add(new SqlParameter("@Status", ));
dbParamList.Add(new SqlParameter("@ByteCount", ));
dbParamList.Add(new SqlParameter("@RowCount", ));
dbParamList.Add(new SqlParameter("@AdditionalInfo", ));

3、处理sqlserver导出的更新语句。

例：导出的sql 为:

UPDATE [ReportServer].[dbo].[ExecutionLogStorage]
SET [InstanceName] = <InstanceName, nvarchar(38),>
,[ReportID] = <ReportID, uniqueidentifier,>
,[UserName] = <UserName, nvarchar(260),>
,[ExecutionId] = <ExecutionId, nvarchar(64),>
,[RequestType] = <RequestType, tinyint,>
,[Format] = <Format, nvarchar(26),>
,[Parameters] = <Parameters, ntext,>
,[ReportAction] = <ReportAction, tinyint,>
,[TimeStart] = <TimeStart, datetime,>
,[TimeEnd] = <TimeEnd, datetime,>
,[TimeDataRetrieval] = <TimeDataRetrieval, int,>
,[TimeProcessing] = <TimeProcessing, int,>
,[TimeRendering] = <TimeRendering, int,>
,[Source] = <Source, tinyint,>
,[Status] = <Status, nvarchar(40),>
,[ByteCount] = <ByteCount, bigint,>
,[RowCount] = <RowCount, bigint,>
,[AdditionalInfo] = <AdditionalInfo, xml,>
WHERE <搜索条件,,>
GO

期望处理后为:

UPDATE [ReportServer].[dbo].[ExecutionLogStorage] SET [InstanceName]= @InstanceName,[ReportID]= @ReportID,

[UserName]= @UserName,[ExecutionId]= @ExecutionId,[RequestType]= @RequestType,[Format]= @Format,

[Parameters]= @Parameters,[ReportAction]= @ReportAction,[TimeStart]= @TimeStart,[TimeEnd]= @TimeEnd,

[TimeDataRetrieval]= @TimeDataRetrieval,[TimeProcessing]= @TimeProcessing,[TimeRendering]= @TimeRendering,

[Source]= @Source,[Status]= @Status,[ByteCount]= @ByteCount,[RowCount]= @RowCount,[AdditionalInfo]= @AdditionalInfo WHERE

List<DbParameter> dbParamList = new List<DbParameter>();
dbParamList.Add(new SqlParameter("@InstanceName", ));
dbParamList.Add(new SqlParameter("@ReportID", ));
dbParamList.Add(new SqlParameter("@UserName", ));
dbParamList.Add(new SqlParameter("@ExecutionId", ));
dbParamList.Add(new SqlParameter("@RequestType", ));
dbParamList.Add(new SqlParameter("@Format", ));
dbParamList.Add(new SqlParameter("@Parameters", ));
dbParamList.Add(new SqlParameter("@ReportAction", ));
dbParamList.Add(new SqlParameter("@TimeStart", ));
dbParamList.Add(new SqlParameter("@TimeEnd", ));
dbParamList.Add(new SqlParameter("@TimeDataRetrieval", ));
dbParamList.Add(new SqlParameter("@TimeProcessing", ));
dbParamList.Add(new SqlParameter("@TimeRendering", ));
dbParamList.Add(new SqlParameter("@Source", ));
dbParamList.Add(new SqlParameter("@Status", ));
dbParamList.Add(new SqlParameter("@ByteCount", ));
dbParamList.Add(new SqlParameter("@RowCount", ));
dbParamList.Add(new SqlParameter("@AdditionalInfo", ));

基于以上需求，编写脚本dealsql.pl。实现为（windows 平台 strawberry perl 5.18）：

#!perl
use strict;
use warnings;

my $updateFlag = 0;

my @fileds = ();
my $sql = '';
while(<>)
{
tr/\x0D\x0A//d;
tr/\x20//s;

$updateFlag = 1 if /UPDATE/;

if(/(<.*?>)/)
{
my $tmp = $1;
$tmp =~ /<(.*?),/;
my $key = '@'.$1;

if($updateFlag)
{
unless($key =~ /搜索条件/)
{
s/<(.*),>/$key/;

push @fileds,$key;
}
else
{
s/<(.*),>//;
}
}
else
{
s/<(.*),>/$key/;

push @fileds,$key;
}
}
s/\s(?=')//;#去'之前的空白
s/\s(?=,)//;#去,之前的空白
s/\s(?==)//;#去=之前的空白

next if length($_) == 0;
next if /^GO$/;

$sql = $sql.$_;
}

################################################################
#结果处理
print "$sql\n";

print "\n\n";
if($updateFlag or $sql =~ /INSERT INTO/)
{
print "List<DbParameter> dbParamList = new List<DbParameter>();\n";
foreach(@fileds)
{
print "dbParamList.Add(new SqlParameter(\"$_\", ));\n";
}
}