c # class to manage different BD providers

Question

c # class to manage different BD providers

Navigation

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

0

I have the following code, this class where I define the interface

namespace classBD.clases.connexiones
{

    public interface IConexion
    {
        void OpenConection();
        void CloseConnection();
        void ExecuteQueries(string Query);
        object ShowDataInGridView(string Query);
        DbDataReader DataReader(string Query);
        Boolean Ejecutar(string query, Dictionary<string,string> parametros);

    }


    public class ConnectionFactory
    {
       public static IConexion GetConnection()
       {
           string valconfig = "sqlite";
           if(valconfig == "sqlite")
               return new conexionSQlite();
           else
               return new conexionSQLServer();
       }
    }

}

Then I have an implementation for sqlServer and another for Sqllite (I put only a piece of code)

namespace classBD.clases.connexiones
{

    public class conexionSQlite : IConexion
    {
        SQLiteConnection conexionSQLite;


        public void OpenConection()
        {
            conexionSQLite = new SQLiteConnection(ConnectionString);
            try
            {
                conexionSQLite.Open();
            }
            catch (Exception ex)
            {
                string error = ex.Message;
            }
        }

        public void CloseConnection()
        {
            conexionSQLite.Close();
        }

    ...
    ...


        public Boolean Ejecutar(string query, Dictionary<string, string> parametros)
        {
            Boolean retorno = true;
            this.OpenConection();
            SQLiteCommand Comando = new SQLiteCommand(query, conexionSQLite);
            foreach (var p in parametros)
            {
                Comando.Parameters.Add(p);
            }

            try
            {
                Comando.ExecuteNonQuery();
            }
            catch
            {
                retorno = false;
            }

            return retorno;
        }


    }
}

Then I have something similar for sql server.

When executing this code the error I receive is this

Thanks

c# sqlite

asked by ilernet 18.05.2017 в 10:03

source

2 answers

Error saving image with ajax in codeigniter How to read the data of a php object?

score 0 · Answer 1

There is a lack of information in your question, but I imagine that what you want is to have a dictionary with the name of the parameter and its value, and add them to the parameter collection. For that, you should first use the AddWithValue method instead of Add of the collection Parameters . Your code should look like this:

foreach (var p in parametros)
{
     Comando.Parameters.AddWithValue(p.Key, p.Value);
}

To complete, the error it gives you is because you can not add a KeyValuePair to the collection of parameters. If you want to add only the name of the parameter to put its value afterwards, your code should be:

Comando.Parameters.Add(p.key,SqlDbType.VarChar); //Aqui debe ir el tipo que corresponda

As I understand that you do not want to worry about the types of the parameters, the best solution in your case is probably the first one using AddWithValue

score 0 · Answer 2

When you're doing this part of the code:

SQLiteCommand Comando = new SQLiteCommand(query, conexionSQLite);
foreach (var p in parametros)
{
    Comando.Parameters.Add(p);
}

Specifically in the .Add you should use any of the overloads you have, if I remember correctly one of the ones you can use is to pass a new SQLiteParameter() so you would have the code like this:

SQLiteCommand Comando = new SQLiteCommand(query, conexionSQLite);
foreach (var p in parametros)
{
    Comando.Parameters.Add(new SQLiteParameter(p.Key,p.Value));
}

Finally, as a tip I have seen that you use var for the temporary definition of foreach , I recommend that if you know what type you use the correct one, as this will prevent future errors.