468,103 Members | 1,337 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,103 developers. It's quick & easy.

Backup / Restore And Unload database with asp.net

JustRun
127 100+
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
Sep 23 '08 #1
9 2552
Curtis Rutland
3,256 Expert 2GB
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.
Sep 23 '08 #2
JustRun
127 100+
I have about 50 Tables
Sep 23 '08 #3
JustRun
127 100+
Here is my code:
Expand|Select|Wrap|Line Numbers
  1.     SqlConnection cnn = new SqlConnection(DBconnection.GetConnection("LocalSqlServer"));
  2.     SqlCommand cmd;
  3.  
  4.     protected void Page_Load(object sender, EventArgs e)
  5.     {
  6.         try
  7.         {
  8.             cmd = new SqlCommand("DELETE FROM Table1 DELETE FROM Table2 DELETE FROM Table3 ...... ", cnn);
  9.             cnn.Open();
  10.  
  11.             if (cmd.ExecuteNonQuery() == 1)
  12.             {
  13.                 lblErr.Text = "You have successfully unload the system";
  14.                 //Response.Redirect("../Default.aspx");
  15.             }
  16.             else
  17.             {
  18.                 lblErr.Text = "An Error have been occured";
  19.             }
  20.         }
  21.         catch (Exception ex)
  22.         {
  23.             lblErr.Text = ex.Message.ToString();
  24.         }
  25.  
  26.         finally
  27.         {
  28.             if (cnn != null) cnn.Close();
  29.         }
  30.     }
  31.  
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
Sep 23 '08 #4
Curtis Rutland
3,256 Expert 2GB
I think that is because ExecuteNonQuery returns the number of rows affected by your statement.
Sep 23 '08 #5
mldisibio
190 Expert 100+
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
Sep 23 '08 #6
JustRun
127 100+
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
Sep 23 '08 #7
JustRun
127 100+
Any Reply, I'm stuch with the Internet search :(
Sep 24 '08 #8
mldisibio
190 Expert 100+
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.
Sep 24 '08 #9
JustRun
127 100+
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
Oct 11 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by James | last post: by
reply views Thread by Mirko Slavko | last post: by
3 posts views Thread by Tim Morrison | last post: by
6 posts views Thread by Eric Herber | last post: by
4 posts views Thread by Hardy | last post: by
reply views Thread by raj.raghavan | last post: by
10 posts views Thread by Konstantin Andreev | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.