How to Update Data in tables: SQL

Programmer need to modify the data in the database when the specifications of a customer, a client, a transaction, or any other data maintained by the organization undergo a change.

For example, if a client changes his address or if the quantity of a product ordered is changed, the required changes need to be made to the respective rows in the tables. You can use the UPDATE statement to make the changes. Updating ensures that the latest and correct information is available at any point of time. One column of a row is the smallest unit of an update.

You can update data in a table by using the UPDATE DML statement. The syntax of the UPDATE statement is:

UPDATE table_name SET column_name = value [, column_name = value]
[FROM table_name]
[WHERE condition]
Where,

• Table_name specifies the name of the table you have to modify.
• Column_name specifies the columns you have to modify in the specified table.
• Value specifies the value(s) with which you have to update the column(s) of the table. Some valid values include an expression, a column name, and a variable name. The DEFAULT and NULL keywords can also be supplied.
• FROM table_name specifies the table(s) that is used in the UPDATE statement.
• Condition specifies the rows that you have to update.

Guidelines for Updating Data

You need to consider the following guidelines while updating data:

• An update can be done on only one table at a time.
• If an update violates integrity constraints, then the entire update is rolled back.

The following statement updates the AddressLine2 attribute of AddressID 104

UPDATE Address
SET AddressLine2 = ‘Plaza Palace’
WHERE AddressID = 104

Consider another example where you need to update the Title of an employee, Lynn Tsoflias to ‘Sales Executive’, in the Employee table. To perform this task, you need to refer to the contact table to obtain the Contact ID. You can update the details by using the following statement:

UPDATE HumanResources.Employee SET Title = ‘Sales Executive’
FROM HumanResources.Employee e, Person.Contact c
WHERE e.contactID = c.ContactID
AND c.FirstName = ‘Lynn’ and c.LastName = ‘Tsoflias’

When the preceding command is executed, the Title will be changed to ‘Sales Executive’.

Delete data from database and related table.

How to Delete Data from Table or Related Table, SQL

Programmer need to delete data from the database when it is no longer required. The smallest unit that can be deleted from a database is a row. You can delete a row from a table by using the DELETE DML statement. The syntax of the DELETE statement is:

DELETE [FROM] table_name
[FROM table (s)]
[WHERE condition]
Where,

• Table_name specifies the name of the table from which you have to delete rows.
• Table_name specifies the name of the table(s) required to set the condition for deletion.
• Condition specifies the condition that identifies the row(s) to be deleted.

For example, the following statement deletes the address details of AddressID 104 from the Address table:

DELETE Address
WHERE AddressID = ‘104’

Deleting Data from Related Tables

While deleting records form related tables, you need to ensure that you first delete the records from the table that contain the foreign key and then from the table that contains the primary key.

Consider the example of the Adventure Works. The Employee table contains data of those employees who have retired from the company. This data is not required anymore. This increases the size of the database.

You are required to ensure that this old data is removed from the Employee table. You can delete this data by using the following SQL statement:

DELETE FROM HumanResources.Employee
WHERE BirthDate < dateadd (yy, -60, getdate ())

The database contains tables related to the Employee table. The related tables are HumanResources.EmployeeAddress, HumanResources.EmployeeDepartmentHistory, HumanResources.EmployeePayHistory, and HumanResources.JobCandidate. The EmployeeID attribute in these tables is a foreign key to the EmployeeID attribute of the Employee table. Therefore, the query results in an error. Therefore, you need to delete data from the related tables before executing the preceding DELETE statement.

Deleting All the Records from a Table

As a database developer, you might need to delete all the records from a table. You can do this by using the following DELETE statement:

DELETE table_name

You can also use the TRUNCATE DML statement. The syntax of the TRUNCATE statement is:
TRUNCATE TABLE table_name
Where,

• Table_name specifies the name of the table from which you have to delete rows. However, TRUNCATE TABLE is executed faster.

TRUNCATE TABLE does not support the WHERE clause. In addition, the TRUNCATE TABLE statement does no fire a trigger. When truncate is used, the deleted rows are not entered in the transaction log.

For example, the following statement deletes all the records from the Address table:
TRANCATE TABLE Address

Manipulate XML Data and Parsing with XML document.

How to Manipulate XML data in Database Table, SQL

With a growth in clients accessing data through heterogeneous hardware and software platforms, a need across for a language that could be interpreted by any environment. This resulted in the evolution of a language called XML. SML is a mark-up language that is used to describe the structure of data in a standard hierarchical manner.

The structure of the documents containing the data is described with the help of tags contained in the document. Therefore, various business applications store their data in XML documents.

