我想利用C#调用数据库脚本文件！
我将数据库脚本文件放入到数据库中，如何利用sqlcommand进行执行？
GO
/****** 对象: Table [dbo].[DayList] 脚本日期: 11/22/2010 11:47:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DayList](
[Datecode] [varchar](10) NOT NULL,
[ItemID] [varchar](32) NOT NULL,
[ItemUrl] [varchar](200) NOT NULL,
[PV] [int] NOT NULL,
[UV] [int] NOT NULL,
[ItemTitle] [varchar](200) NOT NULL,
[PicUrl] [varchar](200) NOT NULL,
CONSTRAINT [PK_DAYLIST] PRIMARY KEY CLUSTERED
(
[Datecode] ASC,
[ItemUrl] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[MonthList] 脚本日期: 11/22/2010 11:47:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MonthList](
[Datecode] [varchar](10) NOT NULL,
[ItemID] [varchar](32) NOT NULL,
[ItemUrl] [varchar](200) NOT NULL,
[PV] [int] NOT NULL,
[UV] [int] NOT NULL,
[ItemTitle] [varchar](200) NOT NULL,
[PicUrl] [varchar](200) NOT NULL,
CONSTRAINT [PK_MonthList_1] PRIMARY KEY CLUSTERED
(
[Datecode] ASC,
[ItemUrl] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[intoList] 脚本日期: 11/22/2010 11:47:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[intoList](
[UserName] [varchar](32) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[PageList] 脚本日期: 11/22/2010 11:47:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PageList](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](32) NOT NULL,
[CurrentPage] [varchar](200) NOT NULL,
[OS] [varchar](50) NOT NULL,
[Browser] [varchar](50) NOT NULL,
[sVisitors] [varchar](50) NOT NULL,
[UserIP] [varchar](50) NOT NULL,
[sLanguage] [varchar](50) NOT NULL,
[RefPage] [varchar](200) NOT NULL,
[CreateDate] [datetime] NOT NULL,
CONSTRAINT [PK_PageList] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[YearList] 脚本日期: 11/22/2010 11:48:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[YearList](
[Datecode] [varchar](10) NOT NULL,
[ItemID] [varchar](32) NOT NULL,
[ItemUrl] [varchar](200) NOT NULL,
[PV] [int] NOT NULL,
[UV] [int] NOT NULL,
[ItemTitle] [varchar](200) NOT NULL,
[PicUrl] [varchar](200) NOT NULL,
CONSTRAINT [PK_YearList] PRIMARY KEY CLUSTERED
(
[Datecode] ASC,
[ItemUrl] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[StopList] 脚本日期: 11/22/2010 11:48:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[StopList](
[StopID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](32) NULL,
[CreateDate] [datetime] NULL,
[StopPage] [varchar](200) NOT NULL,
[ItemUrl] [varchar](200) NULL,
[PicUrl] [varchar](200) NULL,
[ProdID] [varchar](200) NULL,
[StopTime] [int] NULL,
CONSTRAINT [PK_StopList] PRIMARY KEY CLUSTERED
(
[StopID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[Product] 脚本日期: 11/22/2010 11:48:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Product](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](32) NOT NULL,
[CurrentPage] [nvarchar](200) NULL,
[ItemUrl] [nvarchar](200) NULL,
[PicUrl] [nvarchar](200) NULL,
[ProdID] [nvarchar](200) NULL,
[ProcMoney] [varchar](20) NULL,
[CreateDate] [datetime] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[pro] 脚本日期: 11/22/2010 11:47:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[pro](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](32) NOT NULL,
[CurrentPage] [varchar](200) NOT NULL,
[ItemUrl] [varchar](200) NULL,
[PicUrl] [varchar](200) NULL,
[ProdID] [varchar](200) NULL,
[ProcMoney] [varchar](20) NULL,
[CreateDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[ConfigList] 脚本日期: 11/22/2010 11:47:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ConfigList](
[sqlscript] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** 对象: Table [dbo].[Allvisit] 脚本日期: 11/22/2010 11:47:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Allvisit](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](32) NOT NULL,
[CurrentPage] [varchar](200) NOT NULL,
[OS] [varchar](50) NOT NULL,
[Browser] [varchar](50) NOT NULL,
[sVisitors] [varchar](50) NOT NULL,
[UserIP] [varchar](50) NOT NULL,
[sLanguage] [varchar](50) NOT NULL,
[RefPage] [varchar](200) NOT NULL,
[CreateDate] [datetime] NOT NULL,
CONSTRAINT [PK_ALLVISIT] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Trigger [Tri_生成月列表] 脚本日期: 11/22/2010 11:48:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create TRIGGER [dbo].[Tri_生成月列表] on [dbo].[Allvisit]
for INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @CreateDate datetime
,@CurrentPage varchar(200)
,@datecode varchar(10)
,@pv int
,@uv int
select @CreateDate=CreateDate, @CurrentPage=CurrentPage from inserted
set @datecode=convert(varchar(7),@CreateDate,120)
select @pv=count(0) from Allvisit
where CurrentPage=@CurrentPage and CreateDate=@CreateDate
select @uv=count(0) from Allvisit
where CurrentPage=@CurrentPage
group by UserName
if not exists(select 0 from MonthList where [ItemUrl]=@CurrentPage and [Datecode]=@datecode)
begin
INSERT INTO MonthList
([Datecode]
,[ItemID]
,[ItemUrl]
,[PV]
,[UV]
,[ItemTitle]
,[PicUrl])
VALUES
(@datecode
,''
,@CurrentPage
,@pv
,@uv
,''
,'')
end
else
begin
update MonthList
set [PV]=@pv
,[UV]=@uv
where [ItemUrl]=@CurrentPage and [Datecode]=@datecode
end
END
GO
/****** 对象: Trigger [Tri_生成年列表] 脚本日期: 11/22/2010 11:48:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[Tri_生成年列表] on [dbo].[Allvisit]
for INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @CreateDate datetime
,@CurrentPage varchar(200)
,@datecode varchar(10)
,@pv int
,@uv int
select @CreateDate=CreateDate, @CurrentPage=CurrentPage from inserted
set @datecode=convert(char(4),@CreateDate,120)
select @pv=count(0) from Allvisit
where CurrentPage=@CurrentPage and CreateDate=@CreateDate
select @uv=count(0) from Allvisit
where CurrentPage=@CurrentPage
group by UserName
if not exists(select 0 from YearList where [ItemUrl]=@CurrentPage and [Datecode]=@datecode)
begin
INSERT INTO YearList
([Datecode]
,[ItemID]
,[ItemUrl]
,[PV]
,[UV]
,[ItemTitle]
,[PicUrl])
VALUES
(@datecode
,''
,@CurrentPage
,@pv
,@uv
,''
,'')
end
else
begin
update YearList
set [PV]=@pv
,[UV]=@uv
where [ItemUrl]=@CurrentPage and [Datecode]=@datecode
end
END
GO
/****** 对象: Trigger [复制数据到PageList] 脚本日期: 11/22/2010 11:48:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE trigger [dbo].[复制数据到PageList] on [dbo].[Allvisit]
after insert
as
declare
@UserName varchar(32),
@CurrentPage varchar(200),
@OS varchar(50),
@Browser varchar(50),
@sVisitors varchar(50),
@UserIP varchar(50),
@sLanguage varchar(50),
@RefPage varchar(200),
@Createdate datetime
select @UserName=UserName,@CurrentPage=CurrentPage,@OS=OS,@Browser=Browser,@sVisitors=sVisitors,@UserIP=UserIP,@sLanguage=sLanguage,@RefPage=RefPage,@Createdate=Createdate from inserted
insert into PageList values(@UserName,@CurrentPage,@OS,@Browser,@sVisitors,@UserIP,@sLanguage,@RefPage,@Createdate)
GO
/****** 对象: Trigger [my_trig] 脚本日期: 11/22/2010 11:48:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE trigger [dbo].[my_trig] on [dbo].[Allvisit] for insert
as
declare @CurrentPage varchar(200),
@UserName varchar(32),
@CreateDate datetime
select @CreateDate=CreateDate, @CurrentPage=CurrentPage,@UserName = UserName from inserted
if exists(select 1 from inserted where CurrentPage like 'http://www.100to.com/item.htm?id=%' or CurrentPage like 'http://item.taobao.com/item.htm?id=%' )--这里条件自己加,不过你的条件貌似不正确
begin
insert into pro values (@UserName,@CurrentPage,'','','','',@CreateDate)
end
GO
/****** 对象: Trigger [Tri_生成日列表] 脚本日期: 11/22/2010 11:48:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[Tri_生成日列表] on [dbo].[Allvisit]
for INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @CreateDate datetime
,@CurrentPage varchar(200)
,@datecode varchar(10)
,@pv int
,@uv int
select @CreateDate=CreateDate, @CurrentPage=CurrentPage from inserted
set @datecode=convert(char(10),@CreateDate,120)
select @pv=count(0) from Allvisit
where CurrentPage=@CurrentPage and CreateDate=@CreateDate
select @uv=count(0) from Allvisit
where CurrentPage=@CurrentPage
group by UserName
if not exists(select 0 from [DayList] where [ItemUrl]=@CurrentPage and [Datecode]=@datecode)
begin
INSERT INTO [DayList]
([Datecode]
,[ItemID]
,[ItemUrl]
,[PV]
,[UV]
,[ItemTitle]
,[PicUrl])
VALUES
(@datecode
,''
,@CurrentPage
,@pv
,@uv
,''
,'')
end
else
begin
update [DayList]
set [PV]=@pv
,[UV]=@uv
where [ItemUrl]=@CurrentPage and [Datecode]=@datecode
end
END
GO
添加评论
关注(0)
相关推荐
总之，通过C#调用bat文件和SQL脚本，我们可以实现对Oracle数据库内存大小的自动化调整。这种方法灵活且可扩展，适用于各种系统管理任务。了解如何正确地进行这样的操作对于提升数据库性能和维护系统的稳定性至关重要...
3. **PowerShell**：对于更高级的自动化需求，PowerShell可以用来调用SQL Server的Smo库或其他API来执行SQL脚本。通过创建一个脚本，遍历指定目录下的所有SQL文件并执行它们，可以实现批量执行。 4. **编程语言集成...
总之，"c# 开源运行根据sql脚本生成sql2005数据库"这个项目涵盖了C#与SQL Server的交互、SQL脚本执行、文件操作以及数据加密等多个方面，是学习和实践C#数据库开发的一个良好实例。通过理解和实现这样的工具，开发者...
这里的“读取外部sql文件”意味着程序可以从项目之外的文件系统位置加载SQL脚本，而“执行内部sql命令”则意味着这个类能解析并运行SQL文件中的各种SQL命令。 基于提供的标签，我们可以推测这个示例可能包含以下...
标题提到的"C#使用server执行SQL脚本所需DLL"主要指的是C#程序中调用SQL Server的管理对象（SMO，SQL Server Management Objects）来执行SQL脚本。下面将详细介绍这一技术及其背后的原理。 SMO是Microsoft提供的一...
C#通过调用SQL Server的存储过程或直接执行SQL语句，实现对房间状态的更新和查询。 3. **预订管理**：用户可以查询空房、预订房间并取消预订。C#应用程序将处理这些操作，同时更新SQL Server中的预订表，以保持实时...
执行批处理时，我们不需要调用多次ExecuteNonQuery()方法，而是只需调用一次，即可执行所有SQL语句： ```csharp int rowsAffected = command.ExecuteNonQuery(); Console.WriteLine("Rows affected: " + rows...
总之，这个压缩包提供了从C#应用程序调用SQL Server 2005存储过程的全面实例，涵盖了数据库连接、参数传递、结果处理等多个关键步骤，对于学习和掌握这一技能具有很高的参考价值。开发者可以通过这些示例，深入理解...
在.NET框架中，有时我们需要执行SQL...总的来说，通过C#调用CMD执行osql是一种简单的方法，尤其适用于自动化脚本执行，但它可能存在安全性问题。在实际项目中，应根据具体需求和安全考虑选择合适的方法来执行SQL脚本。
Global.asax文件是ASP.NET应用的全局应用程序文件，其中可以定义一些全局事件处理程序，如Application_Start和Application_BeginRequest，用于在应用程序启动或每个请求开始时执行代码，实现全局级别的SQL注入防护。...
该组件允许 SQL Server 脚本直接访问 SOAP Web Services，从而实现跨平台的数据交换和服务调用。 **安装步骤：** 1. **下载 Soap Toolkit 3.0 安装包**：可以从 Microsoft 官方网站下载最新版本的 Soap Toolkit ...
总的来说，通过C#调用SQL Server存储过程，可以有效地结合数据库的处理能力和.NET应用程序的逻辑控制，实现高效且可维护的业务逻辑。不过，应根据项目需求和团队规范来决定是否使用存储过程，同时考虑到其性能、安全...
7. **安全性**：防止SQL注入、跨站脚本攻击(XSS)等网络安全问题，C#提供内置的安全措施，如参数化查询、验证控件等。 8. **性能优化**：缓存技术可以提高系统性能，如使用Output Cache缓存静态页面，减少数据库查询...
总结，C#调用脚本实现打印及保存Excel涉及到的关键技术包括ADO.NET进行数据库交互，使用Microsoft.Office.Interop.Excel操作Excel文件，以及利用System.Drawing.Printing进行打印。在实际开发中，需要根据项目需求和...
例如，当员工信息发生变化时，C#应用程序会调用SQL的存储过程来更新数据库中的相应记录。 此外，压缩包内的"人事工资管理系统.sln"文件是Visual Studio的解决方案文件，包含了整个项目的配置信息和依赖关系。开发者...
通过创建一个使用C#或VB.NET编写的CLR（公共语言运行时）存储过程，可以更灵活地调用Web Service。步骤包括： - 创建一个.NET项目，编写调用Web Service的代码。 - 配置项目以生成SQL Server可使用的DLL。 - 在...
该标题指出本文将介绍如何在C#程序中调用SQL Server中的存储过程，并且这个存储过程会接受参数。存储过程是一种预先编译好的SQL脚本，存储在数据库中，可以被应用程序多次调用执行，以提高性能和代码复用性。 ### ...
这是一个基于C#编程语言和SQL Server数据库系统的超市管理系统源代码项目。该项目包含了完整的数据库脚本，确保了数据存储和管理的高效性。以下是对这个系统的一些关键知识点的详细阐述： 1. C#语言：C#是微软开发...
- 如何编写SQL脚本来创建和初始化数据库。 - 如何在C#代码中调用这些脚本，确保在应用程序启动时自动执行。 - 如何配置项目设置，将数据库文件打包进安装程序。 - 如何处理权限问题，确保应用程序能够正确连接和操作...