Click here to Skip to main content
15,907,687 members
Home / Discussions / C#
   

C#

 
GeneralRe: Viewing the data enterd in the SQL Server Database for a specifed Time frame. Pin
Shameel6-Feb-14 23:43
professionalShameel6-Feb-14 23:43 
GeneralRe: Viewing the data enterd in the SQL Server Database for a specifed Time frame. Pin
OriginalGriff6-Feb-14 23:55
mveOriginalGriff6-Feb-14 23:55 
GeneralRe: Viewing the data enterd in the SQL Server Database for a specifed Time frame. Pin
Shameel7-Feb-14 0:07
professionalShameel7-Feb-14 0:07 
GeneralRe: Viewing the data enterd in the SQL Server Database for a specifed Time frame. Pin
Mohan Subramani7-Feb-14 2:32
Mohan Subramani7-Feb-14 2:32 
GeneralRe: Viewing the data enterd in the SQL Server Database for a specifed Time frame. Pin
OriginalGriff7-Feb-14 2:42
mveOriginalGriff7-Feb-14 2:42 
GeneralRe: Viewing the data enterd in the SQL Server Database for a specifed Time frame. Pin
Mohan Subramani7-Feb-14 2:58
Mohan Subramani7-Feb-14 2:58 
GeneralRe: Viewing the data enterd in the SQL Server Database for a specifed Time frame. Pin
OriginalGriff7-Feb-14 3:23
mveOriginalGriff7-Feb-14 3:23 
AnswerRe: Viewing the data enterd in the SQL Server Database for a specifed Time frame. Pin
KeyStrokes7-Feb-14 2:21
KeyStrokes7-Feb-14 2:21 
In a nut shell use a SP to perform this for you. Below is an example script that will create a table, Populate with so test data and a basic SP. Also note that SQL plays funny with the dates in regards WHERE and BETWEEN clauses.

Your BCP may be different from mine but it should be about that folder structure somewhere depending on the version of SQL that you have.

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


Below is an example C# application to consume this along with an example to use BCP for the Export that uses the same SP.

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);
        }

       
    }
}



The are always better way to do this but I hope that this gives a few pointers. to get better separation of concerns.
PS. You should try to avoid SELECT * as there is a over head and it leave the code open problems in then future.

BCP Ref:

BCP
http://technet.microsoft.com/en-us/library/ms162802.aspx
http://technet.microsoft.com/en-us/library/ms191516.aspx
QuestionHow to run Import-PSSessions (powershell command) from .C# program Pin
Eviatar Gerzi6-Feb-14 7:51
Eviatar Gerzi6-Feb-14 7:51 
AnswerRe: How to run Import-PSSessions (powershell command) from .C# program Pin
Eviatar Gerzi11-Feb-14 8:44
Eviatar Gerzi11-Feb-14 8:44 
QuestionRegEx problem #2 [solved] Pin
Marco Bertschi6-Feb-14 2:32
protectorMarco Bertschi6-Feb-14 2:32 
AnswerRe: RegEx problem #2 Pin
OriginalGriff6-Feb-14 2:54
mveOriginalGriff6-Feb-14 2:54 
GeneralRe: RegEx problem #2 Pin
Marco Bertschi6-Feb-14 3:00
protectorMarco Bertschi6-Feb-14 3:00 
GeneralRe: RegEx problem #2 Pin
OriginalGriff6-Feb-14 3:11
mveOriginalGriff6-Feb-14 3:11 
GeneralRe: RegEx problem #2 Pin
Marco Bertschi6-Feb-14 3:13
protectorMarco Bertschi6-Feb-14 3:13 
GeneralRe: RegEx problem #2 Pin
Ravi Bhavnani6-Feb-14 6:54
professionalRavi Bhavnani6-Feb-14 6:54 
GeneralRe: RegEx problem #2 Pin
Marco Bertschi6-Feb-14 8:59
protectorMarco Bertschi6-Feb-14 8:59 
AnswerRe: RegEx problem #2 Pin
Kornfeld Eliyahu Peter6-Feb-14 3:19
professionalKornfeld Eliyahu Peter6-Feb-14 3:19 
GeneralRe: RegEx problem #2 Pin
Marco Bertschi6-Feb-14 3:28
protectorMarco Bertschi6-Feb-14 3:28 
AnswerMessage Closed Pin
6-Feb-14 3:26
professionalPeter Leow6-Feb-14 3:26 
GeneralRe: RegEx problem #2 Pin
Marco Bertschi6-Feb-14 3:28
protectorMarco Bertschi6-Feb-14 3:28 
AnswerRe: RegEx problem #2 [solved] Pin
Nicholas Marty6-Feb-14 4:01
professionalNicholas Marty6-Feb-14 4:01 
GeneralRe: RegEx problem #2 [solved] Pin
Marco Bertschi6-Feb-14 4:22
protectorMarco Bertschi6-Feb-14 4:22 
AnswerRe: RegEx problem #2 [solved] Pin
BillWoodruff6-Feb-14 4:16
professionalBillWoodruff6-Feb-14 4:16 
QuestionRequested registry access is not allowed. Pin
Jassim Rahma5-Feb-14 23:02
Jassim Rahma5-Feb-14 23:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.