SQL Server allows you to save or retrieve data in the XML format. This enables the SQL Server to provide database support to various kinds of applications. As a database developer, it is important for you to learn to manipulate the XML data by using SQL Server.

The structure of the XML data can be defined in the form of a supporting Document Type Definition (DTD) or schema. You can read more about XML in the appendix.

Staring XML Data in a Table

The XML data is available in the form of XML fragments or complete XML documents. An XML fragment contains XML data without a top-level element that contains the complete data.

SQL Server 2005 uses XML as a data type to save the XML data in its original state. You can create tables or variables by using this data type to store the XML data. However, you can also shred the XML data and store the values in different columns in a rowset. This process of transforming the XML data into a rowset is called as shredding.

In SQL Server, you can store the XML data in the following ways:

• A rowset
• An XML column

Staring the XML Data in a Rowset

Consider an example. You have received the order details from a vendor. The order details are generated by the application used by the vendor in an XML document. You need to store this data in a database table. For this, you need to shred the XML data. The SQL Server allows you to shred the XML data by using the OPENXML function and its related stored procedures.

Shredding an XML document involves the following tasks:

• Parse the XML document.
• Retrieve a rowset from the tree.
• Store the data from the rowset.
• Clear the memory.

How to Parse XML Document and Retrieve Rowset: SQL Server Syntax

SQL Server provides the sp_sml_preparedocument stored procedure to parse the XML document. This stored procedure reads the XML document and parses it with the MSXML parser. Parsing an XML document involves validating the XML data with the structure defined in the DTD or schema.

The parsed document is an internal tree representation of various nodes in the XML document, such as elements, attributes, text, and comments. Sp_xml_preparedocument returns a handle or pointer that can be used to access the newly created internal representation of the XML document. This handle is valid for the duration of the session or until the handle is invalidated by executing

Sp_xml_removedocument.

Retrieving a Rowset from the Tree

After verifying the accuracy of the structure and completeness of data, you need to extract the data from the available XML data. For this, you can use the openxml function to generate an in-memory rowset from the parsed data. The syntax of the openxml function is:

Openxml ( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]
Where,

• Idoc specifies the document handle of the internal tree representation of an XML document.
• Rowpattern specifies the XPath pattern used to identify the nodes (in the XML document whose handle is passed in the idoc parameter) to be processed as rows.
• Flags indicates the mapping that should be used between the XML data and the relational rowset, and how the spill-over column should be filled. Flags is an optional parameter and can have the following values:
  0 – to use the default mapping (attributes)
  1 – to retrieve attribute values
  2 – to retrieve element values
  3 – to retrieve both attribute and element values
• Schemadeclaration specifies the rowset schema declaration for the columns to be returned by using a combination of column names, data types, and patterns.
• TableName specifies the table name that can be given, instead of SchemaDeclaration, if a table with desired schema already exists and no column patterns are required.

C# - Basic Syntax

C# supports object-oriented features like Class, Object, Encapsulation, Polymorphism, Data abstraction, Inheritance etc. In Object-Oriented Programming technology, we works on real time entity(known as object), these entity related to each other also communicate by message passing. If we discuss about class, a class consists of data member and member function (known as encapsulation). Using Object we can call method also initialize class data members. In later session we will discuss about object oriented features. In this article we will design basic structure of class and object.For example, let us consider a circle object. It has attributes like radius. Depending upon the design, it may need ways for accepting the values of this attribute, calculating area and display details.

Let us look at an implementation of a circle class and discuss C# basic syntax, on the basis of our observations in it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace circle
{
    class circle_area
    {
         private static float pi = 3.14F;
         private int radius;
        // initialize datamember in the constructor.
         public circle_area(int radi)
         {
             radius = radi;

         }
        // calcuate area of the circle.
         public double getarea()
         {
             return pi * radius * radius;
         }

    }
    class Program
    {
        static void Main(string[] args)
        {
            circle_area ca = new circle_area(5);
            Console.WriteLine(Convert.ToString(ca.getarea()));
            Console.ReadKey();
        }
    }
}


When the above code is compiled and executed, it produces the following result:

The First line of the program define the system Namespace, a single program can hold multiple using statement. The System Namespace consists of many class like Console class. The Second line of the program hold NameSpace declaration, according to my previous article a single Namespace can contain multiple classes, in this program we have two class first one is circle_area and second one is program. In later session we will discuss, how a Namespace consists of another Namespace, deligates and structure. The third line of the program define class declaration, In this program we have two classes and each class hold at least single member function.

Comments in C#

Comments are used for designing user friendly program. Compilers ignore the comment entries. we can use single line as well as multi-line comments in the c# program The multi-line comments in C# programs start with /* and terminates with the characters */ as shown below:

