How to load Results from a different method into a variable using C#

Question

My Main Method is calling another method(SqlConnector) which is fetching results from SQL database. I need load the results from SqlConnector Method into a variable called "ID".

Nothing happens when i execute this. I don't think my SqlConnector Method is getting invoked.

private static void Main(string[] args)
{
    string SyncType = args[0];
    try
    {
        if (SyncType == "Compute")
        {
            string InitialSQLStatement = ($"exec StartUsage '{SyncType}'");
            var ID = SqlConnector(InitialSQLStatement);

            int RowID = Convert.ToInt32(ID);
            Console.WriteLine($"{ID},{RowID}");
            System.Threading.Thread.Sleep(TimeSpan.FromSeconds(2));

            ...Calling a different Class in case of Compute
            Compute Compute = new Compute();
            var task = Compute.GetBaseDetails(RowID);
            task.Wait();
        }

        else if (SyncType == "Blob")
        {
            ...Calling a different Class in case of Blob
        }

        else if (SyncType == "FileShare")
        {
            ...Calling a different Class in case of FileShare
        }

    catch (Exception ex)
    {
        Console.WriteLine($"Exception: {ex.Message}");
    }
}

public static SqlDataReader SqlConnector(string SQLStatement)
{
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    builder.DataSource = Properties.Settings.Default.SQLServerName;
    builder.UserID = Properties.Settings.Default.SQLServerAdmin;
    builder.Password = Properties.Settings.Default.SQLServerAdminPasword;
    builder.InitialCatalog = Properties.Settings.Default.DatabaseName;

    using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
    {
        StringBuilder sb = new StringBuilder();

        using (SqlCommand command = new SqlCommand(SQLStatement, connection))
        {
            connection.Open();
            SqlDataReader Executed = command.ExecuteReader();

            connection.Close();
            return Executed;
        }
    }
}

Answer 1

I think your Problem is, that you cannot convert the SqlReader to an Integer. You have to read first, then Convert the string to an integer. I didn't test the code, but this should work.

private static void Main(string[] args)
    {
        string SyncType = args[0];
        try
        {
            if (SyncType == "Compute")
            {
                string InitialSQLStatement = ($"exec StartUsage '{SyncType}'");
                int RowID = ConnectAndReadID(InitialSQLStatement);
                Console.WriteLine($"{RowID}");
                System.Threading.Thread.Sleep(TimeSpan.FromSeconds(2));

                //...Calling a different Class in case of Compute
                Compute Compute = new Compute();
                var task = Compute.GetBaseDetails(RowID);
                task.Wait();
            }

            else if (SyncType == "Blob")
            {
                //...Calling a different Class in case of Blob
            }

            else if (SyncType == "FileShare")
            {
                //...Calling a different Class in case of FileShare
            }
        }

        catch (Exception ex)
        {
            Console.WriteLine($"Exception: {ex.Message}");
        }
    }

    public static int ConnectAndReadID(string SQLStatement)
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.DataSource = Properties.Settings.Default.SQLServerName;
        builder.UserID = Properties.Settings.Default.SQLServerAdmin;
        builder.Password = Properties.Settings.Default.SQLServerAdminPasword;
        builder.InitialCatalog = Properties.Settings.Default.DatabaseName;

        using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
        {
            StringBuilder sb = new StringBuilder();

            using (SqlCommand command = new SqlCommand(SQLStatement, connection))
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    string str = reader.GetString(0); // Read first column
                    connection.Close();
                    return Convert.ToInt32(str);
                }
                else
                {
                    // Your query fails
                    connection.Close();
                    return -1;
                }
            }
        }
    }

    public static List<int> ConnectAndReadIDs(string SQLStatement)
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.DataSource = Properties.Settings.Default.SQLServerName;
        builder.UserID = Properties.Settings.Default.SQLServerAdmin;
        builder.Password = Properties.Settings.Default.SQLServerAdminPasword;
        builder.InitialCatalog = Properties.Settings.Default.DatabaseName;

        using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
        {
            StringBuilder sb = new StringBuilder();

            using (SqlCommand command = new SqlCommand(SQLStatement, connection))
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                List<int> Ids = new List<int>(); // Create a List since you have multiple ids
                while (reader.Read()) // Instead of checking once if the reader has data, read rows until it doesnt have data anymore
                {
                    string str = reader.GetString(0); // Read first column
                    Ids.Add(Convert.ToInt32(str)); // Add the value to the Ids List
                }
                connection.Close();
                return Ids; // Return all Ids
            }
        }
    }