Dapper ensure that query succeeded best practice

Question

I want to use Dapper into my WebApi Project. I Have a method for creating database from query:

    public async Task<bool> CreateDatabase()
    {
        var connection = _connection.GetOpenConnection();

        bool created = await connection.ExecuteAsync(SqlQuery.CreateDatabase); 
    }

My question is, how to write query for creating database and how to ensure, that query completed succesfuly and which method from dapper to use?

My SqlQuery.CreateDatabase looks like this:

IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'DataBase')
  BEGIN
    CREATE DATABASE DataBase
  END

Do i need to return something from query?

Dapper's method **ExecuteAsync** returns an integer value, see what is returning after you execute the query. Your variable (**created**) shouldn't be of type bool. — leopcode, Jan 08 '20 at 15:22
Typically, a SQL Server database is created via SQL Server Management Studio (SSMS). A .NET WebApi would then connect to that database to do CRUD data manipulations. Are you sure that you need to create a SQL Server database via dapper? — JohnH, Jan 08 '20 at 15:26

score 2 · Accepted Answer · answered Jan 08 '20 at 15:31

If a problem occurs, it should already present as an exception - probably a SqlException - telling you about it, so: what you have should already be fine. Note, though, that if your connection string tries to connect to the database that doesn't yet exist, then Open[Async] is likely to fail. Ultimately, you could just try connecting to the specified database afterwards to be sure, but again, what you have should already be fine.

Note that it is often the case that app accounts do not have permissions to create databases. In that scenario, you'll need to get a DBA to create the database for you, and tell you the connection details.

Dapper ensure that query succeeded best practice

1 Answers1