/* Program define how to

calculate circle area with static keyword*/

Single-line comments are declared by double slash like //, according to above mentioned code, which is

// initialize Data Member in the constructor.

Data Member

It shows the properties of an object, suppose you have a object employee so create a class, which covers employee details like Employee_id, Employee_Name, etc. These properties known as data members. In this example we have two properties for circle class, these are pi and radius. Basically these variable are used for storing data.

Member Functions

It shows the behavior of the class, in which we can perform some action related to object properties. In this example we have one member function getarea(), which returns calculated area in double type.

Class and how to declare it:

Class is a user defined data type or you can say it is a composite data type. we have many definition of a class like "Class is a container of data member and member function" and second one is "class is a instance of a variable". 

Design a new class in c#- synatx of class

class class_name 

{

// Data member;

// Member function;    

}

Ok, Now understand what is in this program:

First start with main () method because its the entry point of the program. Here we have a object , which is created by new keyword. Also assign the instance name , which is ca. According to constructor theory, When we create a object ,constructor call automatically. Assign the Data Member by the constructor.  

 After assign the data member we can perform some operation on it. Now, we calculate area of the circle using getarea() method. This method is called from object ca.

C# - Program Structure

Introduction

Now, we will learn basic structure of the c# program as well as building blocks. In this article, we will cover only basic structure of c#. In this article we will learn sample code plus provide additional background information.

C# Hello World! Example
Simple c# program should take some minimum functionality

• Imported namespace ( How to use libraries)
• Namespace declaration (How to create new Namespace in c#)
• A class (How to design a class in c#)
• Class methods (Default class consist of single main() method, you can take other methods)
• Class Properties(it shows the behavior of the class as well as object)
• A Main method (its the entry point of the c# program)
• Statements & Expressions (You can put your ideas using statements and expression)
• Comments (Design user friendly using comments)

Lets take a simple that would print Hello World!, I have visual studio 2013 , you can take 2005 and above version.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace csharpprogram
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World");
            Console.ReadKey(false);
        }
    }
}

If you want to compile and execute this code, press green triangle button, which is mentioned in VS IDE and see your result

Ok, Now understand what is in this program:

• The first five line of the program using System; and etc - the using keyword is most important keyword in c# library, you can take it in different purpose like The using block is used, where you want to deallocate resources forcefully. Also used where you want to add library references in the code. A program generally has multiple using statements.
• The next line has the namespace declaration. A namespace is a collection of classes, enum , delegates , structure and etc, also its not a replacement of assemblies. Here csharpprogram namespace consists of single Program class.
• The next line has a class declaration, the class Program contains data members and member functions, also known as encapsulation, which is discuss later. The data members shows the properties of the object and member functions shows behavior of the object. Like, we take a person as a object then we can take person name, person id, person address, etc as a data member and person_walk consider in member function. 
• The next line defines the Main method, which is the entry point for all C# programs.  The main method consists of various statement. In our c# program, we can take multiple main methods.
• The next line defines the WriteLine( ) method, which return the string output onto the screen. It is included in Console class , which is in System Namespace.
• Using the Console.ReadKey( ) method, which is overloaded by boolean values. You can take true as well as false as a argument in it. False parameter shows presses key onto the screen. Using This method compiler wait for a key press and it prevents the screen from running and closing quickly when the program is launched from Visual Studio .NET. 

Note: Some Basic concepts about c#

1. C# is a case sensitive language.
2. Statement and expression end with semicolon(;)
3. Always C# program start from main method.

How to Compile & Execute a C# Program: lets check this video.

How to find month name in asp.net c#

Using the CultureInfo, you can get calendar information such as date, current month, current date etc. In this program we will get month name. Using the parameterized GetMonthName ( ) method , you can  retrieve month information.
<form id="form1" runat="server">
    <div>  
        <asp:Button ID="Button1"
         runat="server"
         BackColor="#33CCFF"
         onclick="Button1_Click"
         Text="Click"
          Width="69px" />  
    </div>
        <asp:Label ID="Label1"
         runat="server"
          BackColor="Yellow"
          Text="Label"></asp:Label>
    </form>
Code Behind
 protected void Button1_Click(object sender, EventArgs e)
        {          
            DateTime today = DateTime.Today;
            String Todaymonth = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(today.Month);
            String month = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(2);
            Label1.Text = "Today : " + today.ToLongDateString();
            Label1.Text += "<br /><br />today month Name= ";
            Label1.Text += Todaymonth;
            Label1.Text += "<br /><br /> [2] number month= ";
            Label1.Text += month;

        }
Code Generate the following output