Calling the SQL variable for the program C#

Question

I have to create a c# function that will call the variable I created in sql-server but I can’t figure out how to do it! C# code:

var ss = new SqlConnection("Data Source=(LocalDb)\\MSSQLLocalDB;Initial Catalog=tupaidb;Integrated Security=True");
ss.Open();
if (ss.State == ConnectionState.Open)
{
       
var ee = new SqlDataAdapter("[dbo].[dbo.Bi_DispositivoInset]", ss);

ee.SelectCommand.CommandType = CommandType.StoredProcedure;
ee.SelectCommand.Parameters.Add("@disp_mac", SqlDbType.NVarChar, (17)).Value = macAddr;
ee.SelectCommand.Parameters.Add("@disp_nom", SqlDbType.VarChar,(50)).Value = nomeCompleto;
ee.SelectCommand.Parameters.Add("@ID_interno_cc", SqlDbType.Int).Value = "1";
ee.SelectCommand.ExecuteNonQuery();
ss.Close();
               
}
foreach (var s in SerialPort.GetPortNames())
{
var comPort = DetectArduino();
                
var myPort = new SerialPort {BaudRate = 9600, PortName = s};
myPort.PortName = comPort;
myPort.BaudRate = 9600;
                   
myPort.Open();
/*    IntPtr hWnd = GetConsoleWindow();
if (hWnd != IntPtr.Zero)
{
ShowWindow(hWnd, 0); //Esconde
}*/
          
while (true)
{
var dataRx = myPort.ReadLine(); 

var underscore = dataRx.Split('_');
humidade = underscore[0];
temperatura = underscore[1];
heatIndex = underscore[2];
lpgGas = underscore[3];
monoCarbo = underscore[4];
smoke = underscore[5];

Console.WriteLine(humidade);
Console.WriteLine(temperatura);
Console.WriteLine(heatIndex);
Console.WriteLine(lpgGas);
Console.WriteLine(monoCarbo);
Console.WriteLine(smoke);
Console.WriteLine(DateTime.Now);
Console.WriteLine("___________________");
                 

SqlConnection connString = new SqlConnection("Data Source=(LocalDb)\\MSSQLLocalDB;Initial Catalog=tupaidb;Integrated Security=True");
connString.Open();
                    
SqlCommand command = new SqlCommand();
command.Connection = connString;
command.CommandText = "[dbo].[dbo.Bi_medicaoInset]";
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@ID_interno_disp", SqlDbType.Int).Value = ;
command.Parameters.Add("@med_hum", SqlDbType.VarChar, (50)).Value = humidade;
command.Parameters.Add("@med_temp", SqlDbType.VarChar, (50)).Value = temperatura;
command.Parameters.Add("@med_lpg", SqlDbType.VarChar, (50)).Value = lpgGas;
command.Parameters.Add("@med_co", SqlDbType.VarChar, (50)).Value = monoCarbo;
command.Parameters.Add("@med_fumo", SqlDbType.VarChar, (50)).Value = smoke;
command.ExecuteNonQuery();
connString.Close();
                }
            }

Sql code:

DECLARE @macadr nvarchar(17)
    set @macadr= (select disp_mac FROM Bi_Dispositivo Where disp_mac=@disp_mac)

    if @macadr = null 
    begin
    insert into Bi_Dispositivo (disp_mac,disp_nom,ID_interno_cc) values (@disp_mac,@disp_nom,@ID_interno_cc)
    set @macadr=@disp_mac
    end

    select ID_interno_disp 
    from Bi_dispositivo
    Where disp_mac = @macadr

Answer 1

There are easier ways, for example below: Here I am using . net core 3.1 (recommend) and installed the package: System.Data.Sqlclient

CREATE PROCEDURE BUSCARMOTORISTA @Nome nvarchar(30)
AS

SELECT [Id] ,[Nome] ,[Cpf]
    FROM [dbo].[Motorista] 
    WHERE NOME = @NOME
GO

And for the sake of the code

  using var conn = new SqlConnection("Server=.\\SQLEXPRESS;Database=tupaidb;Trusted_Connection=yes;");
            using var command = new SqlCommand("BUSCARMOTORISTA", conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            // AQUI É O PARAMETRO DA PROCEDURE
            command.Parameters.Add(new SqlParameter("@Nome", "Fabio"));

            conn.Open();
            using SqlDataReader rdr = command.ExecuteReader();
            while (rdr.Read())
            {
                // AQUI VOCE LE A RESPOSTA, CUIDADO PRA NAO TA NULL
                int id = Convert.ToInt32(rdr["Id"]);
                string nome = rdr["Nome"].ToString();
                string cpf = rdr["Cpf"].ToString();
            }