I can not dropping view with SMO

Isidro Padilla

6y787

I can not Dropping View with SMO

Let's see, after googling a lot and going to 2 of the most

important C # forums I still don't understand why to delete

a View from a SQL Server database I have to create it and

then delete it, on the contrary, it won't delete a view

existing in any of the ways I've tried.

There must be some reason for it that I cannot understand.

On the other hand, what sense does what Microsotf does in

its example of the following link where it creates it and

immediately deletes it. Something that I consider absurd.

Link: https://docs.microsoft.com/ca-es/sql/relational-databases/server-management-objects-smo/tasks/creating-altering-and-removing-views?view=sql-server-2017&viewFallbackFrom=aps-pdw-2016-au7

private void BtnBorrarVista_Click(object sender, EventArgs e)

{

try

{

if (MessageBox.Show("Está intentando borrar la Vista " + textBoxBorrarVista.Text + " ¿Está seguro de que quiere eliminarla?", "¡Atención!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

{

m_Server = new Server(cmbServerName.Text);

if (!string.IsNullOrWhiteSpace(cmbServerName.Text))

{

if (m_Server != null && !string.IsNullOrWhiteSpace(cmbDbName.Text))

{

Database myNewDatabase = m_Server.Databases[cmbDbName.Text];

if (myNewDatabase != null)

{

Microsoft.SqlServer.Management.Smo.View view;

view = new Microsoft.SqlServer.Management.Smo.View(myNewDatabase, textBoxBorrarVista.Text, "Sales");

//=================Create view. Why? ===========================

view.TextHeader = "CREATE VIEW [Sales]." + textBoxBorrarVista.Text + " AS";

view.TextBody = "SELECT h.SalesOrderID, d.OrderQty FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID";

//Create the view on the instance of SQL Server.

view.Create();

MessageBox.Show("La vista de la 'Tabla' 'Sales' de " + cmbDbName.Text + " se creó satisfactoriamente ");

//==============================================================

//Checking view

MessageBox.Show("Check view: " + view);

if (view != null)

{

view.Drop();

MessageBox.Show("La Vista " + textBoxBorrarVista.Text + " se borró con éxito");

}

else

{

MessageBox.Show("Operación abortada. La Vista " + textBoxBorrarVista.Text + " no se ha borrado");

}

catch (Exception ex)

{

MessageBox.Show(ex.Message.ToString());

}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Isidro Padilla6y ago
Hi Jefferson S. Motta, thank you very much for your help.
But there must be some way to delete an existing view ...
How do I change the following code to delete an existing view?

private void BtnBorrarVista_Click(object sender, EventArgs e)
{
try
{
if (MessageBox.Show("Está intentando borrar la Vista " + textBoxBorrarVista.Text + " ¿Está seguro de que quiere eliminarla?", "¡Atención!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
m_Server = new Server(cmbServerName.Text);
if (!string.IsNullOrWhiteSpace(cmbServerName.Text))
{
if (m_Server != null && !string.IsNullOrWhiteSpace(cmbDbName.Text))
{
Database myNewDatabase = m_Server.Databases[cmbDbName.Text];
if (myNewDatabase != null)
{
Microsoft.SqlServer.Management.Smo.View textView;
textView = new Microsoft.SqlServer.Management.Smo.View(myNewDatabase, textBoxBorrarVista.Text, "Sales");
if (textView != null)
{
textView.Drop();
MessageBox.Show("La Vista " + textBoxBorrarVista.Text + " se borró con éxito");
}
}
}
}
}
else
{
MessageBox.Show("Operación abortada. La Vista " + textBoxBorrarVista.Text + " no se ha borrado");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
Jefferson S. Motta6y ago
At Microsoft documentation their samples usually show creating and deleting. For production you just create, otherwise, you can't use it.
Isidro Padilla6y ago
Hello Jignesh Kumar first of all thank you very much for your help.
I have tried the modified code that you have provided me and the result is the same: do not delete the view if the view is not created previously, as is the case with my first code. But if I remove the creation from the view and just leave "textView.Drop (); it won't delete the existing view.

I think I did not explain myself well when presenting my query and what I want to do is not well understood.
1) What I want is to delete a view in a table from a database with SMO.

2) I rely on the microsoft example from the link:
https://docs.microsoft.com/ca-en/sql/relational-databases/server-management-objects-smo/tasks/creating-altering-and-removing-views? view=sql-server-2017&viewFallbackFrom=aps- pdw-2016-au7

3) The example of this link creates the view and immediately deletes it (I do not understand the usefulness of this example, possibly due to my little experience, but I think it helps little and confuses a lot)

4) What I try is to create a method that deletes the existing view in the database, in this way:
private void BtnBorrarVista_Click(object sender, EventArgs e)
{
try
{
if (MessageBox.Show("Está intentando borrar la Vista " + textBoxBorrarVista.Text + " ¿Está seguro de que quiere eliminarla?", "¡Atención!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
m_Server = new Server(cmbServerName.Text);
if (!string.IsNullOrWhiteSpace(cmbServerName.Text))
{
if (m_Server != null && !string.IsNullOrWhiteSpace(cmbDbName.Text))
{
      Database myNewDatabase = m_Server.Databases[cmbDbName.Text];
    if (myNewDatabase != null)
         {
             Microsoft.SqlServer.Management.Smo.View textView;
             textView = new Microsoft.SqlServer.Management.Smo.View(myNewDatabase, textBoxBorrarVista.Text, "Sales");
           if (textView != null)
              {
              textView.Drop();
               MessageBox.Show("La Vista " + textBoxBorrarVista.Text + " se borró con éxito");
               }
               }
             }
}
}
else
{
MessageBox.Show("Operación abortada. La vista " + textBoxBorrarVista.Text + " no se ha borrado");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
But I can't find a way to do it.
The name of the view, as you can see, I enter it in a textBox.
How can I get these, what am I doing wrong?
Jignesh Kumar6y ago
Hi,

Please change your view object creation with some other name like, I gave testView, you can give appropriate name,
private void BtnBorrarVista_Click(object sender, EventArgs e)
{
      try
      {
               if (MessageBox.Show("Está intentando borrar la Vista " + textBoxBorrarVista.Text + " ¿Está seguro de que quiere eliminarla?",                      "¡Atención!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
               {
                     m_Server = new Server(cmbServerName.Text);
                     if (!string.IsNullOrWhiteSpace(cmbServerName.Text))
                     {
                           if (m_Server != null && !string.IsNullOrWhiteSpace(cmbDbName.Text))
                           {
                                    Database myNewDatabase = m_Server.Databases[cmbDbName.Text];
                                    if (myNewDatabase != null)
                                    {
                                             Microsoft.SqlServer.Management.Smo.View testView;
                                             testView = new Microsoft.SqlServer.Management.Smo.View(myNewDatabase, textBoxBorrarVista.Text, "Sales");
                                             //=================Create view. Why? ===========================
                                             testView.TextHeader = "CREATE VIEW [Sales]." + textBoxBorrarVista.Text + " AS";
                                             testView.TextBody = "SELECT h.SalesOrderID, d.OrderQty FROM Sales.SalesOrderHeader AS h INNER JOIN                                                          Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID";
                                             //Create the view on the instance of SQL Server.
                                             testView.Create();
                                             MessageBox.Show("La vista de la 'Tabla' 'Sales' de " + cmbDbName.Text + " se creó satisfactoriamente ");
                                             //==============================================================
                                             //Checking view
                                             MessageBox.Show("Check view: " + view);
                                             if (testView != null)
                                             {
                                                   testView.Drop();
                                                   MessageBox.Show("La Vista " + textBoxBorrarVista.Text + " se borró con éxito");
                                             }
                                       }
                              }
                        }
                  }
                  else
                  {
                        MessageBox.Show("Operación abortada. La Vista " + textBoxBorrarVista.Text + " no se ha borrado");
                  }
            }
               catch (Exception ex)
            {
               MessageBox.Show(ex.Message.ToString());
         }
}