How to apply Check on Constraint on Column in SQL

A check constraint enforces domain integrity by restricting the values to be inserted in a column. It is possible to define multiple check constraints on a single column. These are evaluated in the order in which they are defined. The syntax of applying the check constraint is:

CREATE TABLE table_name
(
Col_name [CONSTRAINT onstraint_name] CHECK (expression)
(col_name [, col_name [, …]])
.
)
A single check constraint can be applied to multiple columns when it is defined at the table level
Where,

• Constraint_name specifies the name of the constraint to be created.
• Expression specifies the conditions that define the check to be made on the column. Expression can include elements, such as arithmetic operators, relational operators, o keywords, such as IN, LIKE, and BETWEEN.

A check constraint can be specified by using the following keywords:

• IN: To ensure that the values entered are from a list of constant expressions.
• LIKE: To ensure that the value entered in specific column are of a certain pattern. This can be achieved by using wildcards.
• BETWEEN: To specify a range of constant expressions by using the BETWEEN keyword. The upper and lower boundary values are included in the range.

The rules regarding the creation of the CHECK constraint are as follows:

• It can be created at the column level.
• It can contain user-specified search conditions.
• It cannot contain subqueries.
• It does not check the existing data in the table if created with the WITH NOCHECK option.
• It can reference other columns of the same table.

In the example of the EmployeeLeave table, you need to ensure that the leave type can take any one of the three values: CL, PL, or SL. For this, while creating the table, you can add the check constraint using the IN keyword for the LeaveType column

You can use the following statement to apply the check constraint:

CREATE TABLE HumanResources.EmployeeLeave
(
EmployeeID int CONSTRAINT fkEmployeeID FOREIGN KEY REFERENCES HumanResources.Employee (EmployeeID),
LeaveStartDate datetime CONSTRAINT cpkLeaveStartDate PRIMARY KEY (EmployeeID, LeaveStartDate), LeaveEndDate datetime NOT NULL,
LeaveReson varchar(100),
LeaveType char (2) CONSTRAINT chkLeave CHECK (LeaveType IN (‘CL’, ‘SL’, ‘PL’))
)

The preceding command creates the EmployeeLeave table with a check constraint on the LeaveType column. The name of the constraint is chkLeave.

Foreign key constraints

How to use Foreign Key Constraint on Column in SQL

Database developer can use the foreign key constraint to remove the inconsistency in two tables when the data in one table depends on the data in another table.

A foreign key constraint associates one or more columns (the foreign key) of a table with an identical set of columns (a primary key column) in another table on which a primary key constraint has been defined. The syntax of applying the foreign key constraint when creating table is:

CREATE TABLE table_name
(
Col_name [CONSTRAINT constraint_name FOREIGN KEY (col_name [, col_name [, …]])
REFERENCES table_name (column_name [, column_name [, …]])]
Col_name [, col_name [, col_name [, …]]])
Col_name [, col_name [, col_name [, …]]]
)
Where,

• Constraint_name is the name of the constraint on which the foreign key constraint is to be defined.
• Col_name is the name of the column on which the foreign key constraint is to be enforced.
• Table_name is the name of the related table in which the primary key constraint has been specified.
• Column_name is the name of the primary key column of the related table on which the primary key constraint has been defined.

In the example of the EmployeeLeave table under the HumanResources schema, you need to add the foreign key constraint to enforce referential integrity. In the HumanResources schm,the EmployeeID column is set as a primary key in the Employee table. Therefore, you need to set EmployeeID in the EmployeeLeave table as a foreign key.

In the preceding example, you can use the following statement to apply the foreign key constraint in the EmployeeLeave table:

CREATE TABLE HumanResources.EmployeeLeave
(
EmployeeID int CONSTRAINT fkEmployeeID FOREIGN KEY REFERENCES
HumanResources.Employee (EmployeeID),
LeaveStartDate datetime CONSTRAINT cpkLeaveStartDate PRIMARY KEY (EmployeeID, LeaveStartDate) ,
…
…
…
)

