Update Table using JOIN in SQL Server

Update a table value from another Table using JOIN Query in SQL Server

Code :
    UPDATE
        A
    SET
        A.Colx = B.Coly
    FROM
        Table1 A
    JOIN
        Table2 B ON A.col1 = B.col5
    WHERE
        A.Col3 = 'xyz'

Convert Text to XML Data in SQL Server.

Convert Text to XML Data in SQL Server.

In the following Stored Procedure we can Use XML Data as Follows.
Send XML Data to Stored Procedure as "Text" Data Type.
In SP Convert this "Text" (XML Text)  to "XML" Data
Read and Fetch the XML Data into Table.
Use the Cursor to Traverse through Data.

Code :
/**************************************************************************
 
-----------------------  
Developers Note   
-----------------------  
Object          : StoredProcedure [dbo].[canon_UpdateValidUserFromXML]      
Script Date     : 19/11/2012   
Developed By    : Rohit Shram  
Used For        : Update Valid  Users 
Implementaion   :   
    SELECT *  FROM [USER] where ID in ( 80309 ,80310)
    GO
    UpdateValidUserFromXML
    '<UsersList>
        <Users>
            <ID>80309</ID>
            <UserName>KARL TYLER</UserName>
            <Email>CRAIG.WASHINGTON80309@testmail.com</Email>
        </Users>
        <Users>
            <ID>80310</ID>
            <UserName>CHAD MARTIN</UserName>
            <Email>LEWIS.CUNNINGHAM80310@testmail.com</Email>
        </Users>
    </UsersList>',
    'File1234',
    80311
    GO
    SELECT *  FROM [USER] where ID in ( 80309 ,80310)

*************************************************************************************/  

CREATE PROCEDURE [dbo].[UpdateValidUserFromXML] 

    @UsersXml TEXT  
--'<UsersList><Users><ID>12</ID><UserName>KAYR</UserName><Email>CRAI@test.com</Email></Users></UsersList>' 
AS  
BEGIN  
    SET NOCOUNT ON;  
    
    BEGIN TRANSACTION Update
    
    BEGIN TRY
    
    DECLARE @idoc INT
    DECLARE @ID INT                  
    DECLARE @UserName NVARCHAR(500)                
    DECLARE @Email NVARCHAR(500)                
 
    -- Used to Keep ErrorList for all Users Data as Table
    DECLARE @UsersErrorList TABLE                  
    (                  
        ID INT,                 
        Name NVARCHAR(500),                
        Email NVARCHAR(500),                 
        Error NVARCHAR(MAX)       
    ) 
 
    -- Used to Keep XML Data as Table
    DECLARE @UsersList TABLE                  
    (                  
        ID INT,                 
        UserName NVARCHAR(500),                
        Email NVARCHAR(500)                
    ) 

    -- Prepare XML Doc
    EXEC sp_xml_preparedocument @idoc OUTPUT, @UsersXml                  
          
    -- Insert into Table Variable From XML
    INSERT @UsersList                
    (                 
        ID,                
        UserName,                 
        Email                
    )                  
    SELECT                   
        ID,                
        UserName,                 
        Email               
       
    FROM                  
    OPENXML (@idoc, '/UsersList/Users')                  
    WITH                 
    (                  
        ID INT 'ID',                 
        UserName NVARCHAR(500) 'UserName',                
        Email NVARCHAR(500) 'Email'               
    )                  
           
    -- Remove XML Do after getting Data
    EXEC sp_xml_removedocument @idoc           
   
    -- Declare Cursor
    DECLARE Cursor CURSOR FOR                  
    SELECT                  
    ID,                
    UserName,                 
    Email                
    FROM @UsersList                   
         
    -- Open Cursor
    OPEN Cursor                  
         
    -- Fetch Cursor
    FETCH NEXT FROM Cursor INTO                   
    @ID,                  
    @UserName,                  
    @Email       
         
    -- Traverse through Data                
    WHILE @@FETCH_STATUS = 0                
    BEGIN  
  
        -- Update User's Name and Email    
        UPDATE [USER] 
        SET Email = @Email, UserName = @UserName 
        WHERE ID = @ID 
  
        -- Fetch Cursor
        FETCH NEXT FROM Cursor INTO                   
        @ID,                  
        @UserName,                  
        @Email                 
                  
    END  -- WHILE @@FETCH_STATUS = 0                
                 
        CLOSE Cursor                  
        DEALLOCATE Cursor 
 
    END TRY
    BEGIN CATCH

        -- Rollback Transaction If any Transaction Occured
        IF @@ROWCOUNT > 0
            ROLLBACK TRANSACTION Update
  
        -- Insert Into Temp table to Keep Error Details for all Users
        INSERT INTO @UsersErrorList
        VALUES ( @ID , @UserName , @Email , ERROR_MESSAGE())

    END CATCH
 
    -- Returns Error ResultSet
    SELECT * FROM @UsersErrorList;  

    -- Commit Transaction If any Transaction Occured
    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION Update
    
    SET NOCOUNT OFF;  

