add list the Search a substring in C#

Question

I'm create the application, but i have one question.

The client write the name of user in textbox, example 3 letters and search in database(access) and add the database.

Example: User: Rui. and search in database all nameuser "Rui".

//libraries
using Microsoft.VisualStudio.OLE.Interop;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

private void textBox1_TextChanged(object sender, EventArgs e)
{
    OleDbConnection conexao = new OleDbConnection(string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= {0}\Teste.accdb", Path.GetDirectoryName(Assembly.GetEntryAssembly().Location)));
        
    List<string> Users = new List<string>();

    
    OleDbCommand STK = new OleDbCommand($"SELECT NºCliente, NomeUser, CodigoPostal, NIF", conexao);
    STK.CommandText = $" SELECT* FROM MyTable WHERE Str(Lista_Pokemon) like '*{textBox1.Text}*'";

    User.Clear();
    //this code is invention, probably is wrong

    for(int d=0; d<Stk.Count()-1; d++)
         User.Add(...);
}

If you can help my thanks. This project is c#, net framework and the database is Access 2010. At the moment I dont create the class, but if you need tell my, i need created.

Answer 1

Look at the following code.

The using operator is used here to release resources - this is important!

var dataSource = Path.Combine(
    Path.GetDirectoryName(Assembly.GetEntryAssembly().Location),
    "Teste.accdb");

var builder = new OleDbConnectionStringBuilder();
builder.Provider = "Microsoft.ACE.OLEDB.12.0";
builder.DataSource = dataSource;

var connectionString = builder.ToString();
var sql = "SELECT ..."; // place your query here

using (var connection = new OleDbConnection(connectionString))
{
    connection.Open();

    using (var command = new OleDbCommand(sql, connection))
    using (var reader = command.ExecuteReader())
    {
        var users = new List<User>();

        while (reader.Read())
        {
            var user = new User();

            user.ClientNumber = (int)reader["NºCliente"];
            user.UserName = (string)reader["NomeUser"];
            user.CodigoPostal = (string)reader["CodigoPostal"];
            user.NIF = (string)reader["NIF"];

            users.Add(user);
        }
        // return users; // Return data from method
    }
}

This class is used for storing user data.
Change the property names and types to the ones you need.

class User
{
    public int ClientNumber { get; set; }
    public string UserName { get; set; }
    public string CodigoPostal { get; set; }
    public string NIF { get; set; }
}

And, of course, use parameters in sql queries, as @dovid showed in his example.