C#

Member 4211040 wrote:

I agree with you, it sound like something happens to the buffer that it becomes no longer valid but I am not sure what happens exactly!.

SELECT * FROM MyTable WHERE MyDateColumn BETWEEN '2014-01-01' AND '2014-01-31'

OriginalGriff wrote:

SELECT * WHERE MyDateColumn BETWEEN '2014-01-01' AND '2014-01-31'

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT iD, description FROM myTable WHERE enterDate BETWEEN @FD AND @TD", con))
        {
        cmd.Parameters.AddWithValue("@FD", fromDatePicker.Value);
        cmd.Parameters.AddWithValue("@TD", toDatePicker.Value);
        using (SqlDataReader reader = cmd.ExecuteReader())
            {
            while (reader.Read())
                {
                int id = (int) reader["iD"];
                string desc = (string) reader["description"];
                Console.WriteLine("ID: {0}\n    {1}", iD, desc);
                }
            }
        }
    }

CREATE TABLE DBO.MyData
(
	[ID] INT NOT NULL IDENTITY(1,1),				--ALL TABLES SHOULD HAVE A ID EITHER SURRAGATE OR NATURAL.
	[User] INT NOT NULL,							--ASSUME FK CONSTRINT WITH YOU USER TABLE
	[Data] VARCHAR(256) NULL,					
	[DateTimeStamp] DATETIME NOT NULL DEFAULT(GETDATE()),	--TIME STAMP FOR THE ENTRY
		
)
GO

ALTER TABLE MyData ADD PRIMARY KEY (ID)
GO

/* Add some test data */
INSERT INTO DBO.MyData([User], [Data]) VALUES (1,'MESSAGE 1 FROM USER 1')
INSERT INTO DBO.MyData([User], [Data]) VALUES (1,'MESSAGE 2 FROM USER 1')
INSERT INTO DBO.MyData([User], [Data]) VALUES (1,'MESSAGE 3 FROM USER 1')
INSERT INTO DBO.MyData([User], [Data]) VALUES (2,'MESSAGE 1 FROM USER 2')
INSERT INTO DBO.MyData([User], [Data]) VALUES (2,'MESSAGE 2 FROM USER 2')
INSERT INTO DBO.MyData([User], [Data]) VALUES (3,'MESSAGE 1 FROM USER 3')
INSERT INTO DBO.MyData([User], [Data]) VALUES (1,'MESSAGE 4 FROM USER 1')
GO


CREATE PROCEDURE GetData 
(
	@User INT = NULL,
	@FROM DATETIME = NULL,
	@TO	DATETIME = NULL
)
AS
	SELECT	
		D.[ID],
		D.[DATA],
		D.[DateTimeStamp]									
	FROM	
		DBO.MyData	AS D
	WHERE
		D.[User] = ISNULL(@User, D.[User])
	AND
		D.[DateTimeStamp] >= ISNULL(@FROM, D.[User])
	AND
		D.[DateTimeStamp] <= ISNULL(@TO, D.[User])
	
	ORDER BY D.[DateTimeStamp] DESC
GO

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace ConsoleApplication9
{
    class Program
    {
        static void Main(string[] args)
        {
            //For the stored Procedure
            int _user = 1;
            DateTime _from = DateTime.Now.Add(new TimeSpan(-1, 0, 0, 0));
            DateTime _to = DateTime.Now.Add(new TimeSpan(1, 0, 0, 0));

            //Enviroment variables (DB server etc)
            string dbServer = "localhost";                                         //TODO.  Put your sever name here
            string dbDatabase = "";                                            //TODO:  Put you database name here
            string dbStoredProcedure = "[dbo].[GetData]";
            string outPutFile = @"C:\Temp\MyData.txt";
            string bcpPath = @"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\";

            //Execute the SP to get all data required.
            using (var conn = new SqlConnection(
                string.Format("Server={0};Database={1};Trusted_Connection=True;",
                dbServer,
                dbDatabase))
                )
            {
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = dbStoredProcedure;
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("User", _user));
                    command.Parameters.Add(new SqlParameter("From", _from));
                    command.Parameters.Add(new SqlParameter("To", _to));

                    conn.Open();
                    using (var r = command.ExecuteReader())
                    {
                        while (r.Read())
                        {
                            Console.WriteLine(r[0].ToString());
                            Console.WriteLine(r[1].ToString());
                            Console.WriteLine(r[2].ToString());
                        }
                    }

                    conn.Close();
                }
            }

            //Use BCP to do the extraction
            //Arguments for BCP
             string bcpArgs  = string.Format("[{0}].{1} {2}, '{3}','{4}'",
                 dbDatabase,
                dbStoredProcedure,
                _user.ToString(), 
                _from.ToString("yyyy-MM-dd"), 
                _to.ToString("yyyy-MM-dd"));


            //Run BCP
             System.Diagnostics.Process.Start(
                 string.Format("\"{0}BCP.exe\"",
                    bcpPath),
                 string.Format("\"{0}\" queryout {1} -c -t, -T -S {2}",
                     bcpArgs,
                     outPutFile,
                     dbServer));

             System.Diagnostics.Process.Start(
                 "Notepad.exe", outPutFile);
        }

       
    }
}