END  
   

Use Try...Catch Block and Transactions in SQL Server

Use Try...Catch Block and Transactions in Stored Procedure as Followes :

Code :
/******************************************************************************    
-----------------------  
Developers Note   
-----------------------  
Object          : StoredProcedure [dbo].[sp_SetUsersDetails]     
Script Date     : 19/11/2012   
Developed By    : Rohan Sharma
Used For        : Add/Update User
Implementaion   :   

    sp_SetUsersDetails 5, 'Rohan Sharma', 'Block-B', '87867675765'

*****************************************************************************/ 
 
CREATE PROCEDURE [dbo].[sp_SetUsersDetails] 
    -- Parameters Here
    @ID   INT, 
    @Name  NVARCHAR(500), 
    @Address NVARCHAR(500) = NULL, 
    @Phone  NVARCHAR(500) = NULL, 
AS  
BEGIN  

    SET NOCOUNT ON;  

    BEGIN TRANSACTION UpdateLMS

    BEGIN TRY

        -- Put Your Tansaction DB Codes Here

    END TRY
    BEGIN CATCH

        -- Rollback Transaction If any Transaction Occured
        IF @@ROWCOUNT > 0
            ROLLBACK TRANSACTION UpdateLMS

        -- Put Any ErrorLog Codes Here

    END CATCH

    -- Commit Transaction If any Transaction Occured

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION UpdateLMS

    SET NOCOUNT OFF;  

END  
   

Export Data From Excel File in ASP.Net

Export Data From Excel File in ASP.Net

The following function takes the following parameter :

    "excelFileName" : Its specify the Path with File Name .i.e: "D:\MyExcel.xls"

Use the following NameSpace :
    using System.IO;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;
Code :
    /// <summary>
    /// This function convert uploaded Ecxcel sheet into DataTable and then returns 
    /// </summary>
    /// <param name="fileToConvert"></param>
    /// <param name="fileForDs"></param>
    /// <param name="uniqueFileName"></param>
    /// <returns></returns>
    public System.Data.DataTable ReadFromExcelFile(string excelFileName)
    {
        OleDbConnection connection = null;

        //this datatable contains Active Sheet  records 
        System.Data.DataTable dtImprtExcel = new System.Data.DataTable();

        try
        {

            // Get Active Sheet Name
            string activeSheetName = getActiveSheetName(excelFileName);

            //If Active Sheet Name is not Null or Blank
            if (!string.IsNullOrEmpty(activeSheetName))
            {

                // <add key="ExcelConnectionString" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Path;Extended Properties=Excel 8.0;"/>
                string connectionString = ConfigurationManager.AppSettings.Get("ExcelConnectionString").Replace("Path", excelFileName);
                OleDbConnection excelCon = new OleDbConnection(connectionString);
                excelCon.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + activeSheetName + "$]", excelCon);
                adapter.Fill(dtImprtExcel);
                excelCon.Close();

                //cleans up the temporary file that was stored
                File.Delete(excelFileName);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (connection != null)
            {
                if (connection.State == ConnectionState.Open)
                {
                    //close a open connection
                    connection.Close();
                }
            }
        }
        return dtImprtExcel;
    }

Get Active Sheet Name :

The following function takes the following parameter :
"strFileName" : Its specify the Path with File Name .i.e: "D:\MyExcel.xls"

Use the following NameSpace :

    using System.IO;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;