The preceding statement creates the EmployeeLeave table with a foreign key constraint on the EmployeeID column. The name of the constraint is fkEmployeeID.

Unique key Constraints

Linear QUEUE for Data Structure in 'C'

Linear QUEUE:
Linear Queue is a Queue in which the elements are arranged in a linear order, one after the other like the elements of one-dimensional array or linked list. The arrangement of elements and storage is similar to array or linked list, but the insertion and deletion are restricted.
Implementation of Linear QUEUE using one-dimensional array:
                                                    The linear QUEUE can be represented graphically as consecutive blocks when it is implemented using one-dimensional array. N the maximum number of elements can be added into the QUEUE is called as size of QUEUE. To represent a QUEUE an array of size N can be used. The capacity of QUEUE in this case will  be N.

In the above representation the size of QUEUE is 8. FRONT and REAR index variables can be used to do the DELETE and ADD operations respectively. Initially when the QUEUE is empty FRONT and REAR are assigned with a value 0,because of Lower Bound 1 of array. When both are equal to 0, then the QUEUE is said to be empty.
         When first element is added into the QUEUE, REAR and FRONT are both incremented by 1 and the element to be added is placed at REAR index of QUEUE, QUEUE is name of the array i.e.  FRONT<--1,   REAR<--1    and QUEUE[REAR]<--ITEM. ITEM is the element  to be added to QUEUE. In the further addition operation, the REAR is incremented and the ITEM is copied at REAR index. When the REAR is equal to N, the QUEUE, QUEUE is said to be full. If any addition is done when the QUEUE is full, ‘overflow’ occurs.
         When the element is to be deleted from the QUEUE, the element stored in the QUEUE with FRONT index is deleted by copying it in ITEM and the incrementing FRONT by 1. When FRONT is equal to 0, the QUEUE is empty. When the QUEUE is empty and if the deletion operation is done ‘underflow’, occurs. As an example, consider a QUEUE of size8. It is initially represented as follows:

When element is deleted from the QUEUE, ITEM is copied with A, because QUEUE [FRONT] gives A and FRONT is incremented, QUEUE becomes,

When G is added into QUEUE, the QUEUE becomes,

When I is added into the QUEUE, overflow occurs because REAR=8, the size of QUEUE and QUEUE is full. Even though 1 2 3 4 indices are free, the overflow occurs, this is the disadvantage Circular Queue is used.

About the Default Edit Action Methods and View in MVC

Earlier article was about the create action of the controller, which in other words used to create/insert a record into the database using an MVC application. This article have a brief description about Edit action created by default, it’s Get and post method.

According to name, Get method is used to get a particular model according to passed id and return to the view that will show that model. The below code will return the details of a particular Student. User can edit particular information in the textboxes shown on the form and click on submit button.

// GET: /Student/Edit
public ActionResult Edit(int id = 0)
{
Student student = db.Students.Find(id); //db is object of our context class
if (student == null)
{
return HttpNotFound();
}
return View(student);
}
The first line is used to find the existing record by the primary key (id) passed as parameter. Checking all the required condition, it will return the selected student on the Edit view. After clicking on the submit button, compiler goes to the post method of the same action i.e. Post Edit action, as written below:

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit(Student student)
{
if (ModelState.IsValid)
{
db.Entry(student).State = EntityState.Modified;
db.SaveChanges();
return RedirectToAction("Index");
}
return View(student);
}
This method have a parameter of type student that was passed through the Get method of the same action. The first two line of this code are attributes used to tell the compiler about this method i.e. this is post method and validates fields on submit method. In the create action we have add the student, but in this case we need to just change the state to Modified to update all the fields provided to the user to edit on the view.

@using (Html.BeginForm()) {
@Html.AntiForgeryToken()
@Html.ValidationSummary(true)
@Html.HiddenFor(model => model.Id)
<div class="editor-label">
@Html.LabelFor(model => model.Name)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Name)
@Html.ValidationMessageFor(model => model.Name)
</div>……………….
……………….
……………….

