I have read a lot of posts about SQL Server, C# and read out of the databases. I have two topics I would found a solution for:

1. I read an registration template which write all the information into the databases. Right now I want to create a login. How can I read out the database and found out if the email and password are related to each other. Furthermore of course if the email is already registered.

Therefore I started to create this code:

    protected void btn_login_Click(object sender, EventArgs e)
    {           
        if (tb_email_entry.Equals("") || tb_password_entry.Equals(""))
        {
            la_email_login.Text = "Alle Felder füllen";
            la_pas`enter code here`sword_login.Text = "Alle Felder füllen";
        }

        SqlConnection con = new SqlConnection(CONNECTIONSTRINFS);
        {
            SqlCommand check = new SqlCommand("EXEC dbo.CheckLoginInfo @Email", con);

            con.Open();

            try
            {
                check.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
            }

            // SQLDatareader
            SqlDataReader sqlReader = check.ExecuteReader();

            while (sqlReader.Read())
            {
                // Ausgabe des ersten Wertes aus dem query
                if (sqlReader.GetValue(0).ToString().Equals(""))
                {
                    la_email_login.Text = "Email noch nicht registriert"; 
                }
                else
                {
                    la_email_login.Text = "Anmeldung Erfolgreich";
                }
            }

            con.Close();            
        }

I get an error on the line:

check.ExecuteNonQuery(); 

In SSMS the stored procedures and a test executable are working fine.

2. I would like to find out how many rows a table has. Therefore I think about something like that:

    string sql = "SELECT COUNT(*) FROM [DB].[dbo].[myTable]";
    SqlCommand cmd = new SqlCommand(sql, connectionString);
    SqlDataReader mySqlDataReader = cmd.ExecuteReader();
   
    int count = mySqlDataReader.GetInt32(0); 
   

Many thanks for your support.

CodePudding user response：

First of all, you are using SqlCommand with a query that contains @Email and I cannot see where you supplied it:

SqlCommand check = new SqlCommand("EXEC dbo.CheckLoginInfo @Email", con);

So, check it out by supplying the @Email by using SqlParameter as like as below example:

SqlParameter emailParam = new SqlParameter("@Email", SqlDbType.NVarChar);

Then use this object to supply Email to your query:

emailParam.Value = tb_email_entry; // or any other var

CodePudding user response：

Ok, so I'm going to assume you just doing this for the sake of learning. The reason? Well, you really do want to consider using the built in security, and the built in logon system. The reason for this is many, but a simple reason is that the built in secuirty system will save you a week, or even a month of work.

But, lets ignore the above for now.

So, you have a store procedure, but you don't outline what the name(s) of the parameters are.

So, regardless, the approach, if you did not have that store procedure could be like this:

        using (SqlConnection con = new SqlConnection("connection goes here"))
        {
            string strSQL = "SELECT * FROM tblUsers WHERE UserName = @User "  
                            "AND PassWord = @Pass";

            using (SqlCommand cmdSQL = new SqlCommand(strSQL,con))
            {

                DataTable rstUser = new DataTable();
                con.Open();
                cmdSQL.Parameters.Add("@User", SqlDbType.NVarChar).Value = txtEmail.Text;
                cmdSQL.Parameters.Add("@Pass", SqlDbType.NVarChar).Value = txtPass.Text;
                rstUser.Load(cmdSQL.ExecuteReader());

                if (rstUser.Rows.Count == 0)
                {
                    // code here - user password or user name wroting
                }
                else
                {
                    // code here - user ok
                }
            }
        }

And, if you have a store procedure, then say this:

        using (SqlConnection con = new SqlConnection("connection goes here"))
        {
            string strSQL = "SELECT * FROM tblUsers WHERE UserName = @User "  
                            "AND PassWord = @Pass";

            using (SqlCommand cmdSQL = new SqlCommand(strSQL,con))
            {

                DataTable rstUser = new DataTable();
                con.Open();
                cmdSQL.Parameters.Add("@User", SqlDbType.NVarChar).Value = txtEmail.Text;
                cmdSQL.Parameters.Add("@Pass", SqlDbType.NVarChar).Value = txtPass.Text;
                rstUser.Load(cmdSQL.ExecuteReader());

                if (rstUser.Rows.Count == 0)
                {
                    // code here - user password or user name wroting
                }
                else
                {
                    // code here - user ok
                }
            }
        }

So, you can load the data into a datatable. At that point, you can count the rows (if you have a match), and you also free to see/get/grab/use columns from that query or stored procedure in your code.