将数据从sql表复制到数据集并进行比较 [英] copy data from sql table to dataset and compare

问题描述

我需要开发一个代码，我需要将文件从一个位置复制到另一个位置，如果它的星期日和星期一，以及假日后的第二天...和假期列表在数据库表中。星期天星期一我已经完成了。需要将我从该sql表中的数据放入数据集并与系统日期进行比较的代码。如果是假日后的第二天再复制文件。

i need to develop a code in which i need to copy files from one location to other if its sunday and monday and if its day after the holiday..and holiday list is in database table. for sunday monday i have done already. need the code in which i put data from that sql table to data set and compare with system date . if its day after holiday then copy files.

推荐答案

这是你的解决方案



1）创建一个SQL SERVER数据库中的表。这是创建表脚本

Here is your solution

1) Create one table in SQL SERVER Database. Here is the create table script

CREATE TABLE [dbo].[tblHoliday](
	[HolidayDate] [datetime] NOT NULL,
	[HolidayName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[HolidayType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

2）按以下查询插入日期

2) Insert date as per below query

INSERT INTO tblHoliday 
SELECT '2014-01-01','New Years Day','Restricted Holiday'
UNION
SELECT '2014-01-07','Guru Govind Singh Jayanti','Restricted Holiday'
UNION
SELECT '2014-01-14','Pongal','Restricted Holiday'
UNION
SELECT '2014-01-14','Makar Sankranti','Restricted Holiday'
UNION
SELECT '2014-01-26','Republic Day','Gazetted Holiday'
UNION
SELECT '2014-08-15','Independence Day','Gazetted Holiday'

3）在SQL Server数据库中创建以下程序

3) Create below procedure in SQL server databas

CREATE PROCEDURE spTodaysDateInspection
AS
BEGIN
Declare @TodaysDateName AS NVARCHAR(10)
Declare @Result AS NVARCHAR(50)

SELECT @TodaysDateName = (
  DATENAME(dw, 
  CAST(DATEPART(m, GETDATE()) AS VARCHAR) 
  + '/' 
  + CAST(DATEPART(d, GETDATE()) AS VARCHAR) 
  + '/' 
  + CAST(DATEPART(yy, getdate()) AS VARCHAR))
  )

SET @Result=''

IF @TodaysDateName='Sunday' OR @TodaysDateName= 'Monday' 
  
 BEGIN
    SET @Result='Today is ' + @TodaysDateName
 END

IF EXISTS(SELECT HolidayName FROM tblHoliday WHERE CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) = DateAdd(dd,1,HolidayDate))  
 
BEGIN
 SET @Result='Today is day after holiday' 
END

SELECT @Result

END

4）她e是VB.net代码

4) Here is the VB.net code

Imports System.Data.SqlClient

Public Class Form1

    Private Sub btnCopyfiles_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCopyfiles.Click

        Dim cn As New SqlConnection("Server=testsqlserver;Database=TestDB;Trusted_Connection=True;")
        'Dim cmd As New SqlCommand
        'Dim dr As SqlDataReader
        Dim vResult As String

        cn.Open()
        Dim cmd As SqlCommand = New SqlCommand("spTodaysDateInspection", cn)
        cmd.Connection = cn
        cmd.CommandType = CommandType.StoredProcedure
        'dr = cmd.ExecuteReader()
        'MessageBox.Show(dr.ToString())
        'cn.Close()


        Dim dtTable As New DataTable

        'execute command
        Try

            dtTable.Load(cmd.ExecuteReader())
            cn.Close()
        Catch ex As SqlException
            'do something here
        End Try

        'return string
        vResult = dtTable.Rows(0)(0).ToString()

        If vResult <> "" Then
            My.Computer.FileSystem.CopyFile( _
                "Source\CopyFiles.xml", _
                "Destination\CopyFiles.xml", overwrite:=True)
        End If
    End Sub
End Class

希望这会有所帮助。如果是，则接受并投票解决方案

Hope this helps. If yes then accept and vote the solution

这篇关于将数据从sql表复制到数据集并进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！