Delete All Tables MSSQL

Share |

While working on ASP.Net application, I came across a situation where I had to delete all the Tables of the MSSQL 2008 database, and it was really painful process. 

In this case I had to delete all the SPs and all views as well. As I started searching like "Truncate database Sql 2008" or "Delete all tables in MSSQL" I came across two things and it worked for me.

(I am really not sure whether it works in all the cases but for Deleting all tables in SQL, this worked for me.) 

Firstly we need to remove all indexs from SQL table, I found following code when I Google the term 

DECLARE @indexName NVARCHAR(128)
DECLARE @dropIndexSql NVARCHAR(4000)

DECLARE tableIndexes CURSOR FOR
SELECT name FROM sysindexes
WHERE id = OBJECT_ID(N'tableName') AND
  indid > 0 AND indid < 255 AND
  INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY indid DESC

OPEN tableIndexes
FETCH NEXT FROM tableIndexes INTO @indexName
WHILE @@fetch_status = 0
BEGIN
  SET @dropIndexSql = N'DROP INDEX tableName.' + @indexName
  EXEC sp_executesql @dropIndexSql

  FETCH NEXT FROM tableIndexes INTO @indexName
END

CLOSE tableIndexes
DEALLOCATE tableIndexes

After this we need to delete all the tables in SQL, for this I used following SP

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

It seems that this is a HIDDEN stored procedure in MSSQL.

Read more...

Cross Page PostBack In Asp.Net

Share |

In web development, many times we come across following situation

We need to redirect to new page, but we also need some data which belongs to previous page, we might need to display the data on new page. So what things we normally do are,

eighter keep the data in session, or in hidden variable, or put data in application variable, or again run the code to get same data and bind it to similar kind of control.

I think this is something which we can avoid by simply applying logic of Cross Page Postback, where we can directly identify the controls whose value we need to display on new page.

E.g.

step1--> We have Grid View on say PAGE1.aspx and we redirect to PAGE2.aspx.

step2--> On PAGE2.aspx we need same Grid View to Display. For this we will probably use traditional ways to maintain the grid.

Now what if I say we can directly access grid which is already filled with data, and simply put the same grid on PAGE2.aspx. This sounds very simple. And it is very simple. For better understanding just check out following code which I have written on PAGE2.aspx

<br /> protected void Page_Load(object sender, EventArgs e)<br /> {<br /> if (PreviousPage.IsCrossPagePostBack)<br /> {<br /> TextBox tb1 = (TextBox)PreviousPage.FindControl("_tb1");<br /> TextBox tb2 = (TextBox)PreviousPage.FindControl("_tb2");<br /> GridView Gv = (GridView)PreviousPage.FindControl("GridView1");<br /> int sum = Int32.Parse(tb1.Text) + Int32.Parse(tb2.Text);<br /> _div.Controls.Add(Gv);<br /> Response.Write(sum.ToString());<br /> }<br /> <br /> }<br />

It looks very easy to use the code. You can DOWNLOAD the demo code, explained below
In above code check out the If condition where we have check whether it is a CrossPagePostBack or not.
We have following things in our web application.

• Page1.aspx (with cs code)
• Page2.aspx (with cs code)

Lets build Page1.aspx first, we have following HTML in Page1.aspx file. 

<br /> <form id="form1" runat="server"> <div> <asp:TextBox runat="server" ID="_tb1"></asp:TextBox><br /> +<br /> <asp:TextBox runat="server" ID="_tb2"></asp:TextBox><br /> <br /> <asp:Button runat="server" ID="_button" PostBackUrl="~/Page2.aspx" Text=" = " /><br /> <asp:GridView ID="GridView1" runat="server"><br /> </asp:GridView><br /> </div> </form>

check out the PostBackUrl of button. which is set to Page2.aspx, we just need to do this in our code so that Page1 will be accessible in Page2.aspx
which looks like following page

Now we will write code in Page1.aspx.cs file which is as below, you can generally copy paste the code in your files.

