COMPLETE ASP NET TUTORIAL

Tuesday, February 11, 2014

Drop all tables, stored procedure, views and triggers

Sometimes, there is a case, when we need to remove all tables, stored procedure, views and triggers completely from the database. If you have around 100 tables, stored procedure and views in your database, to remove these, completely from database became a tedious task. In this article, I would like to share the script by which you can remove tables, stored procedure, views and triggers completely from database.
Remove all Tables
 -- drop all user defined tables
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" 
Remove all User-defined Stored Procedures
 -- drop all user defined stored procedures
Declare @procName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'p' 
Open cur 
Fetch Next From cur Into @procName 
While @@fetch_status = 0 
Begin 
 Exec('drop procedure ' + @procName) 
 Fetch Next From cur Into @procName 
End
Close cur 
Deallocate cur 
Remove all Views
 -- drop all user defined views
Declare @viewName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'v' 
Open cur 
Fetch Next From cur Into @viewName 
While @@fetch_status = 0 
Begin 
 Exec('drop view ' + @viewName) 
 Fetch Next From cur Into @viewName 
End
Close cur 
Deallocate cur 
Remove all Triggers
 -- drop all user defined triggers
Declare @trgName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'tr' 
Open cur 
Fetch Next From cur Into @trgName 
While @@fetch_status = 0 
Begin 
 Exec('drop trigger ' + @trgName) 
 Fetch Next From cur Into @trgName 
End
Close cur 
Deallocate cur

Monday, February 10, 2014

Asp.net c# Delegates

Uc1.ascx
----------
<%@ Control Language="C#" AutoEventWireup="true" CodeFile="Uc1.ascx.cs" Inherits="Controls_Uc1" %>
This is Uc1
<asp:Button ID="btnButton1" runat="server" Text="Click" OnClick="btnButton1_Click" />
<asp:DropDownList ID="drp1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="drp1_OnSelectedIndexChanged">
<asp:ListItem>One</asp:ListItem>
<asp:ListItem>Two</asp:ListItem>
<asp:ListItem>Three</asp:ListItem>
<asp:ListItem>Four</asp:ListItem>
</asp:DropDownList>
Uc1.ascx.cs
-------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Controls_Uc1 : System.Web.UI.UserControl
{
    #region DECLRATIONS
    public delegate void MyCustomHandler(object sender, EventArgs e);  //This is for button click
    public event MyCustomHandler SomethingClicked;
    
    public delegate void MyCustomSelectedHandler(object sender, EventArgs e); //This is for Dropdown selected 
    public event MyCustomSelectedHandler SomethingSelected;
    #endregion
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnButton1_Click(object sender, EventArgs e)
    {
        //tell our parent page which is listening for the event that something was clicked
        //this null check and method invoke is the equivalent of raise event in VB
        if (SomethingClicked != null)
        {
            SomethingClicked(sender, e);
        }
        //do some other work specific to this button in the user control
    }
    protected void drp1_OnSelectedIndexChanged(object sender, EventArgs e)
    {
        if (SomethingSelected != null)
        {
            SomethingSelected(sender, e);
        }
    }
}

Default3.aspx
---------------
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>
<%@ Register Src="~/Controls/Uc1.ascx" TagName="Users" TagPrefix="Magic" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:Button ID="btn1" runat="server" Text="Load Control1"  />
 
    <table>
    <tr>
    <td>
    <asp:PlaceHolder ID="phModule" runat="server"></asp:PlaceHolder>
    </td>
    </tr>
    </table>
    
    </div>
    </form>
</body>
</html>
Default3.aspx.cs
--------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Default3 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        phModule.Controls.Clear();
        Control myUserControl = (Control)Page.LoadControl("~/Controls/Uc1.ascx");
        // Place web user control to place holder control
        if (myUserControl != null)
        {
            Button btnButton1 = (Button)myUserControl.FindControl("btnButton1");
            btnButton1.Click += new EventHandler(CustomerEmail1_RestoreModalDialog);
            DropDownList drp1 = (DropDownList)myUserControl.FindControl("drp1");
            drp1.SelectedIndexChanged += new EventHandler(drp1_SelectedIndexChanged);
        }
        phModule.Controls.Add(myUserControl);
    }
    protected void CustomerEmail1_RestoreModalDialog(object senders, EventArgs e)
    {
        ScriptManager.RegisterClientScriptBlock(this, GetType(), "clientkey", "<script>alert('Button1 Clicked');</script>", false);
    }
    protected void drp1_SelectedIndexChanged(object sender, EventArgs e)
    {
        ScriptManager.RegisterClientScriptBlock(this, GetType(), "clientkey", "<script>alert('Make Parent Pages Modal Popup visible when UserControls dropdownlist posted back');</script>", false);
    }
}

