copy data from sql table to dataset and compare

Question

1.00/5 (1 vote)

See more:

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.

Posted 30-Apr-14 3:19am

Member 10785936

Add a Solution

Comments

CHill60 30-Apr-14 9:23am

A lot about what you need but nothing about what you tried and where the problem is - post details of what you have tried so far and where you are stuck

[no name] 30-Apr-14 9:25am

If you need this code then you should probably start writing it.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Answer 1 · 2014-05-01T03:10:00

Here is your solution

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

SQL

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) Insert date as per below query

SQL

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) Create below procedure in SQL server databas

SQL

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) Here is the VB.net code

VB.NET

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