I'm trying to create a database tool (linux, docker, c#, sql server).

I use a Database.json file that contains the definition of a database, and try to create a database based on it.

This is a sample script for a table that I create in my program:

if not exists (select * from sys.tables where [name] = 'Cultures' and schema_name([schema_id]) = 'languages')
begin
    create table [languages].[Cultures]
    (
        Id bigint not null identity(1,1)
    )
end

And I'm using this C# code to run it:

public static void Run(string connectionString, string sql)
{
    using (var connection = new SqlConnection(connectionString))
    using (var command = new SqlCommand())
    {
        connection.Open();
        command.Connection = connection;
        command.CommandText = sql;
        var result = command.ExecuteNonQuery();
        Console.WriteLine($"Query:\n{sql}\nResult: {result}");
    }
}

The problem I have is that sometimes it creates the table and sometimes it does not.

And the only thing that I have access to is the return value of ExecuteNonQuery method which is always -1 regardless of the success or failure.

So, I'm stuck here.

How can I run DDL commands on SQL Server from C#, and access the result of its execution?

CodePudding user response：

DDL does not return a rowcount affected, because there aren't any. You need to return something yourself

if not exists (select 1 from sys.tables where name = 'Cultures' and schema_name([schema_id]) = 'languages')
begin
    create table [languages].[Cultures]
    (
        Id bigint not null identity(1,1)
    );
    select 1;
end
else
    select 0;

public static void Run(string connectionString, string sql)
{
    using (var connection = new SqlConnection(connectionString))
    using (var command = new SqlCommand(sql, connection))
    {
        connection.Open();
        var result = (int)command.ExecuteScalar();
        Console.WriteLine($"Query:\n{sql}\nResult: {result}");
    }
}