What is the point: I write my own class provider to work with the database, but an error occurs.

namespace Tourism.Data.Providers { public class DatabaseProvider { // Common database stuff public DbConnection Connection; public string ConnectionString = "Data Source=CRUST\\SQLEXPRESS;Initial Catalog=Tourism;Integrated Security=True"; public bool ConnectionOpened; public string Query; public void Connect(string connectionString) { ConnectionString = connectionString; Connection = new SqlConnection(ConnectionString); Connection.Open(); if (Connection.State.ToString() == "Open") ConnectionOpened = true; else ConnectionOpened = false; } public void SQLWithoutFetch(string query) { if (!ConnectionOpened) Connect(ConnectionString); Query = query; using (Connection) { try { SqlCommand command = new SqlCommand(Query); command.ExecuteNonQuery(); } catch (SqlException) { throw new Exception("Bad SQL query"); } } Connection.Close(); } }

When the command.ExecuteNonQuery(); string is executed command.ExecuteNonQuery(); an error occurs

ExecuteNonQuery: Connection property not initialized

Connecting to the database is going on, as indicated by the MessageBox with the Connection.State.ToString(); .

I tried these solutions:

  • make a full connection inside the SQLWithoutFetch method
  • change the query to the database (my current - "USE Places;")
  • I tried to do a singleton class and statics

Thanks in advance for your help.

  • public static void Connect (string connectionString) ?? - Gorets
  • I tried, it did not help. - Indenk Nov.

1 answer 1

When creating a SqlConnection you must explicitly specify the Connection field, otherwise it is automatically initialized to null .

Honestly, I don’t know why the API allows constructing invalid objects, but I think that this is not a design flaw , but still serves some purpose.

In your case you need to use:

 var command = new SqlCommand(Query, Connection);

By the way, yes. SqlConnection is IDisposable . Accordingly, if you store it as a field in your class, then you need to do the IDisposable class and provide methods for freeing resources (otherwise you may run into problems in the near future).

A simpler option is to use connection in local scopes via using .

Your current version with using incorrect, because the following will happen:

  • A Connection object will be created in the Connect method.
  • After the first call to the SQLWithoutFetch method (at the output of the using block), Connection.Dispose() will be implicitly called.
  • The next SQLWithoutFetch you try to call SQLWithoutFetch you will (at best) get an ObjectDisposedException because the object was freed in the previous call.

How to solve this I wrote above.

Another note:

 catch (SqlException) { throw new Exception("Bad SQL query"); }

Replacing a specific SqlException with error parameters with a generic Exception not allowed. try-catch is not needed here at all, because you cannot do anything inside this method to correct an error situation.

Error handling should be a higher level, therefore, an automatically thrown SqlException is good.

And, and more:

 if (Connection.State == ConnectionState.Open) ...
  • I wrote through using (Connection) about var command = new SqlCommand (Query, Connection) - this construction is emphasized by me as an error, and the description says that my arguments are incorrectly set - Indenk
  • one
    You declared Connection as DbConnection , and you need to explicitly indicate that the type of the variable is SqlConnection : public SqlConnection Connection; - Costantino Rupert
  • @Indenk Using supertypes where possible is certainly good, but not in this case, since the SqlCommand cannot be constructed using an arbitrary DbConnection . - Costantino Rupert Nov.
  • thanks a lot, it started :) - Indenk
  • @Indenk See updates of the response about using and exceptions and comparisons of enum'ов . - Costantino Rupert