Thursday, August 21, 2014

Linq To Xml

 
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="UploadImages.WebForm1" %>

<!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">
    <strong>Add To XML</strong><br />
    Name:<br />
    <asp:TextBox ID="txtName" runat="server" /><br />
    City:<br />
    <asp:TextBox ID="txtCity" runat="server" />
    <br />
    Age:<br />
    <asp:TextBox ID="txtAge" runat="server" />
    <br />
    <asp:Button ID="butAdd" runat="server" Text="Add" onclick="butAdd_Click" /><br />
    <asp:Label ID="lblStatus" runat="server" /><br />
    <br />
    <strong>Read XML:</strong><br />
    <asp:Button ID="butRead" runat="server" Text="Read" onclick="butRead_Click" /><br />
    <asp:Literal ID="litResults" runat="server" />
    </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.Xml.Linq;

namespace UploadImages
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void butRead_Click(object sender, EventArgs e)
        {
            readXML();
            lblStatus.Text = "";

        }

        protected void butAdd_Click(object sender, EventArgs e)
        {
            try
            {
                if (txtName.Text == "" || txtCity.Text == "" || txtAge.Text == "")
                    lblStatus.Text = "Please complete the form.";
                else
                {
                    XDocument xmlDoc = XDocument.Load(Server.MapPath("XMLFile1.xml"));

                    xmlDoc.Element("Persons").Add(new XElement("Person", new XElement("Name", txtName.Text),
                    new XElement("City", txtCity.Text), new XElement("Age", txtAge.Text)));

                    xmlDoc.Save(Server.MapPath("XMLFile1.xml"));
                    lblStatus.Text = "Data successfully added to XML file.";
                    readXML();
                }
            }
            catch
            {
                lblStatus.Text = "Sorry, unable to process request. Please try again.";
            }
        }
        protected void readXML()
        {
            XDocument xmlDoc = XDocument.Load(Server.MapPath("XMLFile1.xml"));

            var persons = from person in xmlDoc.Descendants("Person")
                          select new
                          {
                              Name = person.Element("Name").Value,
                              City = person.Element("City").Value,
                              Age = person.Element("Age").Value,
                          };

            litResults.Text = "";
            foreach (var person in persons)
            {
                litResults.Text = litResults.Text + "Name: " + person.Name + "<br />";
                litResults.Text = litResults.Text + "City: " + person.City + "<br />";
                litResults.Text = litResults.Text + "Age: " + person.Age + "<br /><br />";
            }

            if (litResults.Text == "")
                litResults.Text = "No Results.";
        }
    }
}
<?xml version="1.0" encoding="utf-8"?>
<Persons>
    <Person>
    <Name>naresh</Name>
    <City>Hyd</City>
    <Age>22</Age>
  </Person>
    <Person>
    <Name>naresh</Name>
    <City>wgl</City>
    <Age>22</Age>
  </Person>
</Persons>


Wednesday, August 13, 2014

SQL Server Transactions Management

Transaction is a set of T-SQL statements that are executed together as a unit like as a single T-SQL statement. If all of these T-SQL statements executed successfully, then a transaction is committed and the changes made by T-SQL statements permanently saved to database. If any of these T-SQL statements within a transaction fail, then the complete transaction is cancelled/ rolled back.
We use transaction in that case, when we try to modify more than one tables/views that are related to one another. Transactions affect SQL Server performance greatly. Since When a transaction is initiated then it locks all the tables data that are used in the transaction. Hence during transaction life cycle no one can modify these tables’ data that are used by the transaction. The reason behind the locking of the data is to maintain Data Integrity.

Types of Transactions

1.               Implicit Transaction

Implicit transactions are maintained by SQL Server for each and every DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE) statements. All these T-SQL statements runs under the implicit transaction. If there is an error occurs within these statements individually, SQL Server will roll back the complete statement.

2.               Explicit Transaction


Explicit transactions are defined by programmers. In Explicit transaction we include the DML statements that need to be execute as a unit. Since SELECT statements doesn’t modify data. Hence generally we don’t include Select statement in a transaction.

 

 

       CREATE TABLE Department
      (
       DeptID int PRIMARY KEY,
       DeptName varchar(50) NULL,
       Location varchar(100) NULL,
 )
