Return database values to array?

Question

I'm reading some values incoming from the arduino in my c# visual studio (VS) program. Whenever I send a signal via IR to my arduino, it generates a code to my VS's textbox. I'm saving it in a variable called lastvalue.

private void SerialPort1_DataReceived(object sender, SerialDataReceivedEventArgs e)
    {
        lastValue = serialPort1.ReadLine(); //reads serial     
    }

That code is related to a database table column, what I mean is that if I send code 1 -> a, 2 -> b, ...

How can I press a button in my VS program and start a cycle that based on the codes I'm receiving from arduino, fills an array. Imagine I send codes 1, 2, 5, 10 to arduino, I want an array like this [a, b, e, j].

private void btnLoadDB_Click(object sender, EventArgs e)
    {
        string constring = @"Data Source=.\SQLEXPRESS...";
        SqlConnection conDataBase = new SqlConnection(constring);
        SqlCommand cmdDataBase = new SqlCommand("select LETTER from TABLE where CODE = " + lastValue + ";", conDataBase);  

        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = cmdDataBase;
        DataTable dbdataset = new DataTable();
        sda.Fill(dbdataset);
        BindingSource bSource = new BindingSource();

        bSource.DataSource = dbdataset;
        dgvData.DataSource = bSource;
        sda.Update(dbdataset);         
    }

What I'm doing now is just pressing a button at a time and it generates a datagridview with one letter, corresponding to last code. I need a cycle to get all letters from all codes in an array.

My SQL table:

So the objective: user sends 805316096, 805316100 and 805316185, the array must be [0, 4, Key_Down].

What I'm doing now is I'm sending code 805316185, press a button and get "Key_Down", next I send 805316100 and the "Key_Down" is replaced with 4.

lastValue (sent 3 different codes):

Thanks in advance

You're going to need to post a sample of the input, the exact value returned into lastValue and what you eventually want to look like because, so far, it reads like you want to turn 1 into a and you don't need SQL and an Arduino to do that -- int i = 1; char c = (char)(96 + i); — jimbobmcgee
– jimbobmcgee, Commented May 31, 2016 at 16:10
And lastValue? Is it one code per line, or do you have to split the words yourself (and how is each word separated)? — jimbobmcgee
– jimbobmcgee, Commented May 31, 2016 at 16:28
onde code per line, as you can see in the second image I uploaded. When I press the button, I can only get the Name of the last lastValue — João Amaro
– João Amaro, Commented May 31, 2016 at 16:36

Community · Accepted Answer · 2017-05-23 10:28:58Z

2

In your form, maintain a List<string> of codes (as per Chris Steele's answer), then in your DataReceived event, add to the list:

private readonly object _sync = new object();
private readonly List<string> _receivedCodes = new List<string>();

private void SerialPort1_DataReceived(object sender, SerialDataReceivedEventArgs e)
{
    var readValue = (serialPort1.ReadLine() ?? string.Empty).Trim(); //reads serial  

    if (!string.IsNullOrEmpty(readValue))
    {
        lock (_sync) _receivedCodes.Add(readValue);
        lastValue = readValue;
    } 
}

Add a method to your form code to lookup the codes from the database and emit them as an array:

public string[] GetMappedKeyCodes()
{
    var mapping = GetMapping();

    List<string> clone;
    lock (_sync) clone = new List<string>(_receivedCodes);

    return clone.Select(code => {
                     string key;
                     return mapping.TryGetValue(code, out key)
                          ? key
                          : null;
                 })
                .Where(key => !string.IsNullOrEmpty(key))    // skip unmapped values
                .ToArray();
}

private IReadOnlyDictionary<string, string> GetMapping()
{
    var constring = @"Data Source=.\SQLEXPRESS...";

    using (var conn = new SqlConnection(constring))
    using (var cmd = new SqlCommand("select LETTER, CODE from TABLE;", conn))

    var mappedCodes = new Dictionary<string, string>();
    using (var reader = cmd.ExecuteReader())
    {
        if (reader.MoveNext())
        {
            var codeOrd = reader.GetOrdinal("CODE");
            var letterOrd = reader.GetOrdinal("LETTER");

            do
            {
                var code = reader.GetString(codeOrd);
                var letter = reader.GetString(letterOrd);
                mappedCodes[code] = letter;                
            }
            while (reader.MoveNext());
        }
    }

    return new ReadOnlyDictionary<string, string>(mappedCodes);
}

Cache the result of GetMapping, at your leisure. You will probably also need to make sure that you are getting all the data, as you might not get a complete line from the serial port every time.

edited May 23, 2017 at 10:28

CommunityBot

11 silver badge

answered May 31, 2016 at 19:03

jimbobmcgee

1,73113 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

João Amaro Over a year ago

Thanks @jimbobmcgee! I'm sorry for bothering you, but how do I make my program to enter the method? When I run it, it just doesnt pass through it

jimbobmcgee Over a year ago

You call it yourself, in code, at the point where you need the array.

Chris Steele · Accepted Answer · 2016-05-31 18:07:13Z

1

Store your codes in a list, and convert to array if really necessary:

List<string> codes = new List<string>();
codes.Add(code1);
codes.Add(code2);
codes.Add(code3);
string[] codeArray = codes.ToArray();

answered May 31, 2016 at 18:07

Chris Steele

1,3811 gold badge9 silver badges21 bronze badges

Comments

Filipe Santos · Accepted Answer · 2016-05-31 19:20:39Z

0

Here my example: OleDbConnection conn = new OleDbConnection(); { DataTable dt = new DataTable(); conn.ConnectionString = "your conn sring"

            OleDbCommand comm = new OleDbCommand();
            {
                comm.CommandText = "Select * from *****";
                comm.Connection = conn;
                OleDbDataAdapter da = new OleDbDataAdapter();
                {
                    da.SelectCommand = comm;
                    da.Fill(dt);
                }

Note that dt is your DataTable

answered May 31, 2016 at 19:20

Filipe Santos

1

Collectives™ on Stack Overflow

Return database values to array?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related