Backup / Restore And Unload database with asp.net

[JustRun]

Hi,

I want to delete all data from tables when the user click on the unload button, I dont wanna make about 50 Delete Command to unload the data from database. is there any way to do this, regarding that i have forign keys.

As for the Backup and Restore to the system database, I mean i want to allow the user to select the path of Backup, and also from where he want to make the Restore.


I think it could be done with uploading form that takes paths from the user but actually i dont know the SQL statement for backup

I use SQL SERVER 2000
ASP.NET 2
C#

Thanks

[Curtis Rutland]

What do you mean that you don't want to make 50 delete statements? Do you have 50 tables, or just 50 rows in one table? If it is the latter, one delete command will solve your problem.

[JustRun]

I have about 50 Tables

[JustRun]

Here is my code:

    SqlConnection cnn = new SqlConnection(DBconnection.GetConnection("LocalSqlServer"));
    SqlCommand cmd;
 
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            cmd = new SqlCommand("DELETE FROM Table1 DELETE FROM Table2 DELETE FROM Table3 ...... ", cnn);
            cnn.Open();
 
            if (cmd.ExecuteNonQuery() == 1)
            {
                lblErr.Text = "You have successfully unload the system";
                //Response.Redirect("../Default.aspx");
            }
            else
            {
                lblErr.Text = "An Error have been occured";
            }
        }
        catch (Exception ex)
        {
            lblErr.Text = ex.Message.ToString();
        }
 
        finally
        {
            if (cnn != null) cnn.Close();
        }
    }
 

This code works fine and it affects the specified tables, But The browser display the message of Else "An Error have been occured"
I dont know why

[Curtis Rutland]

I think that is because ExecuteNonQuery returns the number of rows affected by your statement.

[mldisibio]

Confirming what insertAlias said: ExecuteNonQuery returns number of rows affected, and in the case of your query, only the rows deleted from the last table.

Here are some recommendations, but you need to pull out SqlServer Books online and look this syntax up for yourself: allowing users to delete 50 tables en masse with a click of a web page is asking for trouble if you don't understand the syntax yourself:

1. Put the DELETE statement into a stored procedure. The web page will call only one stored procedure, which you can change as your database requirements change;

2. Your StoredProcedure (SP) can return a success or failure return value. It can return specific errors. It can decide whether to proceed, cancel or ROLLBACK if a table delete fails.

3. If you are just wiping out tables and do not care about ROLLBACK in case of failure, use TRUNCATE TABLE not DELETE, in order to efficiently not over use log files.

4. Within your SP, you can either hardcode your 50 TRUNCATE statements, which in your case you probably should do to avoid errors in using syntax you do not understand. This way you can also verify Foreign Key integrity. However, you could also write a cursor that gets all the table names from the system catalog (this catalog tables/views are different between SQL 2000 and 2005). You can either generate sql statements and execute them, or write a procedure that parameterizes the table names. There are plenty of examples of this on SQL websites. Here is a starter, but you can find scripts written by expert DBA's as well: How to List All Tables

4a. You could, though not recommended, do the same on the server side code: execute a statement which retrieves the table names in question, then dynamically create the DELETE string. However, stored procedure would be the best practice.

5. If you use a cursor, you need to make sure you are deleting only the tables you want, not system tables, not other tables or databases. How are you handling concurrent users?

As far as backup/restore syntax, assuming your web user has permission to execute these statements, and that they are doing so in the "master" db and there are no connections to the database in question, then you can do this via a sql statement. Simply study and test the syntax for BACKUP and RESTORE:
Transact SQL 2000 BACKUP

[JustRun]

Well, I have tried the Stored Procedure but it didnt work for me, any way i managed Unloading the system with Delete command, i know its a bad way but at least it works.

How about the Backup and Restore?
I have no idea about the sql command of them

[JustRun]

Any Reply, I'm stuch with the Internet search :(

[mldisibio]

The MSDN link I posted not only gives the syntax, but has examples at the bottom of the page.

In MSDN, you will see the contents on the left panel. You can click on RESTORE to get the same syntax explanation and examples.

You would use the example sql just like your DELETE statement above. They are sql text statements that would be executed from a SqlCommand.ExecuteNonQuery method directly, or placed in a stored procedure whose name is executed from a SqlCommand.ExecuteNonQuery method.

[JustRun]

Hi,

I knew how to make the Backup and Restore through SQL statement,
What I need to know is

in the backup page.aspx --> Choose the path of backup from a "Save as window", take this path and pass it to the SQL statement.
The Question is --> how to take the path that user had choose?

and the same in the Restore