Ganesh S. Chatraband (Proffessional Blog): July 2013

Wednesday, 31 July 2013

How to use ControlParameter in asp.net

Example: Country-->State-->City selection in asp.net

Asp.net Page Source:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

#form1

{ height: 283px;

width: 218px; }

</style>

</head>

<body>

<asp:SqlDataSource ID="SqldataSourceCountry" runat="server"

ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>"

SelectCommand="SELECT [countryID], [countryName] FROM [tblCountry] ORDER BY [countryName]">

</asp:SqlDataSource>

<asp:SqlDataSource ID="SqlDataSourceState" runat="server"

ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>"

SelectCommand="SELECT [stateID], [stateName] FROM [tblState] WHERE ([countryID] = @countryID) ORDER BY [stateName]">

<asp:ControlParameter ControlID="cmbCountry" DefaultValue="0" Name="countryID"

PropertyName="SelectedValue" Type="Int32" />

</SelectParameters>

</asp:SqlDataSource>

<asp:SqlDataSource ID="SqlDataSourceCity" runat="server"

ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>"

SelectCommand="SELECT [cityId], [cityName] FROM [tblCity] WHERE ([stateID] = @stateID) ORDER BY [cityName]">

<asp:ControlParameter ControlID="cmbStates" DefaultValue="0" Name="stateID"

PropertyName="SelectedValue" Type="Int32" />

</SelectParameters>

</asp:SqlDataSource>

<br />Select Country:<br>

<asp:DropDownList ID="cmbCountry" runat="server"

DataSourceID="SqldataSourceCountry" DataTextField="countryName"

DataValueField="countryID" Height="18px" Width="169px" AutoPostBack="True">

</asp:DropDownList>

<br />

<br />Select State:<br />

<asp:DropDownList ID="cmbStates" runat="server"

DataSourceID="SqlDataSourceState" DataTextField="stateName"

DataValueField="stateID" Height="50px" Width="163px" AutoPostBack="True">

</asp:DropDownList>

<br />

<br />Select City <br />

<asp:DropDownList ID="cmbCity" runat="server" Height="33px" Width="167px"

DataSourceID="SqlDataSourceCity" DataTextField="cityName"

DataValueField="cityId">

</asp:DropDownList>

</form>

</body>

</html>

SQL Queries:

Create Country/State/City Tables as follows

CREATE TABLE [dbo].[tblCountry](

[countryID] [int] IDENTITY(1,1) NOT NULL,

[countryName] [varchar](50) NOT NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[tblState](

[stateID] [int] IDENTITY(1,1) NOT NULL,

[stateName] [varchar](50) NOT NULL,

[countryID] [int] NOT NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[tblCity](

[cityId] [int] IDENTITY(1,1) NOT NULL,

[cityName] [varchar](50) NOT NULL,

[stateID] [int] NOT NULL

) ON [PRIMARY]

Insert Data into tblCountry/State/City Tables

Friday, 26 July 2013

SQL Database Backup Restore Utility
VB.NET Code

Code :
    'Database resourecs
    Dim con As SqlConnection
    Dim cmd As SqlCommand
    Shared conn As New SqlConnection

#Region "DATABASE BACKUP"
    Public Function DatabaseBackup(ByVal ToDisk As String) As Boolean
        Try
            con = New SqlConnection(APPCONSTRING)
            cmd = New SqlCommand("backup database " & SQL_DBNAME & " to disk='" & ToDisk & ".bak'", con)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
            'MsgBox("Database Backup Successfull")
        Catch ex As SqlException
            MsgBox("SQlServer Exception :" & ex.Message)
            Dim frm As New FrmApplicationSettings
            frm.ShowDialog()
            Return False
        Catch ex As Exception
            MsgBox("Exception :" & ex.Message)
            Dim frm As New FrmApplicationSettings
            frm.ShowDialog()
            Return False
        Finally
            If Not (con Is Nothing) Then con.Dispose()
            con = Nothing
            If Not (cmd Is Nothing) Then cmd.Dispose()
        End Try
        Return True
    End Function
#End Region

#Region "DATABASE RESTORE"
    Public Function DatabaseRestore(ByVal FromDisk As String) As Boolean
        Try
            MsgBox(FromDisk)

            Dim sqlstr As String = "RESTORE DATABASE " & SQL_DBNAME & " " _
            & "FROM DISK='" & FromDisk & "'" _
            & " WITH RECOVERY"

            MsgBox(sqlstr)

            Dim CONSTR = "Persist Security Info=False;User ID=" & SQL_USER_ID & ";pwd=" & SQL_PASSWORD & ";Initial Catalog=master;Data Source=" & SQL_SERVER_NAME
            con = New SqlConnection(CONSTR)
            cmd = New SqlCommand(sqlstr, con)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
            MsgBox("Database restore Successfull", MsgBoxStyle.Information, MSG_TITLE)
        Catch ex As SqlException
            MsgBox("SQlServer Exception :" & ex.Message, MsgBoxStyle.Critical, MSG_TITLE)
            Return False
        Catch ex As Exception
            MsgBox("Exception :" & ex.Message, MsgBoxStyle.Critical, MSG_TITLE)
            Return False
        Finally
            If Not (con Is Nothing) Then con.Dispose()
            con = Nothing
            If Not (cmd Is Nothing) Then cmd.Dispose()
            cmd = Nothing
        End Try
        Return True
    End Function
#End Region