This view is strongly typed view because of the first line and have all the fields of student class (only name is shown here, remaining are something like this).

So turn back on our previous discussion that was about the post Edit action. This action will then check the model is valid or not (Validation). If valid then it will update this student in the student table and then save the changes to the database. If not it will return to the same page with the errors generated.

The last line will return to the same action i.e. Edit with the model passed as parameter and of course with validation errors, you can check this to not enter some fields in the form and submit the data.

Crete a Custom View using Empty Controller

GridView Control and their properties, methods and events in ASP.NET

The GridView control is a data bound control that displays the values of a data source in the form of a table. In this table, each column represents a field and each row represents a record. The GridView control exists within the System.Web.UI .Controls namespace.

When you drag and drop the GridView control on the designer page, the following syntax is added to the source view of the page:
<asp:GridView ID="GridView1" runat="server"> </asp:GridView>

The GridView data control has a built-in capability of sorting, paging, updating, and deleting data. You can also set the column fields through the AutoGenerate property to indicate whether bound fields are automatically created for each field in the data source.

Column fields types of the GridView class

BoundField:   Shows the value of a field in a data source. It is the default column type of the GridView control.

ButtonField:   Shows a command button for each item in the GridView control. It allows to create a column of custom button controls, such as the Add or the Remove button.

CheckBoxField:  Shows a CheckBox control for each item in the GridView control. This column field type is commonly used to display fields with a Boolean value.

CommandField:  Shows predefined command buttons to perform operations, such as select, edit, or delete.

HyperLinkField:    Shows the value of a field in a data source as a hyperlink. This column field type allows you to bind a second field to the hyperlink's URL.

ImageField:            Shows an image for each item in the GridView control.

TemplateField:    Shows user-defined content for each item in the GridView control according to a specified template. This column field type enables us to create a custom column field.

You can also customize the appearance of the GridView control by setting the style properties

EditRowStyle: Performs the style settings for the row that is edited in the GridView control.

EmptyDataRowStyle: Performs the style settings for the empty data row displayed in the GridView control when the data source does not contain any records.

FooterStyle: Performs the style settings for the footer row of the GridView control.

HeaderStyle: Performs the style settings for the header tow of the GridVxew control.

PagerStyle: Performs the style settings for the pager row of the GridView control.

SelectedRowStyle: Performs the style settings for the selected row in the GridView control.

Properties of the GridView Class

AllowPaging: Obtains or sets a value indicating whether the paging feature is enabled.

AllowSorting: Obtains or sets a value indicating whether the sorting feature is enabled.
AlternatingRowStyle: Obtains a reference to the TableltemStyle object that enables us to set the appearance of alternating data rows in a GridView control.
AutoGenerateColumns: Obtains or sets a value indicating whether bound fields are automatically created for each field in the data source.
AutoGenerateDeleteButton: Obtains or sets a value indicating whether a CommandField field column with a Delete button for each data row is automatically added to a GridView control.
AutoGenerateEditButton: Obtains or sets a value indicating whether a CommandField field column with an Edit button for each data row is automatically added to a GridView control.
AutoGenerateSelectButton: Obtains or sets a value indicating whether a CommandField field column with a Select button for each data row is automatically added to a GridView control.
BacklmageUrl: Obtains or sets the URL to an image to display in the background of a GridView control.
BottomPagerRow: Obtains a GridViewRow object that represents the bottom pager row in a GridView control.
Caption; Obtains or sets the text to render in an HTML caption element in a GridView control. This property is provided to make the control more accessible to users of assistive technology devices.
CaptionAlign: Obtains or setsthe horizontal or vertical position of the HTML caption element in a GridView control. This property is provided to make the control more accessible to users of assistive technology devices.
CellPadding: Obtains or sets the amount of space between the contents of a cell and the cell's border.
Cellspacing: Obtains or sets the amount of space between cells.
Columns: Obtains a collection of DataControlField objects that represent the column fields
in a GridView control.
DataKeyNames: Obtains or sets an array that contains the names of the primary key fields for the items displayed in a GridView control.
DataKeys: Obtains a collection of DataKey objects that represent the data key value of each row in a GridView control.
Editlndex:  Obtains or sets the index of the row to edit.
EditRowStyle: Obtains a reference to the TableltemStyle object that enables us to set the appearance of the row selected for editing in a GridView control.
EmptyDataRowStyle: Obtains a reference to the TableltemStyle object that enables us to set the appearance of the empty data row rendered when a GridView control is bound to a data source that does not contain any records.
EmptyDataTemplate: Obtains or sets the user-defined content for the empty data row rendered when a GridView control is bound to a data source that does not contain any records.
EmptyDataText: Obtains or sets the text to display in the empty data row rendered when a GridView control is bound to a data source that does not contain any records.

