C# Running a DDL command

Asked

Viewed 156 times

0

I’m trying to run the Ddl command below that creates the table I need, but even not returning error the table is not created, would anyone have any idea where I’m going wrong? 

public const string scriptCriacao =  " if exists(select * from sys.objects as o where o.type =N'u' and o.name = N'Tuss_temp')"+
" drop table Tuss_temp "+
" Create Table Tuss_temp ( "+
" competencia         nvarchar(30), "+
" codigoTerminologia  float, "+
" nomeTerminologia    nvarchar(256), "+
" codigoTermo nvarchar(30), "+
" termo nvarchar(256), "+
" dtInicioVigencia datetime, "+
" dtFimVigencia datetime, "+
" dtFimImplementacao datetime, "+
" tipoAcao nvarchar(256), " +
")";

String connectionString = "Data Source=1.1.1.1;Initial Catalog=PortalTISS_v3;Persist Security Info=True;User ID=sa;Password=teste";

SqlConnection SqlConn = new SqlConnection(connectionString);
SqlConn.Open();

SqlCommand cmd = new SqlCommand(scriptCriacao, SqlConn);
cmd.CommandType = CommandType.Text; 
cmd.ExecuteNonQuery();
c# sql .net

  • Make sure it’s not the missing server port in connectionString.

  • The connection is established I don’t think this is it, the problem is that I execute the command that does not return error, but does not effect the creation of the table

  • The solution was to separate the scripts as can be seen below

  • I replicated exactly your example, just by changing the connection string data and it worked perfectly. Another issue you should consider is bank access permissions for other applications.

  • 1

    public const string scriptDrop = " if exists(select * from sys.Objects as o Where o.type =N'u' and o.name = N'Tuss_temp')"+ " drop table Tuss_temp "; public const string scriptCreate = " Create Table Tuss_temp ( "+ " nvarchar competition(30), "+ " codigoTerminologia float, "+
" nomeTerminologia nvarchar(256), "+
" codigoTermo nvarchar(30), "+
" termo nvarchar(256), "+
" dtInicioVigencia datetime, "+
" dtFimVigencia datetime, "+
" dtFimImplementacao datetime, "+
" tipoAcao nvarchar(256), " +
")";

  • If possible, turn your comment into an answer and we will leave the question as solved. This is essential to make the "unanswered" queue fit.

Show 1 more comment

1 answer

0

I solved the problem by separating the script into two as can be seen below.

Note: The constant with the table name is unrelated to the problem in question. Ismael in the comments did not have the problem to execute the composite script, I believe it may be the version of visual studio/C# I am using and/or the version of sql server I want to use.

Code.:

public const string NomeTabela = "Tuss_temp";

public const string scriptDrop = " if exists(select * from sys.objects as o where o.type =N'u' and o.name = N'"+ NomeTabela + "')" + " drop table " + NomeTabela;

public const string scriptCreate = " Create Table " + NomeTabela + " ( " +
    " competencia         nvarchar(30), " +
    " codigoTerminologia  float, " +
    " nomeTerminologia    nvarchar(256), " +
    " codigoTermo nvarchar(30), " +
    " termo nvarchar(256), " +
    " dtInicioVigencia datetime, " +
    " dtFimVigencia datetime, " +
    " dtFimImplementacao datetime, " +
    " tipoAcao nvarchar(256), " +
    ")";

Browser other questions tagged c# sql .net

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