Code :

    /// <summary>
    /// Get Excel Sheet Name
    /// </summary>
    /// <param name="path"></param>
    /// <returns></returns>
    private string getActiveSheetName(string strFileName)
    {
        string activeSheetName = string.Empty;
        try
        {
            Application oXL;
            Workbook oWB;
            Worksheet oSheet;

            // Start Excel and get Application object. 
            oXL = new Application();

            // Set some properties 
            oXL.Visible = false;
            oXL.DisplayAlerts = false;

            // Open the workbook. 
            oWB = oXL.Workbooks.Open(strFileName);

            // Get the active sheet 
            oSheet = (Worksheet)oWB.ActiveSheet;
            activeSheetName = oSheet.Name;

            // Save the sheet and close 
            oSheet = null;
            oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                XlSaveAsAccessMode.xlExclusive,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
            oWB.Close(Missing.Value, Missing.Value, Missing.Value);
            oWB = null;
            oXL.Quit();

            // Clean up 
            // NOTE: When in release mode, this does the trick 
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return activeSheetName;
    }

Get ActiveSheet Name of Uploaded Execl in ASP.Net

 The following function takes the following parameter :

    "strFileName" : Its specify the Path with File Name .i.e: "D:\MyExcel.xls"

Use the following NameSpace :
    using System.IO;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;
Code :
    /// <summary>
    /// Get Excel Sheet Name
    /// </summary>
    /// <param name="path"></param>
    /// <returns></returns>
    private string getActiveSheetName(string strFileName)
    {
        string activeSheetName = string.Empty;
        try
        {
            Application oXL;
            Workbook oWB;
            Worksheet oSheet;

            // Start Excel and get Application object. 
            oXL = new Application();

            // Set some properties 
            oXL.Visible = false;
            oXL.DisplayAlerts = false;

            // Open the workbook. 
            oWB = oXL.Workbooks.Open(strFileName);

            // Get the active sheet 
            oSheet = (Worksheet)oWB.ActiveSheet;
            activeSheetName = oSheet.Name;

            // Save the sheet and close 
            oSheet = null;
            oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                XlSaveAsAccessMode.xlExclusive,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
            oWB.Close(Missing.Value, Missing.Value, Missing.Value);
            oWB = null;
            oXL.Quit();

            // Clean up 
            // NOTE: When in release mode, this does the trick 
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return activeSheetName;
    }

Download Excel in ASP.Net

The Following Function downloads the Excel .

It accepts the following paramete :
fileName : It contains the path with FileName . I.e : "D:\MyExcel.exe".

Code :
    /// <summary>
    /// Download Excel template with some existing data
    /// </summary>
    /// <param name="fileName"></param>
    private void DownloadExcel(string fileName)
    {
        try
        {
            FileInfo file = new FileInfo(fileName);
            if (file.Exists)
            {
                // Clear the content of the response
                Response.ClearContent();
                Response.Clear();

                // add the header that specifies the default filename for the Download/SaveAs dialog
                Response.AddHeader("Content-Disposition", "attachment; filename=LMSUsers.xls");

                // add the header that specifies the file size, so that the browser can show the download progress
                Response.AddHeader("Content-Length", file.Length.ToString());

                // specify that the response is a stream that cannot be read by the client and must be downloaded
                Response.ContentType = "application/vnd.ms-excel";

                // send the file stream to the client
                Response.WriteFile(file.FullName);
                Response.BufferOutput = true;
                Response.Flush();
                Response.Close();

                //Delete the file if Required
                file.Delete();
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.InnerException.Message);
        }
    }

Create Excel with Data in ASP.Net

Create Excel in ASP.Net with Dynamic data .

The following function takes the following parameter :
    "DataTable"  : which holds the data from Database.
    "strFileName" : It is specify the Path with File Name .i.e: "D:\MyExcel.xls"
 
Use the following NameSpace :
    using System.IO;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;
Code :
    /// <summary>
    /// Creates the excel file from the provided dataset.
    /// </summary>
    /// <param name="dtSource">The dt source.</param>
    /// <param name="strFileName">Name of the STR file.</param>
    private void GenerateExcel(System.Data.DataTable dtSource, string strFileName)
    {
        Application oXL;
        Workbook oWB;
        Worksheet oSheet;
        Range oRange;

        // Start Excel and get Application object. 
        oXL = new Application();

        // Set some properties 
        oXL.Visible = false;
        oXL.DisplayAlerts = false;

        // Get a new workbook. 
        oWB = oXL.Workbooks.Add(Missing.Value);

        // Get the active sheet 
        oSheet = (Worksheet)oWB.ActiveSheet;
        oSheet.Name = "LMS Users";
        int rowCount = 1;

        // Add Headrer to Excel
        for (int i = 1; i < dtSource.Columns.Count + 1; i++)
        {
            oSheet.Cells[1, i] = dtSource.Columns[i - 1].ColumnName;
            oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, i];
            oRange.Font.Bold = true;
        }

        // Add the Data Rows to the Excel
        if (dtSource.Rows.Count > 0)
        {
            foreach (DataRow dr in dtSource.Rows)
            {
                rowCount += 1;
                for (int i = 1; i < dtSource.Columns.Count + 1; i++)
                {
                    if (rowCount > 1)
                    {
                        oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                    }
                }
            }
        }
        
        // Resize the columns 
        oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                      oSheet.Cells[rowCount, dtSource.Columns.Count]);
        oRange.EntireColumn.AutoFit();

        // Save the sheet and close 
        oSheet = null;
        oRange = null;
        oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            XlSaveAsAccessMode.xlExclusive,
            Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);
        oWB.Close(Missing.Value, Missing.Value, Missing.Value);
        oWB = null;
        oXL.Quit();

        // Clean up 
        // NOTE: When in release mode, this does the trick 
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
    }