EnableSortingAndPagingCallbacks: Obtains or sets a value indicating whether client-side callbacks are used for sorting and paging operations.
FooterRow: Obtains a GridViewRow object that represents the footer row in a GridView control.
FooterStyle: Obtains a reference to the TableltemStyle object that enables us to set the appearance of the footer row inaGridView control.
GridLines: Obtains or sets the gridline style for a GridView control.
HeaderRow: Obtains a GridViewRow object that represents the header row in a GridView control.
HeaderStyle: Obtains a reference to the TableltemStyle object that enables us to set the appearance of the header row in a GridView control.
HorizontalAlign: Obtains or sets the horizontal alignment of a GridView control on the page.
PageCount: Obtains the number of pages required to display the records of the data source in a GridView control.
Pagelndex: Obtains or sets the index of the currently displayed page.
PagerSettings: Obtains a reference to the PagerSettings object that enables us to set the properties of the pager buttons in a GridView control.
PagerStyle: Obtains a reference to the TableltemStyle object that enables us to set the appearance of the pager row in a GridView control.
PagerTemplate: Obtains or sets the custom content for the pager row in a GridView control.
PageSize: Obtains or sets the number of records to display on a page in a GridView control.
RowHeaderColumn: Obtains or sets the name of the column to use as the column header for the GridView control. This property is provided to make the control more accessible to users of assistive technology devices.
Rows: Obtains a collection of GridViewRow objects that represent the data rows in a GridView control.
RowStyle: Obtains a reference to the TableltemStyle object that enables us to set the appearance of the data rows in a GridView control.
SelectedDataKey: Obtains the DataKey object that contains the data key value for the selected row in a GridView control.
Selectedlndex: Obtains or sets the index of the selected row in a GridView control.
SelectedRow: Obtains a reference to a GridViewRow object that represents the selected row in the control.
SelectedRowStyle: Obtains a reference to the TableltemStyle object that enables us to set the appearance of the selected row in a GridView control.
SelectedValue: Obtains the data key value of the selected row in a GridView control.
ShowFooter: Obtains or sets a value showing whether the footer row is displayed in a GridView control.
ShowHeader: Obtains or sets a value showing whether the header row is displayed in a GridView control.
SortDirection Obtains the sort direction of the column being sorted.
SortExpression Obtains the sort expression associated with the column or columns being sorted.
TopPagerRow Obtains a GridViewRow object that represents the top pager row in a GridView control.
UseAccessibleHeader Obtains or sets a value indicating whether a GridView control renders its header in an accessible format. This property makes the control more accessible to users of assistive technology devices.

Methods of the GridView Class

DataBind: Binds the data source to the GridView control
DeleteRow: Deletes the record at the specified index from the data source
IsBindableType: Determines whether the specified data type can be bound to a column in a GridView control
Sort: Sorts the GridView control based on the specified sort expression and direction
UpdateRow: Updates the record at the specified row index using the field values of the row

Events of the GridView Class

PagelndexChanged: Invoked when one of the pager buttons is clicked after the GridView control handles the paging operation