GO

-----------------------------------------------------------------------------
       CREATE TABLE Employee
      (
       EmpID int PRIMARY KEY,
       Name varchar(50) NULL,
       Salary int NULL,
Address varchar(100) NULL,
       DeptID int foreign Key references Department(DeptID)
      )


-------------------------------------------------------------------------------------------
--Now Insert data
      INSERT INTO Department(DeptID,DeptName,Location)VALUES(1,'IT','Delhi')
      GO
      INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',15000,'Delhi',1)
      SELECT * FROM Department
      SELECT * FROM Employee

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


BEGIN TRANSACTION trans
      BEGIN TRY
      INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')
      INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)
IF @@TRANCOUNT > 0
       BEGIN COMMIT TRANSACTION trans
       END
      END TRY
      BEGIN CATCH
       print 'Error Occured'
       IF @@TRANCOUNT > 0
       BEGIN ROLLBACK TRANSACTION trans
       END
      END CATCH
--------------------------------------------------------------------------------

       --Now Select data to see transaction affects
      SELECT * FROM Employee
      SELECT * FROM Department

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

      BEGIN TRY
      INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')
      IF @@TRANCOUNT > 0
       BEGIN SAVE TRANSACTION trans;
       END
      INSERT INTO Department(DeptID,DeptName,Location)VALUES(3,'Admin','Delhi')
      INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)
      IF @@TRANCOUNT > 0
       BEGIN COMMIT TRANSACTION trans
       END
      END TRY
      BEGIN CATCH
       print 'Error Occured'
      IF @@TRANCOUNT > 0
       BEGIN ROLLBACK TRANSACTION trans
       END
      END CATCH

-----------------------------------------------------------------------------------------------
      SELECT * FROM Employee

      SELECT * FROM Department 

Swap the values of two columns in SQL Server



SELECT * FROM CUSTOMER

UPDATE Customer SET Name=Address , Address=Name

SELECT * FROM CUSTOMER


Tuesday, August 12, 2014

cursor

cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use cursor when we need to update records in a database table in singleton fashion means row by row.
Declare Cursor
A cursor is declared by defining the SQL statement that returns a result set.
Open
A Cursor is opened and populated by executing the SQL statement defined by the cursor.
Fetch
When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
Close
After data manipulation, we should close the cursor explicitly.
Deallocate
Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

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 

SET @path = 'C:\Backup\'

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

DECLARE db_cursor CURSOR FOR
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')

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



SQL Server Cursor Components

Based on the example above, cursors include these components:
  • DECLARE statements - Declare variables used in the code block
  • SET\SELECT statements - Initialize the variables to a specific value
  • DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
    • NOTE - There are an equal number of variables in the DECLARE <cursor_name> CURSOR FOR statement as there are in the SELECT statement.  This could be 1 or many variables and associated columns.
  • OPEN statement - Open the cursor to begin data processing
  • FETCH NEXT statements - Assign the specific values from the cursor to the variables
    • NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
  • WHILE statement - Condition to begin and continue data processing
  • BEGIN...END statement - Start and end of the code block
    • NOTE - Based on the data processing multiple BEGIN...END statements can be used
  • Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
  • CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
  • DEALLOCATE statement - Destroys the cursor


Syntax to Deallocate Cursor
Deallocate statement delete the cursor definition and free all the system resources associated with the cursor. The basic syntax to close cursor is given below:
 DEALLOCATE cursor_name --after deallocation it can't be reopen 
SQL SERVER – Simple Examples of Cursors
 CREATE TABLE Employee
(
 EmpID int PRIMARY KEY,
 EmpName varchar (50) NOT NULL,
 Salary int NOT NULL,
 Address varchar (200) NOT NULL,
)
GO
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')
GO
SELECT * FROM Employee 
 
 SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
 DECLARE cur_emp CURSOR
STATIC FOR 
SELECT EmpID,EmpName,Salary from Employee
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 WHILE @@Fetch_status = 0
 BEGIN
 PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 END
1. END
2. CLOSE cur_emp
3. DEALLOCATE cur_emp
4. SET NOCOUNT OFF 

Subscribe to: Posts (Atom)