SQL and Visual Studio Retrieving a Table

KKoopamillion10/7/2022

Hi - I'm trying to select all the "emails" in a table called "users" with c# in visual studio. Currently I have this command:

SqlCommand cmd = new SqlCommand("select email from Users", cn);

I'm not sure how to get the data from this though, would it be doing smth like:

List<string> emails = cmd.Execute();

MMMayor McCheese10/7/2022

Something like SqlReader reader = cmd.ExecuteReader();

Then iterate over the reader.

There is a popular package called Dapper that has functionality similar ( but not identical ) to what you have written

MMMayor McCheese10/7/2022

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader

MMMayor McCheese10/7/2022

https://www.nuget.org/packages/Dapper/

KKoopamillion10/7/2022

Thank you !

Mmtreit10/7/2022

Here is a very simple example of using DataReader:
https://github.com/Treit/MiscBenchmarks/blob/main/SqlDataSetVsDataReader/Benchmark.cs

Mmtreit10/7/2022

        public List<short> ReadDataUsingDataReader()
        {
            var sql = "select OrderQty from Sales.SalesOrderDetail";
            using var cmd = new SqlCommand(sql, _conn);
            using var reader = cmd.ExecuteReader();

            var result = new List<short>();

            while (reader.Read())
            {
                result.Add(reader.GetInt16(0));
            }

            return result;
        }

KKoopamillion10/7/2022

thanks both of you I think I've got datareader working now - however, and probably more importantly, whenever I try to open a SqlConnection I get an error

An attempt to attach an auto-named database for file "path" failed. A database with the same name exists or specified file cannot be opened or is located on UNC share

I'm not sure what's going wrong here as you can tell Im not experienced with SQL at all and have just followed a basic tutorial to setup a small database alongside my c# code, the file path seems to be correct..?

MMMayor McCheese10/7/2022

Can you share your connection string without passwords?

KKoopamillion10/7/2022

cn.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C: \Users\username\OneDrive\Documents\Computing NEA Project Current(DB EDIT)\Computing NEA Project Current\Computing NEA Project Current\Computing NEA Project Current\Client Chat App\Client Chat App\Database1.mdf;Integrated Security=True";

KKoopamillion10/7/2022

the file path is really stupid because its a folder in tons of folders because of extracting/sending to zip all the time

MMMayor McCheese10/7/2022

Try and move it to a simpler park outside of one drive; one drive can cause a lot of issues with locking of files

Mmtreit10/7/2022

I didn't know you could just attach to an mdf file without actually using a server

MMMayor McCheese10/7/2022

Oh I assumed it was installed

MMMayor McCheese10/7/2022

Though tbh I’m not a localdb expert

KKoopamillion10/8/2022

Thank you moving it off one drive worked - I have one more and hopefully final issue:

            SqlCommand cmd = new SqlCommand("insert into Users values(@username,@password,@email)", cn);
            cmd.Parameters.AddWithValue("username", info["username"]);
            cmd.Parameters.AddWithValue("password", encryptedPW);
            cmd.Parameters.AddWithValue("email", info["email"]);
            int id = (int)cmd.ExecuteScalar();

KKoopamillion10/8/2022

I'm trying to add username password and email to my users table

KKoopamillion10/8/2022

And then get the ID of this new user, however I get this error:

System.Data.SqlClient.SqlException: 'Column name or number of supplied values does not match table definition.'