PagelndexChanging: Invoked when one of the pager buttons is clicked, but before the GridView control handles the paging operation

RowCancelingEdit: Invoked when the Cancel button of a row in edit mode is clicked, but before the row exits the edit mode

RowCommand: Invoked when a button is clicked in a GridView control.

RowCreated:         Invoked when a row is created in a GridView control

RowDataBound: Invoked when a data row is bound to data in a GridView control

RowDeleted: Invoked when a row's Delete button is clicked, but after the GridView control deletes the row

RowDeleting: Invoked when a row's Delete button is clicked, but before the GridView control deletes the row

RowEditing: Invoked when a row's Edit button is clicked, but before the GridView control enters edit mode

RowUpdated: Invoked when a row's Update button is clicked, but after the GridView control updates the row

RowUpdating: Invoked when a row's Update button is clicked, but before the GridView control updates the row

SelectedlndexChanged: Invoked when a row's Select button is clicked, but after the GridView control handles the select operation

SelectedlndexChanging: Invoked when a row's Select button is clicked, but before the GridView control handles the select operation.

Sorted: Invoked when the hvperlink to sort a column is clicked, but after the GridView control handles the sort operation

Sorting: Invoked when the hyperlink to sort a column is clicked, but before the GridView control handles the sort operation

Data Bound Control in ASP.NET

What are databases? Why do we need them? How does a .NET application work with databases? These are some of the questions whose answers we need to know before we learn about working with databases in ASP.NET.
A. database is a collection of records or information that is stored in the form of tables in a systematic way so that a computer program can access the information easily whenever required. Structured Query Language (SQL) is used for retrieving, storing, deleting, and updating the records stored in a database. An application may need to use the databases for performing various functions, such as:
□ Displaying data in a tabular format by retrieving the records from the database
□ Displaying the data after processing the record retrieved from the database, such as calculating the service duration of each employee and then displaying the records
□ Processing the retrieved data and updating them in the database
□ Deleting the records from the database depending on the choice or condition specified by the user of the application
Here you learn about data bound controls that supported by ASP.NET. You can bind the data bound controls to the data source controls to display the data. Following is a list of data bound controls:
□ The GridView Control
□ The DataList Control
□ The DetailsVlew Control
□ The FormView Control
□ The ListView Control
□ The Repeater Control
□ The DataPager Control
The data source controls allow you to work with different types of data sources, such as SQL server or an XML file. Following is a list of data source controls:
□ The SqlDataSource Control
□ The AccessDataSource Control
□ The LinqDataSource Control
□ The ObjectDataSource Control
□ The XmlDataSource Control
□ The SiteMapDataSource Control

Recursive function to find the factorial of a positive number

Recursive function to find the factorial of a positive number:

        unsigned long int rfact(unsigned long int num)
       {
         if (num==0)
          return 1;
         else
           return n*rfact(n-1);
         }

In the above function (num==0), is the base condition. When this condition is true then the recursive call is terminated. In the recursive call you can observe that every time the actual parameter value is changed. It is necessary to reach to the base condition. Suppose that the recursive function is called with a parameter 4. Then the function will return the value of the expression 4*rfact(3); it means the function rfact() is called i.e. recursion. So, the function will return the value only after getting the value of rfact() is again called with parameter 2. So, till the base condition is satisfied the same function is called again by again by changing the parameter. When the parameter is 0 for the rfact(), the call terminates by returning 1. Then one by one all the function calls will be terminated in the reverse order of their calls. This is called as backtracking. So the call rfact(0) returns 1, rfact(1) also returns 1, rfact(2) returns 2, rfact(3) returns 6, rfact(4) returns 24. 24 is the final value returned by the first function call. So the factorial value of 4 is 24.

Recursive function to find ‘gcd’ of two positive numbers:
int rgcd(int a, intb)
{
    if (a%b=0)
       return b;
    else
      rgcd(b, a%b);
  }