1

I am working on a Biometric Attendance System. The device generates these User punch logs based on the number of users registered in it. Now the problem is every month a new table is automatically generated to store the device logs whenever I try to sync the hardware logs with its communication SDK.

For example:

For all User punches in July 2017, there is an SQL table called DeviceLogs_7_2017. Similarly, for August, there is DeviceLogs_8_2017.

Now I am making a different front end (other than the software provided with the system) using Visual Studio 2012 and C#, and I need to read these logs from my front end to calculate the attendance using some formula.

Now I don't know exactly how to read them as they are changing every month.

Can this be done using variables in some stored procedure query?

The table description is as below:

DeviceLogId int NULL
DownloadDate    datetime    NULL
DeviceId    int NULL
UserId  nvarchar    50
LogDate datetime    NULL
Direction   nvarchar    100
AttDirection    nvarchar    255
C1  nvarchar    255
C2  nvarchar    255
C3  nvarchar    255
C4  nvarchar    255
C5  nvarchar    255
C6  nvarchar    255
C7  nvarchar    255
WorkCode    nvarchar    100
UpdateFlag  int NULL

Out of this table I want the data in the UserID, LogDate and WorkCode columns. Please help me to get my head working in the direction of some idea to solve this problem.

Obsidian Age
  • 41,205
  • 10
  • 48
  • 71
Pawan Bisht
  • 7
  • 6

1 Answers1

0

First see this to open a connection to DB How to directly execute SQL query in C#? Have example batch file

The following is the modified code from the above link (change the connection string). You can play with this code. Instead of using Now() you may use other date. For example if you are always going to pull data for last month, you can do DateTime dt = DateTime.Now().AddMonths(-1):

string queryString = "SELECT UserID, LogDate, WorkCode from DeviceLogs_" + Month(DateTime.Now()) + "_" + Year(DateTime.Now());
string connectionString = "Server=.\PDATA_SQLEXPRESS;Database=;User Id=sa;Password=2BeChanged!;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    try
    {
        while (reader.Read())
        {
            Console.WriteLine(String.Format("{0}, {1}, {2}",
            reader["UserId"],reader["LogDate"]), reader["WorkCode"]));// etc
        }
    }
    finally
    {
        // Always call Close when done reading.
        reader.Close();
    }
}

The very first line of the above code is what you need to modify to create a union with generated table name using from and to dates selected by the user. You need to do a refresher on SQL language and how to use join and unions. Or you can fire multiple queries in a loop (as suggested by a comment below) each hitting one table, collect the returned data in C# code and display. It appears some very wise guy designed the database. A sane person would have not created different tables for different months, instead one table with was suffice because the table has LogDate column

Allen King
  • 2,372
  • 4
  • 34
  • 52
  • Thanks,...............I have designed my front end like this,there will be two datepicker fields From and To,from which the user will select date range.Now using this date range we need to extract data out of these tables.What if the user selects a date range of 3 months.How would i be able to select from 3 tables?What if he selects only a week duration? – Pawan Bisht Aug 03 '17 at 05:38
  • @PawanBisht at that point, dynamically generate a union of the relevant tables before applying the where clause. – AndrewP Aug 03 '17 at 05:42
  • @AndrewP..Hi,Can you give some examples or link where i can learn to do that? – Pawan Bisht Aug 03 '17 at 05:46
  • You can loop through each month and store the query result in a data table that you can later display to the user. – pso Aug 03 '17 at 06:07