How to get File and Function Details from Exception object

Its better to use ErrorLog in Exception Handling. In ErrorLog we need to keep track of the File and the Function details where this Exception occurs. So, instead of sending the Hardcoded Values (i.e : Function Name , File Name ) to ErrorLog, we can directly get this details from Exception object (i.e : Exception ex) as follows :

Code :

Use namespace :
using System.Diagnostics;
using System.Reflection;
Function where Error Occurred :

        /// <summary>
        /// Set AssetName for the AssetID
        /// </summary>
        /// <param name="programId"></param>
        /// <param name="languageId"></param>
        /// <param name="assetId"></param>
        /// <param name="assetName"></param>
        public void SetAssetName(Int32 progId, Int32 langId, int astId, string astName)
        {
            AssetClient assetClient = null;
            try
            {
                assetClient = new AssetClient(appBindingAssets);
                //return assetClient.SetAssetName(progmId, langId, astId, astName);
            }
            catch (Exception ex)
            {
                //call ErrorLog using the File Details
                LogError(ex);
            }
            finally
            {
                if (assetClient != null)
                {
                    assetClient.Close();
                }
            }
        }
ErrorLog Function accepts Exception object as Parameter :
        /// <summary>
        /// Logs the exception. 
        /// </summary>
        /// <param name="ex"></param>
        public static void LogError(Exception ex)
        {
            // Get the File Details from Exception
            StackTrace st = new StackTrace(ex);
 
            // Get the object of MethodBase from StackTrace
            MethodBase mb = st.GetFrame(st.FrameCount - 1).GetMethod();
 
            // Get FileName with NameSpace 
            // Ex: IGroup.Modules.SalesZone.Controller.SalesZoneController"
            string source = mb.DeclaringType.FullName;
 
            // Get Function Name "GetPhraseTranslationList"
            string methodName = mb.Name;
 
            //Implement ErrorLog Logics here using the File Details
            //AddLogError(source, ex, methodName);
        }

Bind same event to Multiple controls in JQuery

When multiple controls needs to fire a same event.
Instead of binding the event to each control separately , we can create an array of controls and loop through it to bind the single event as following :

Code :

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Single Event for Multiple Controls</title>
     <script src="http://code.jquery.com/jquery-1.7.2.min.js" type="text/javascript"></script>
        <script type="text/javascript">
            $(function() {
                var btns = $('#btnA,#btnC,#btnE');
                $.each(btns, function() {
                    $(this).click(function() {
                        alert(this.id);
                    });
                });
            });
        </script>
</head

<body>
    <input id="btnA" type="button" value="Button A" />
    <input id="btnB" type="button" value="Button B" />
    <input id="btnC" type="button" value="Button C" />
    <input id="btnD" type="button" value="Button D" />
    <input id="btnE" type="button" value="Button E" />
</body>
</html>

Gadget

This content is not yet available over encrypted connections.