C#•3y ago

SQL and Visual Studio Retrieving a Table

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);

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();

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

10 Replies

Mayor McCheese•3y ago

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 https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader https://www.nuget.org/packages/Dapper/

koopaOP•3y ago

Thank you !

mtreit•3y ago

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

        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;
        }

        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;
        }

koopaOP•3y ago

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

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..?

Mayor McCheese•3y ago

Can you share your connection string without passwords?

koopaOP•3y ago

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"; the file path is really stupid because its a folder in tons of folders because of extracting/sending to zip all the time

Mayor McCheese•3y ago

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

mtreit•3y ago

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

Mayor McCheese•3y ago

Oh I assumed it was installed Though tbh I’m not a localdb expert

koopaOP•3y ago

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();

            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();

I'm trying to add username password and email to my users table 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.'

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

Gaming

Programming

SQL and Visual Studio Retrieving a Table