Useful Delegates Example in C# and ASP.NET

Create a UserControl named Uc1.ascx (i.e. I have created insided Folder named Controls)

Step 1: Declare a Delegate and Events for it. In my case,i have created for button click and dropdownlist

Step 2: In .aspx page reference it like this :

<%@ Register Src="~/Controls/Uc1.ascx" TagName="Users" TagPrefix="Test" %>

Full Code :

Uc1.ascx
------------
<%@ Control Language="C#" AutoEventWireup="true" CodeFile="Uc1.ascx.cs" Inherits="Controls_Uc1" %>
This is User control1
<asp:Button ID="btnButton1" runat="server" Text="Click" OnClick="btnButton1_Click" />
<asp:DropDownList ID="drp1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="drp1_OnSelectedIndexChanged">
<asp:ListItem>One</asp:ListItem>
<asp:ListItem>Two</asp:ListItem>
<asp:ListItem>Three</asp:ListItem>
<asp:ListItem>Four</asp:ListItem>
</asp:DropDownList>
Uc1.ascx.cs
------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Controls_Uc1 : System.Web.UI.UserControl
{
    #region DECLRATIONS
    public delegate void MyCustomHandler(object sender, EventArgs e);  //This is for button click
    public event MyCustomHandler SomethingClicked;
    
    public delegate void MyCustomSelectedHandler(object sender, EventArgs e); //This is for Dropdown selected 
    public event MyCustomSelectedHandler SomethingSelected;
    #endregion
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnButton1_Click(object sender, EventArgs e)
    {
        //tell our parent page which is listening for the event that something was clicked
        //this null check and method invoke is the equivalent of raise event in VB
        if (SomethingClicked != null)
        {
            SomethingClicked(sender, e);
        }
        //do some other work specific to this button in the user control
    }
    protected void drp1_OnSelectedIndexChanged(object sender, EventArgs e)
    {
        if (SomethingSelected != null)
        {
            SomethingSelected(sender, e);
        }
    }
}

Default4.aspx

---------------------

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default4.aspx.cs" Inherits="Default4" %>
<%@ Register Src="~/Controls/Uc1.ascx" TagName="Users" TagPrefix="Test" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
     <asp:Button ID="btn1" runat="server" Text="Load UserControl1"  />
    <br />
    <Test:Users ID="Users1" runat="server"></Test:Users>
    </div>
    </form>
</body>
</html>

Default4.aspx.cs

----------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Default4 : System.Web.UI.Page
{
    protected override void OnInit(EventArgs e)
    {
        base.OnInit(e);
        Users1.SomethingClicked += new Controls_Uc1.MyCustomHandler(Users1_ButtonClicked); //Button Click
        Users1.SomethingSelected += new Controls_Uc1.MyCustomSelectedHandler(Users1_DropDownSelected); //DropDownList
    }
    protected void Users1_ButtonClicked(object senders, EventArgs e)
    {
        ScriptManager.RegisterClientScriptBlock(this, GetType(), "clientkey", "<script>alert('Button1 Clicked');</script>", false);
    }
    protected void Users1_DropDownSelected(object senders, EventArgs e)
    {
        ScriptManager.RegisterClientScriptBlock(this, GetType(), "clientkey", "<script>alert('Make Parent Pages Modal Popup visible when UserControls dropdownlist posted back');</script>", false);
     
    }
    protected void Page_Load(object sender, EventArgs e)
    {
    }
}

ASP.NET: Loading User Control Dynamically in C#

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" Text="Load UserControl1" CommandName="UserControl1" OnCommand="btn1_Click" />
        <asp:Button ID="Button2" runat="server" Text="Load UserControl2" CommandName="UserControl2" OnCommand="btn1_Click" />
        <table>
            <tr>
                <td>
                    <asp:PlaceHolder ID="phModule" runat="server"></asp:PlaceHolder>
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Default5 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Session["UserControl"] = "UserControl1";
            string ControlName = Convert.ToString(Session["UserControl"]);
            LoadControls(ControlName);
        }
        else
        {
            if (Session["UserControl"] != null)
            {
                string ControlName = Convert.ToString(Session["UserControl"]);
                LoadControls(ControlName);
            }
        }
    }
    private void LoadControls(string ControlName)
    {
        string CommandName = ControlName;
        switch (CommandName)
        {
            case "UserControl1":
                phModule.Controls.Clear();
                Control myUserControl = (Control)Page.LoadControl("~/Controls/Uc1.ascx");
                // Place web user control to place holder control
                phModule.Controls.Add(myUserControl);
                break;
            case "UserControl2":
                phModule.Controls.Clear();
                myUserControl = (Control)Page.LoadControl("~/Controls/Uc2.ascx");
                // Place web user control to place holder control
                phModule.Controls.Add(myUserControl);
                break;
        }
    }
    protected void btn1_Click(object sender, CommandEventArgs e)
    {
       
        string CommandName = e.CommandName;
        switch (CommandName)
        {
            case "UserControl1":
                phModule.Controls.Clear();
                Control myUserControl = (Control)Page.LoadControl("~/Controls/Uc1.ascx");
                // Place web user control to place holder control
                phModule.Controls.Add(myUserControl);
                break;
            case "UserControl2":
                phModule.Controls.Clear();
                myUserControl = (Control)Page.LoadControl("~/Controls/Uc2.ascx");
                // Place web user control to place holder control
                phModule.Controls.Add(myUserControl);
                break;
        }
        Session["UserControl"] = CommandName;
    }
    protected void btn1_Click(object sender, EventArgs e)
    {
    }
}

