Connection to slow database

Asked

Viewed 183 times

0

I have a program in C# that when I start and ask to show me the data entered in Sql Server takes time to open, but after this time is already faster. There’s a way for the first time to be faster?

My connection is made this way and only stored use:

public class AcessoBaseDadosSqlServer
{
    private SqlConnection conexao()
    {
        return new SqlConnection(Settings.Default.StringConnection);
    }

    private SqlParameterCollection sqlParameterCollection = new SqlCommand().Parameters;

    public void LimparParametros()
    {
        sqlParameterCollection.Clear();
    }

    public void AdicionarParametros(string nomeParametro, object valorParametro)
    {
        sqlParameterCollection.Add(new SqlParameter(nomeParametro, valorParametro));
    }


    // INSERIR, ALTERAR E APAGAR
    public object ExecutarManipulacao(CommandType commandType, string nomeStoredProcedureOuTextoSQL)
    {
        try
        {
            // criar um ligação à base de dados
            SqlConnection sqlConnection = conexao();
            // abrir ligação
            sqlConnection.Open();
            //criar um comando
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandType = commandType;
            sqlCommand.CommandText = nomeStoredProcedureOuTextoSQL;
            sqlCommand.CommandTimeout = 7200;

            foreach (SqlParameter SqlParameter in sqlParameterCollection)
            {
                sqlCommand.Parameters.Add(new SqlParameter(SqlParameter.ParameterName, SqlParameter.Value));
            }

            return sqlCommand.ExecuteScalar();

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

    // Consulta
    public DataTable ExecutarConsulta(CommandType commandType, string nomeStoredProcedureOuTextoSql)
    {
        try
        {
            SqlConnection sqlConnection = conexao();
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandType = commandType;
            sqlCommand.CommandText = nomeStoredProcedureOuTextoSql;
            sqlCommand.CommandTimeout = 7200;

            foreach (SqlParameter sqlParameter in sqlParameterCollection)
            {
                sqlCommand.Parameters.Add(new SqlParameter(sqlParameter.ParameterName, sqlParameter.Value));
            }

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            DataTable dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable);

            return dataTable;
        }
        catch (Exception)
        {

            throw;
        }
    }
}
  • Display the connection, how do we say whether or not it can be faster if we don’t even know what you’re doing ?

  • @bigown my question is already edited, show how I make my connection, I hope so is already a little clearer. Regards

  • I don’t understand why this foreach and the Parameter Collection are in the class

  • @Bsalvo is absolutely right, I have already edited and I have shown how the connection is made, I hope you are better.

  • That code has two of the most mistakes made in programming today. Read at least the first of https://answall.com/search?tab=votes&q=user%3a101%20%5bexce%C3%A7%C3%a3o%5d. Don’t treat exception if you don’t know how to do it. There are other weird things in the code. Reopen.

  • @Bigown Thank you, I am not experienced so I believe I am making mistakes and practices less suitable, so I need people like you to give me directions to be able to do things the right way and evolve. Thank you

  • @When Diogosousa is inexperienced, it is best to learn first and then pick up more complex things. What you’re doing is what’s called doing the roof before you build the foundation, it doesn’t usually work out. My suggestion is to start slower, understanding each concept before trying to make code functional.

  • Just like @bigown said, there are some pretty strange things about the way you chose to access the database. Here is a link to get started on the most basic access to an SQL Server database: http://www.macoratti.net/08/11/c_sql_m1.htm

  • thanks @Perozzo for the tip, I followed a tutorial and thought it was the best way but I’ll try to figure out more correct ways to do, thanks again.

Show 4 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.