<br /> protected void Page_Load(object sender, EventArgs e)<br /> {<br /> GridView1.DataSource = _sampleData;<br /> GridView1.DataBind();<br /> <br /> }<br /> public DataTable _sampleData<br /> {<br /> get<br /> {<br /> DataTable dt = (DataTable)Session["DataTable"];<br /> if (dt == null)<br /> {<br /> dt = new DataTable();<br /> dt.Columns.Add(new DataColumn("Contact Name", typeof(string)));<br /> dt.Columns.Add(new DataColumn("Company Name", typeof(string)));<br /> dt.Columns.Add(new DataColumn("City", typeof(string)));<br /> dt.Columns.Add(new DataColumn("Country", typeof(string)));<br /> dt.Columns.Add(new DataColumn("rowNumber", typeof(int)));<br /> <br /> dt.Rows.Add(new object[] { "Maria Anders", "Alfreds Futterkiste", "Berlin", "Germany", 0 });<br /> dt.Rows.Add(new object[] { "Ana Trujillo", "Emparedados y helados ", "México D.F.", "Mexico", 1 });<br /> dt.Rows.Add(new object[] { "Antonio Moreno", "Antonio Moreno Taquería", "México D.F.", "Mexico", 2 });<br /> dt.Rows.Add(new object[] { "Thomas Hardy", "Around the Horn", "London", "UK", 3 });<br /> dt.Rows.Add(new object[] { "Christina Berglund", "Berglunds snabbköp", "Luleå", "Sweden", 4 });<br /> dt.Rows.Add(new object[] { "Hanna Moos", "Delikatessen", "Mannheim", "Germany", 5 });<br /> dt.Rows.Add(new object[] { "Blauer See", "Citeaux Blondesddsl ", "Strasbourg", "France", 6 });<br /> dt.Rows.Add(new object[] { "Frédérique", "Bólido Comidas preparadas", "Madrid", "Spain", 7 });<br /> dt.Rows.Add(new object[] { "Martín Sommer", "Lebihan", "Bon app", "France", 8 });<br /> dt.Rows.Add(new object[] { "Laurence", "Lincoln Bottom-Dollar Markets", "Marseille", "France", 9 });<br /> dt.Rows.Add(new object[] { "Elizabeth", "père et fils", "Tsawassen", "Canad", 10 });<br /> dt.Rows.Add(new object[] { "Aria Cruz", "Familia Arquibaldo Sao", "Paulo", "Brazil", 11 });<br /> dt.Rows.Add(new object[] { "Diego Roel", "FISSA Fabrica Inter. Salchichas S.A.", "Madrid", "Spain", 12 });<br /> dt.Rows.Add(new object[] { "Martine", "Rancé Folies gourmandes", "Lille", "France", 13 });<br /> dt.Rows.Add(new object[] { "Maria", "Larsson Folk och fä HB", "Bräcke", "Sweden", 14 });<br /> dt.Rows.Add(new object[] { "Peter", "Franken Frankenversand", "München", "Germany", 15 });<br /> dt.Rows.Add(new object[] { "Carine", "Schmitt France restauration", "Nantes", "France", 16 });<br /> dt.Rows.Add(new object[] { "Paolo", "Accorti Franchi S.p.A.", "Torino", "Italy", 17 });<br /> dt.Rows.Add(new object[] { "Lino", "Rodriguez Furia Bacalhau e Frutos do Mar", "Lisboa", "Portugal", 18 });<br /> dt.Rows.Add(new object[] { "Eduardo", "Saavedra Galería del gastrónomo", "Barcelona", "Spain", 19 });<br /> dt.Rows.Add(new object[] { "José", "Pedro Freyre Godos Cocina Típica", "Sevilla", "Spain", 20 });<br /> <br /> Session["DataTable"] = dt;<br /> }<br /> return dt;<br /> }<br /> set<br /> {<br /> Session["DataTable"] = value;<br /> }<br /> <br /> }<br />

This is what we have to do for Page1.aspx, we are done with our code. In Page1.aspx we simple have two textboxes and one Grid View control which we bind to some sample table. In Page2.aspx we Just make Grid View Object and set the reference of Page1's Grid View to new gird view thats it. If you observe the code written for Page2.aspx, we don't retrieve the data from any where and bind it to Grid View, we simple set the reference. This makes code execution also easy, and in similar way we can always pick some random controls which we need to display on some other page. We can also have many links referring to different pages depending on the requirement.

Download Demo Code here --> CrossPagePostback.zip

Read more...