Saturday, February 8, 2014

Creating Dataset Dynamically in c#

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
     <asp:GridView ID="GridVwRowColorchange" runat="server" AutoGenerateColumns="False"
                Font-Names="Verdana" PageSize="5" Width="75%"
                BorderColor="#CCCCCC" BorderStyle="Solid"
                BorderWidth="1px" onrowdatabound="GridVwRowColorchange_RowDataBound" >
                <AlternatingRowStyle BackColor="#BFE4FF" />
                <PagerStyle BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
                <HeaderStyle Height="30px" BackColor="#6DC2FF" Font-Size="15px" BorderColor="#CCCCCC"
                    BorderStyle="Solid" BorderWidth="1px" />
                <RowStyle Height="20px" Font-Size="13px" BorderColor="#CCCCCC" BorderStyle="Solid"
                    BorderWidth="1px" />
                <Columns>
                    <asp:BoundField DataField="Emp_Name" HeaderText="Employee Name"  />
                    <asp:BoundField DataField="Emp_id" HeaderText="Employee ID"  />
                    <asp:BoundField DataField="Emp_job" HeaderText="Job title"  />
                    <asp:BoundField DataField="Emp_Dep" HeaderText="Department"  />
                </Columns>
            </asp:GridView>
    </div>
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
public partial class Default3 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadData();
        }
    }
    private void LoadData()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Emp_Name");
        dt.Columns.Add("Emp_id");
        dt.Columns.Add("Emp_job");
        dt.Columns.Add("Emp_Dep");
        DataRow rw1 = dt.NewRow();
        rw1["Emp_Name"] = "Narain Sidharth";
        rw1["Emp_id"] = "2020";
        rw1["Emp_job"] = "SOftware Engineer";
        rw1["Emp_Dep"] = "IT";
        dt.Rows.Add(rw1);
        DataRow rw2 = dt.NewRow();
        rw2["Emp_Name"] = "Prakalathan";
        rw2["Emp_id"] = "1978";
        rw2["Emp_job"] = "System Engineer";
        rw2["Emp_Dep"] = "IT";
        dt.Rows.Add(rw2);
        DataRow rw3 = dt.NewRow();
        rw3["Emp_Name"] = "Mathu kumar";
        rw3["Emp_id"] = "2700";
        rw3["Emp_job"] = "Support Enginner";
        rw3["Emp_Dep"] = "IT";
        dt.Rows.Add(rw3);
        DataRow rw4 = dt.NewRow();
        rw4["Emp_Name"] = "Arvind";
        rw4["Emp_id"] = "4678";
        rw4["Emp_job"] = "Sr Software Engineer";
        rw4["Emp_Dep"] = "IT";
        dt.Rows.Add(rw4);
        DataRow rw5 = dt.NewRow();
        rw5["Emp_Name"] = "Raja ram";
        rw5["Emp_id"] = "2300";
        rw5["Emp_job"] = "Test Engineer";
        rw5["Emp_Dep"] = "IT";
        dt.Rows.Add(rw5);
        GridVwRowColorchange.DataSource = dt;
        GridVwRowColorchange.DataBind();
    }
    protected void GridVwRowColorchange_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Attributes.Add("onmouseover", "self.MouseOverOldColor=this.style.backgroundColor;this.style.backgroundColor='#C0C0C0'");
            e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=self.MouseOverOldColor");
        }
    }
}

Pages

COMPLETE ASP NET TUTORIAL

Tuesday, February 11, 2014

Drop all tables, stored procedure, views and triggers

Remove all Tables

Remove all User-defined Stored Procedures

Remove all Views

Remove all Triggers

Monday, February 10, 2014

Asp.net c# Delegates

Useful Delegates Example in C# and ASP.NET

ASP.NET: Loading User Control Dynamically in C#

Saturday, February 8, 2014

Creating Dataset Dynamically in c#

Blogger templates

Blogger news

Blogroll

Blog Archive

Labels