C#: Sending data to database (Sqlcompact)

Question

I’m starting now with C# due to the need where I work, I took a project until it was flowing, however, I’m having great difficulty sending the data to the database (a part of the code another person who did).

TotalRecordNum would be the total of tests, so I took the tests by bytes (RecNum, Year, Month, Days, Hr, Min, EventBG, Bg and mid) and then pass via parameter to the database.

I put a if commented inside the is where I determine the value of i it brings me only the data according to the id(TotalRecordNum) and it save normally but would like to save all data.

Thanks in advance, any help, tip, light that gives me.

    public void RECRDecode(byte[] RECR_Buf)
      {
           
           int RecNum, Year, Month, Days, Hr, Min, EventBG, BG, mid;
    
           TotalRecordNum = RECR_Buf[0] * 256 + RECR_Buf[1];
    
           for (TotalRecordNum = 0; TotalRecordNum < 1152; TotalRecordNum++)
           {
               if (RECR_Buf[0 + TotalRecordNum * 8] == 0xFF)
                   break;
               if (RECR_Buf[1 + TotalRecordNum * 8] == 0x00)
                   break;
           }
    
           for (int i = 0; i < TotalRecordNum; i++)
           {             
       
               //if (i == 0)
               //{                
                   
                   RecNum = TotalRecordNum - i;
                   Year = (RECR_Buf[0 + i * 8] & 0xFE) >> 1;
                   Month = (RECR_Buf[0 + i * 8] & 0x01) << 3 | (RECR_Buf[1 + i * 8] & 0xE0) >> 5;
                   Days = RECR_Buf[1 + i * 8] & 0x1F;
                   Hr = (RECR_Buf[2 + i * 8] & 0xF8) >> 3;
                   Min = (RECR_Buf[2 + i * 8] & 0x07) << 3 | (RECR_Buf[3 + i * 8] & 0xE0) >> 5;
                   EventBG = (RECR_Buf[5 + i * 8] & 0x03) << 1 | (RECR_Buf[6 + i * 8] & 0x80) >> 7;
                   BG = (RECR_Buf[6 + i * 8] & 0x03) * 256 + RECR_Buf[7 + i * 8];
                   mid = Mid1_SN;
    
                   dadoGlicemia = BG.ToString("D2");
                   dadoData += Year.ToString("D2") + "/" + Month.ToString("D2") + "/" + Days.ToString("D2");
                   dadoHora += Hr.ToString("D2") + ":" + Min.ToString("D2");
                   AutoSN += MID1.ToString("D5") + mid.ToString("D5");
                   dadoID += RecNum.ToString("D2");
                   dadoEvento += EventBG.ToString("D1");
    
                   Paciente paciente = new Paciente();
                   paciente.NO += dadoID.ToString();
                   paciente.Glicemia += dadoGlicemia.ToString();
    
               //}              
           }
    
       }

Answer 1

As simple as possible!

Start by creating a class to structure the information you want to save:

public class REC
{
    public int RecNum { get; set; }
    public int Year { get; set; }
    public int Month { get; set; }
    public int Days { get; set; }
    public int Hr { get; set; }
    public int Min { get; set; }
    public int EventBG { get; set; }
    public int BG { get; set; }
    public int mid { get; set; }
    public string dadoGlicemia { get; set; }
    public string dadoData { get; set; }
    public string dadoHora { get; set; }
    public string AutoSN { get; set; }
    public string dadoID { get; set; }
    public string dadoEvento { get; set; }
    public Paciente Paciente { get; set; }
}

Change your decoding method to ensure that it returns a collection of data that will be saved:

public List<REC> RECRDecode(byte[] RECR_Buf)
{    
    var dados = new List<REC>();

    TotalRecordNum = RECR_Buf[0] * 256 + RECR_Buf[1];

    for (TotalRecordNum = 0; TotalRecordNum < 1152; TotalRecordNum++)
    {
        if (RECR_Buf[0 + TotalRecordNum * 8] == 0xFF)
            break;
        if (RECR_Buf[1 + TotalRecordNum * 8] == 0x00)
            break;
    }

    for (int i = 0; i < TotalRecordNum; i++)
    {             
        var rec = new REC();

        rec.RecNum = TotalRecordNum - i;
        rec.Year = (RECR_Buf[0 + i * 8] & 0xFE) >> 1;
        rec.Month = (RECR_Buf[0 + i * 8] & 0x01) << 3 | (RECR_Buf[1 + i * 8] & 0xE0) >> 5;
        rec.Days = RECR_Buf[1 + i * 8] & 0x1F;
        rec.Hr = (RECR_Buf[2 + i * 8] & 0xF8) >> 3;
        rec.Min = (RECR_Buf[2 + i * 8] & 0x07) << 3 | (RECR_Buf[3 + i * 8] & 0xE0) >> 5;
        rec.EventBG = (RECR_Buf[5 + i * 8] & 0x03) << 1 | (RECR_Buf[6 + i * 8] & 0x80) >> 7;
        rec.BG = (RECR_Buf[6 + i * 8] & 0x03) * 256 + RECR_Buf[7 + i * 8];
        rec.mid = Mid1_SN;

        rec.dadoGlicemia = BG.ToString("D2");
        rec.dadoData += Year.ToString("D2") + "/" + Month.ToString("D2") + "/" + Days.ToString("D2");
        rec.dadoHora += Hr.ToString("D2") + ":" + Min.ToString("D2");
        rec.AutoSN += MID1.ToString("D5") + mid.ToString("D5");
        rec.dadoID += RecNum.ToString("D2");
        rec.dadoEvento += EventBG.ToString("D1");

        Paciente paciente = new Paciente();
        paciente.NO += dadoID.ToString();
        paciente.Glicemia += dadoGlicemia.ToString();
        rec.Paciente = paciente;

        dados.Add(rec);
    }

    return dados;
}

Create a method that will receive the data collection to insert them into the database.

This method should look like this:

public int Salvar(List<REC> dados)
{
    var linhasSalvas = 0;
    
    using (var connection = new SqliteConnection("BancoDeDados.db"))
    {
        connection.Open();

        foreach (var dado in dados)
        {
            using (var cmd = connection.CreateCommand()){
                cmd.CommandText = @"INSERT INTO TABELA() VALUES()";
                linhasSalvas += cmd.ExecuteNoQuery();
            }
        }
    }

    return linhasSalvas;
}

Important to mention that you may need to install the Sqlite Nuget package if you haven’t already. You also need to enter the database file name and the SQL statement to enter the data into the database.