Sql connection with authentication and visual studio 2019

Question

0.00/5 (No votes)

See more:

Good afternoon people of the forum:

I was testing the validation of my connection string for the login having a SQL authentication, my idea was that if the string was correct, it would open the main form and save that connection string.

But when validating it and sending it to call the login button, regardless of whether the validation is right or wrong, it opens the main form.

If you can tell me how to validate the connection string and how to call it to my forms if it is correct.

What I have tried:

I wanted to do it using a class that builds the connection string:

    internal class Connection
    {
        public static SqlConnection Connect(string user,string password)
        {
            string string = "server=EMMANUEL\\SQLEXPRESS;database=Sales;User ID"+"="+user+";"+ "Password"+"="+password;
            SqlConnection connection = new SqlConnection(string);
            return connection;
        }
    }

    internal class Modelo
    {
        public bool Verificar_Conexion(string user, string contraseña)
        {
            bool resultado;
            try
            {
                using (SqlConnection conexion = Conexion.Conectar(user, contraseña))
                {
                    conexion.Open();
                    return resultado = true;
                }
            }
            catch
            {
                return resultado=false;
            }
        }
        
    }

internal class Control
    {
        public string ctrluser(string user, string contraseña)
        {
            Modelo modelo = new Modelo();
            string respuesta = "";

            if (string.IsNullOrEmpty(user) || string.IsNullOrEmpty(contraseña))
            {
                respuesta = "Llene todos los campos";
            }
            else
            {
                if (modelo.Verificar_Conexion(user, contraseña))
                {
                    respuesta="El usuario o contraseña son incorrectos";
                }
                
            }
            return respuesta;
        }
    }

Posted 21-May-22 13:51pm

Emmanuel Hernández Bazán

Updated 21-May-22 19:14pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2022-05-21T15:17:00

Solution 1

Just creating an instance of the SqlConnection object will NOT test the connection. You actually have to open the connection to try it.

Posted 21-May-22 15:17pm

Dave Kreskowiak

OriginalGriff · Answer 2 · 2022-05-21T19:14:00

To add to what Dave has - rightly - said: it's a very bad idea to hard code your connection string at all. When you do that, even if it is only used in one place it means that when you move to production you need to change the software for each site, and release software that is untested. You should store it in a configuration file, either the XML based settings file myappname.exe.config or a human editable file of your choice.
This is how I do it: Instance Storage - A Simple Way to Share Configuration Data among Applications[^] but that may be overkill for your project.

I'd also strongly suggest that you don't do it like that at all: even if you do open the connection for testing in your Verificar_Conexion method, you don't close it again, you don't Dispose of it anywhere, and you can't use a using block to ensure that the connection object is automatically closed and disposed when you are finished with it. SQL connections are a scarce resource, and need to be deleted as soon as you are finished with them - and not kept open for the life of the application!
Plus, creating a single connection object and "recycling" it has it's own problems: if you accidentally leave a SqlDataReader open then you can't do anything else with the connection until it is closed.

Create your connection in a using block when you need it (and SqlCommand objects etc. as well) and it is all handled for you automatically:

C#

string strConnect = SMDBSupport.SMInstanceStorage.GetInstanceConnectionString("MyDatabaseName");
string sql = "SELECT Id, Desc FROM MyTable";
using (SqlConnection con = new SqlConnection(strConnect))
    {
    try
        {
        con.Open();
        using (SqlCommand cmd = new SqlCommand(sql, con))
            {
            using (SqlDataReader read = cmd.ExecuteReader())
                {
                while (read.Read())
                    {
                    int id = (int)read[0];
                    string desc = (string) read[1];
                    }
                }
            }
        }
    catch (Exception ex)
        {
        Debug.WriteLine(ex.ToString());
        }
    }