Click here to Skip to main content
15,121,564 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All

I have an application that uses MS SQL and from time to time I need to alter tables, add columns, etc. Obviously this could be done using SSMS, but not all my clients have the ability to install and use that on their workstations (their IT departments restrict them) so I was wondering whether there was a way to create a basic one that I can use without the need to install SSMS?

I have tried the ".InfoMessage" event and I do not receive messages back, I tried the ".StatementCompleted" and that works fine if say I am updating a record. but I want to enter a command (or a series of commands) like
ALTER TABLE [dbo].[Batch] ADD [totalLoose] int
and receive back the message as I would in SSMS, either it finished successfully or failed.

Is this possible, as I have spent so much time searching and trying a few things and all have so far fallen well short?

thanks, in advance.

DT

What I have tried:

using (conn = new SqlConnection(ConnectionString))
{

    conn.FireInfoMessageEventOnUserErrors = true;
    conn.InfoMessage += OnInfoMessage;
    conn.Open();
    using (sc = new SqlCommand(sql, conn))
    {
        sc.StatementCompleted += OnStatementCompleted;
        sc.ExecuteNonQuery();
    }
    conn.Close();
    conn.InfoMessage -= OnInfoMessage;
    conn.FireInfoMessageEventOnUserErrors = false;
}


private void OnStatementCompleted(object sender, StatementCompletedEventArgs e)
{
    ResultTBC.Text += "\n" + "("+e.RecordCount + " row(s) affected)";
}

private void OnInfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    ResultTBC.Text += "\n" + e.Message;
}
Posted
Updated 28-Aug-20 14:22pm
v2

1 solution

An interesting conundrum .. btw this Using InfoMessage Event of SqlConnection Object [^] may give you more info on the InfoMessage Event

Ok, given we know the client side has restrictions, pleading for sanity with their IT is likely a waste of time - so here's a thought ..

1) write an interface that fires an InformationEvent - you can define what info it needs to carry - in fact, it can be a 'unifying' event but it wraps SQL execution
2) use that to build classes to handle various commands for which you might want to fire events - You might only need two implementations, one for Update/Insert data SQL statements, one for you 'ALTER ... ' type statements .. but your program will receive one type of event, your InformationEvent .. you can still use it to handle 'batches' of SQL commands as well ..
   
v2
Comments
d_train2014 28-Aug-20 19:22pm
   
Thanks for your response. I am still finding my feet with all this, so I will look into your suggestions.

I already tried the InfoMessage approach and it doesn't show anything.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900