ASP.NET AND SQL :Simple Tips And Tricks

ASP.NET - How To Display Animated .GIF On Postback

January 12, 2018

When designing an interactive web page, it is important for you to let the user know that an action initiated by him/her is in progress.One way to do this is to display an animated .GIF on postback and hide it after the page trip to the server. It would also serve well to disable further inputs from the user until the postback is completed.

Below is a simple ASP.NET page with javascript to display an animated .GIF and disable the button till theirs a response from the server .

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="About.aspx.cs" Inherits="About" %>

<!DOCTYPE html>

<head>

<title>Login Form</title>

window.onbeforeunload = function () {

//Disable the button

document.getElementById('<%=btnContinue.ClientID %>').disabled = true;

//show the animated image.

document.getElementById('<%=imgLoader.ClientID%>').style.visibility = 'visible';

}

</script>

</head>

<body >

<tr>

<asp:Label ID="Label1" runat="server" Text="Username :"></asp:Label>

</td>

<asp:TextBox ID="txtUsername" placeholder="Username" runat="server"></asp:TextBox>

</td>

</td>

</tr>

<tr>

<asp:Label ID="Label2" runat="server" Text="Password :"></asp:Label>

</td>

<asp:TextBox ID="txtPassword" placeholder="Password" runat="server" TextMode="Password"></asp:TextBox>

</td>

</td>

</tr>

<tr>

</td>

<asp:Button ID="btnContinue" runat="server" Text="Continue"

onclick="btnContinue_Click"

Width="287px" />

</td>

</td>

</tr>

</table>

<p>

<asp:Image ID="imgLoader" runat="server" Height="52px"

ImageUrl="~/img/ajax-loader.gif" style="visibility:hidden" Width="62px" />

</p>

</form>

</body>

</html>

Page before postback

Page during post back. *Note that the button was disabled during postback

SQL - How To Backup All Databases In MS SQL Server Instance

January 12, 2018

SQL - How To Backup All Databases In MS SQL Server Instance

If you would like to backup all the databases in an instance of MS SQL server , you can run the following script in the query window of that instance . Remember to change the path 'C:\Backup\' to the path on your own system .

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory

SET @path = 'C:\Backup\'

-- specify filename format

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR READ_ONLY FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

ASP.NET - How To Add Serial Number Column To A GridView

May 25, 2017

To add a serial number column to a gridview , you would add the following markup to the source code of the gridview

<asp:TemplateField HeaderText="S/N">

<asp:Label ID="lblRowNumber" Text='<%# Container.DataItemIndex+1 %>' runat="server" />

</ItemTemplate>

</asp:TemplateField>

Below is what the markup for the gridview would look like .The aspect that handles the serial number generation is highlighted for emphasis.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataKeyNames="Employee_ID" DataSourceID="SqlDataSource1"

ForeColor="#333333" Width="341px">

<asp:TemplateField HeaderText="S/N">

<asp:Label ID="lblRowNumber" Text='<%# Container.DataItemIndex+1 %>' runat="server" />

</ItemTemplate>

</asp:TemplateField>

<asp:BoundField DataField="Employee_ID" HeaderText="Employee_ID"

InsertVisible="False" ReadOnly="True" SortExpression="Employee_ID" />

<asp:BoundField DataField="FirstName" HeaderText="FirstName"

SortExpression="FirstName" />

<asp:BoundField DataField="Salary" HeaderText="Salary"

SortExpression="Salary" />

</Columns>

</asp:GridView>

SQL-Search For A Column Name In All Tables In a Database

February 22, 2017

SQL-Search For A Column Name In All Tables In a Database

If you would like to search all tables in a database for a particular column name, here's a sample query for that:

SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%EmployeeNo%'
ORDER BY TABLE_NAME

Here the column name been searched for is -'EmployeeNo'

SQL-Search Stored Procedures For A Text

February 22, 2017

To search the all the stored procedures in a database for a given text :
Say you want to find all the stored procedures that have the text-'EmployeeNo' , here's how to go about it .

SELECT OBJECT_NAME(id) FROM SYSCOMMENTS WHERE [text] LIKE '%EmployeeNo%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id)

SQL-How to get value of updated column

February 02, 2017

There are times you would like to update a column in a table and return the value of the updated column . Here's how to do it :

UPDATE Employee SET Salary = Salary + 1 OUTPUT INSERTED.Salary WHERE Employee_ID = 2

The key here for displaying the value is 'OUTPUT INSERTED.Salary'

SQL-How to list out all tables in SQL server database

January 24, 2017

To list out all tables in SQL server database using SQL query

SELECT TABLE_NAME FROM <databasename>.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'

example using Northwind Database:

SELECT TABLE_NAME FROM Northwind.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'