How to open a sql Server connection in C #?

Question

How to open a sql Server connection in C #?

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)

1

Well my problem is that I want to open a sql server connection so I can make a query BD and get a possible result either erroneous or successful.

Code of my method to authenticate users:

public string Autentificar()
{

    string sql = @"SELECT * FROM usuario WHERE nick = david AND pass = password";

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conexion"].ToString()))
    {
        conn.Open(); ---- Error
        SqlCommand cmd = new SqlCommand(sql, conn); //ejecutamos la instruccion

        int count = Convert.ToInt32(cmd.ExecuteScalar()); //devuelve la fila afectada

        if (count == 0)
            return "0";
        else
            return "1";

    }

}

Error exiting:

User login failed ''.

My configuration code:

<configuration>

    <system.web>
        <compilation debug="true" targetFramework="4.0" />
    </system.web>

  <connectionStrings>
    <add name="conexion" connectionString="Data Source=NQ-ADUANA-054-V\SQLEXPRESS; Initial Catalog=bilbao;Persist Security Info=False;" providerName="System.Data.SqlClient"/>
  </connectionStrings>

</configuration>

sql c# sql-server

asked by David 24.03.2017 в 22:50

source

3 answers

1

First you have to create a connection string by creating a sqlConnection object, where you must indicate the details of the database (name, server, etc):

        public void GetConnection()
        {
           SqlbConnection Connection = new SqlbConnection("Integrated Security=true;Initial Catalog=(NOMBRE_DE_LA_BASE_DE_DATOS);server=(local O EL QUE SEA)");
            Connection.Open();                
        }
        public void CloseConnection()
        {
           SqlbConnection Connection = new SqlConnection("Integrated Security=true;Initial Catalog=NOMBRE_DE_LA_BASE_DE_DATOS;server=local_O_EL_QUE_SEA");
            Connection.Close();
            Connection.Dispose();
        }

With those methods you can do the consultations. example:

 public void EjecutarQuery(String consulta, List<Parametros> lst)
        {
            try
            {
                SqlCommand Comando = new SqlCommand(consulta, GetConnection());
                if (lst != null)
                {
                    for (int i = 0; i < lst.Count; i++)
                    {
                        if (lst[i].Dirección == ParameterDirection.Input)
                        {
                            Comando.Parameters.AddWithValue(lst[i].Nombre, lst[i].Valor);
                        }
                        if (lst[i].Dirección == ParameterDirection.Output)
                        {
                            Comando.Parameters.Add(lst[i].Nombre, lst[i].TipoDato, lst[i].Tamaño).Direction = ParameterDirection.Output;
                        }
                    }
                    GetConnection();//este metodo tiene la conexion de la base de datos
                    Comando.ExecuteNonQuery();
                    CloseConnection();//aqui cierras la conexion
                    for (int i = 0; i < lst.Count; i++)
                    {
                        if (Comando.Parameters[i].Direction == ParameterDirection.Output)
                        { lst[i].Valor = Comando.Parameters[i].Value.ToString(); }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

answered by 25.03.2017 в 04:40

0

You need to make sure that your connection string is well formed:

<add name="conexion" 
connectionString="Data Source=servidor\instancia;Initial Catalog=BASE_DE_DATOS;Persist Security Info=True;User ID=USUARIO;Password=CONTRASEÑA;Max Pool Size=40" 
providerName="System.Data.SqlClient" />

Also make sure that the user you configured has access to the database. You can try this by trying to login with Microsoft SQL Server Management Studio to your database.

To configure security using SQL Server Authentication you can follow this tutorial: Enable SQL Authentication

answered by 24.03.2017 в 22:59

Mark and unmark checkbox independently Chrome console?

score 2 · Accepted Answer

Try this connection string.

<configuration>

<system.web>
    <compilation debug="true" targetFramework="4.0" />
</system.web>

<connectionStrings>
 <add name="conexion" connectionString="Data Source=NQ-ADUANA-054-V\SQLEXPRESS; Initial Catalog=bilbao;Integrated Security=SSPI;"  providerName="System.Data.SqlClient"/>
</connectionStrings>

</configuration>