-->

Sunday, June 29, 2014

How to apply restrictions at time of Modifying Data using Views

Views do not maintain a separate copy of the data, but only display the data present in the base tables. Therefore, you can modify the base tables by modifying the data in the view, however, the following restrictions exist while inserting, updating, or deleting data through views:

  • You cannot modify data in a view if the modification affects more than one underlying table. However, you can modify data in a view if the modification affects only one table at a time.
  • You cannot change a column that is the result of a calculation, such as a computed column or an aggregate function.

For example, a view displaying the employee id, manger id, and rate of the employees has been defined using the following statement:

CREATE VIEW vwSal AS
SELECT i.EmployeeID, i.MangerID, j.Rate FROM HumanResources.Employee AS i
JOIN HumanResources.EmployeePayHistory AS j ON
i.EmployeeID = j.EmployeeID

After creating the view, if you try executing the following update statement, it generates an error. This is because the data is being modified in two tables through a single update statement.

UPDATE vwSal
SET ManagerID = 2, Rate = 12.45
WHERE EmployeeID = 1

Therefore, instead of a single UPDATE statement, you need to execute two UPDATE statement for each table.

The following statement would update the EmployeeID attribute in the Employee base table:

UPDATE vwSal
SET ManagerID = 2
WHERE EmployeeID = 1

The following statement would update the Rate attribute in the EmployeePayHistory table:

UPDATE vwSal
SET Rate = 12.45
WHERE EmployeeID = 1

Therefore, to modify the data in two or more underlying tables through a view, you need to execute separate UPDATE statements for each table.

Creating View and Guidelines in SQL Server

Database administrator might want to restrict access of data to different users. They might want some users to be able to access all the columns of a table whereas other users to be able to access only selected columns. The SQL Server allows you to create views to restrict user access to the data. Views also help in simplifying query execution when the query involves retrieving data from multiple tables by applying joins.

A view is a virtual table, which provides access to a subset of columns from one or more tables. It is a query stored as an object in the database, which does not have its own data. A view can derive its data from one or more tables, called the base tables or underlying tables. Depending on the volume of data, you can create a view with or without an index. As a database developer, it is important for you to learn to create and manage views.

Creating Views

A view is a database object that is used to view data from the tables in the database. A view has a structure similar to a table. It does not contain any data, but derives its data from the underlying tables.

Views ensure security of data by restricting access to:

  • Specific rows of a table
  • Specific columns of a table
  • Specific rows and columns of a table
  • Rows fetched by using joins
  • Statistical summary of data in a given table
  • Subsets of another view or a subset of views and tables

Apart from restricting access, views can also be used to create and save queries based on multiple tables. To view data from multiple tables, you can create a query that includes various joins. If you need to frequently execute this query, you can create a view that executes this query. You can access data from this view every time you need to execute the query.

You can create a view by using the CREATE VIEW statement. The syntax of the CREATE VIEW statement is:

CREATE VIEW view_name
[ (column_name [, column_name]…)]
[WITH ENCRYPTION [, SCHEMABINDING] ]
AS select_statement [WITH CHECK OPTION]

Where,

  • View_name specifies the name of the view.
  • Column_name specifies the name of the column(s) to be used in a view.
  • WITH ENCRYPTION specifies that the text of the view will be encrypted in the syscomments view.
  • SCHEMABINDING binds the view to the schema of the underlying table or tables.
  • AS specifies the action to be performed by the view.
  • Select_statement specifies the SELECT statement that defines a view. The view may use the data contained in other views and tables.
  • WITH CHECK OPTION forces the data modification statements to meet the criteria given in the SELECT statement defining the view. The data is visible through the view after the modifications have been made permanent.

Guidelines for creating views

While creating views, you should consider the following guidelines:

  • The name of a view must follow the rules for identifiers and must not be the same as that of the table on which it is based.
  • A view can be created only if there is a SELECT permission on its base table.
  • A view cannot derive its data from temporary tables.
  • In a view, ORDER BY cannot be used in the SELECT statement.

For example, to provide access only to the employee ID, marital status, and department ID for all the employees you can create the following view:

CREATE VIEW HumanResources.vwEmployeeDepData
AS
SELECT e.EmployeeID, MaritalStatus, DepartmentID
FROM HumanResources.Employee e JOIN
HumanResources.EmployeeDepartmentHistory d
ON e.EmployeeID = d.EmployeeID

The preceding code crates the vwEmployeeDepData view containing selected columns from the Employee and EmployeeDepartmentHistory tables.

Saturday, June 28, 2014

Returns Radio Button Input element using Html.RadioButton() handler: MVC

Html.RadioButton() handler, used to return radio button input element to be input true or false value by user. Checked radio button will return true and un-checked will return false, otherwise it returns null to store/use the value.

Html.RadioButton() handler method is used to present mutually exclusive option i.e. true of false. Using this radiobutton method makes it easy to bind to view data or model is so easy in compare to using simple input radio element. This handler provides mostly same features as Html.CheckBox() handler discussed earlier.

Parameters


  • htmlHelper: specify html helper instance that this method extends.
  • name: specify name of input element used to access the value in controller.
  • value: specify value of radio button element. If none is assigned then this attribute is used to access the value.
  • isChecked: to be set true of false for radio button. True to select the element, OW false.
  • htmlAttributes: specify an object that contains html attributes to set for the element.
     e.g Html.RadioButton(“gender”)

Html.RadioButtonFor(…)

Having the same functionality as Html.RadioButton() but it returns radio button element for each property passed via the model from controller’s action. This handler have two types of type parameters i.e. TModel (specify type of model) and TProperty (specify type of value).

Parameters

  • htmlHelper: specify html helper instance that this method extends.
  • expression: used to identifies object containing the property to render.
  • value: specify value of radio button element. If none is assigned then this attribute is used to access the value.
  • htmlAttributes: specify an object that contains html attributes to set for the element.

e.g Html.RadioButtonFor(model=>model.Gender)

Render radio button input for the property gender passed via controller. Submitting form will assign the value of this radio button to model’s gender property to be accessed in the controller’s action.

Returns Password input element using Html.Password() Handler: MVC

Html.Password() handler, used to return password input element to be input password value by user. This article describes about to using password html helper that will make text unreadable by other users in MVC application.

Like all other input elements, using Html.Password() html handler users are not able to read input value. This handler is mostly used in login process of any ASP.Net MVC application because of some special features of this helper.

This helper commonly have following features:

  • Text input in the control is un-readable.
  • Does not allow user to copy the text input in the control.
  • Does not repopulate its value from ModelState object.

Parameters


  • htmlHelper: html helper instance that this method extends.
  • name: used to specify the name of form field that may be used to look up the value.
  • value: specify the value of this element.
  • htmlAttributes: specify html attributes to be set for the element like placeholder, id, any class or other values.
Overall this will generate an input element having the type password on the page:
<input type=”password” runat=”server” id=”password” />

Html.PasswordFor(…)

Provide same functionality but used to render password input element for each property passed via model. Here the model is the class for which the view is to be created. This handler have two type parameter i.e. TModel (specify type of model) and TProperty (specify type of value).

Parameters


  • htmlHelper: html helper instance that this method extends.
  • expression: used to identifies object that contains property to render.

Html.PasswordFor(model=>model.Password)

this line will render an input element of type password having id "Password" passed by the model. After submitting form, the value of this element will assigned to the password field of the model and can be easily accessed for further use.

Wednesday, June 25, 2014

How to find no of days between two different date objects in asp.net

We have already discussed about that article , but today we add two months in current date time object , after that i will find how many days remaining from the current date and time. Lets take an simple example
<form id="form1"
     runat="server">
    <asp:Button ID="Button1"
    runat="server"
    onclick="Button1_Click"
    Text="Click"
    Width="71px" />
    <div>  
    <asp:Label ID="Label1"
    runat="server"
    BackColor="Yellow"
    Text="Label"></asp:Label>  
    </div>
    </form>
Code Behind
 protected void Button1_Click(object sender, EventArgs e)
        {

            DateTime today = DateTime.Today;
            Label1.Text = "today : " + today.ToLongDateString();
            DateTime twoMonthLater = today.AddMonths(2);
            TimeSpan TS = twoMonthLater - today;
            int days = TS.Days;
            Label1.Text += "<br ><br />after two month: ";
            Label1.Text += twoMonthLater.ToLongDateString();
            Label1.Text += "<br ><br />This is difference between to datetime object in days:";
            Label1.Text += days;
        }

  Code Generate the following output
How to find no of days between two different date objects in asp.net

Friday, June 20, 2014

C# - Arrays

Introduction

If we create a variable of type int also assign 10 as value in it. If we want to store more than one value in it then its not possible. That why array comes to the picture, If you want to store similar type and more then one value into it then should take array.
Array Definition 
"An array is a collection of similar data type or you can say a single variable hold multiple other variables, those variable known as label of array. Lets take an simple example, suppose you have two things, which are ball and bat and you want to store it, use a box. Put the both things in separate boxes, also with label. Here 'array' is a box and label is 'variable name'.
Basically array consists of two things first one is index and second one is value, If you want to access specific value in an array, use index. When we insert items into an array, default index started from 0. You can insert items randomly at any index position. Lets take an example for clarification that how to insert item into an array. Before learn this example, first should take array declaration.

Syntax of declaring array


datatype [] identifier;

where, you can use any data type to declare an array like int , float, double, string etc. This bracket [] known as rank of the array, its doesn't take decimal type value , always take integer. The value in the rank known as size of the array. Suppose i take 10 in the rank, then array index start from 0 to 9. Here 0 is the starting index and 9 is the last index. All arrays value consists of contiguous memory locations. The lowest address corresponds to the first element and the highest address to the last element.
Lets more about an array that is how to initialize array variable. 
Array initialization means you can assign some values in to the array. 
Before initialization, should take new keyword for create instance of the array because array is a reference type. For example

int[] employee_age = new int[10];

In above mentioned example array hold 10 elements, means size of the array is 10. If you want to assign value in it, you can use index number. Like

employee_age[0] = 20;
employee_age[1] = 40;

Also You can assign values to the array at the time of declaration, like:

int [] employee_age = {20,21,22,23,24};
You can also create and initialize an array, like:
int [] employee_age = new int[5]  {20,21,22,23,24};
In the preceding example you can change the size of element, directly you can say re-sizing of an array. Also you can copy one array to another array in c#. In that case both array would locate to same memory location. like takes an simple example

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

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {

            int[] employee_age = new int[5]{20,21,22,23,24};
            int[] second_array = employee_age;
            int i = 0;
            foreach (var item in second_array)
            {
                Console.WriteLine("arrays elements are[{0}]={1}",i,item);
                i++;
            }
            Console.ReadKey();
        }
    }
}

Code generate the following output
How to copy of one array element to another array element
Above mentioned example covers declaring of an array, initialization of array and how to access array element in c#.
In c#, when we create a new array in the memory then compiler automatically initialize all index value with 0. Take a look.

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

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {

            int[] employee_age = new int[5];
            int[] second_array = employee_age;
            int i = 0;
            foreach (var item in second_array)
            {
                Console.WriteLine("arrays elements are[{0}]={1}",i,item);
                i++;
            }
            Console.ReadKey();
        }
    }
}
Code generate the following output
c# array initialize with default value 0

Monday, June 16, 2014

C# - Variables

A variable is everything in programming, in which we can store values also change it during program execution. Before declare a variable in c#, must use type of it, so you say Each variable in C# has a specific type. You can perform multiple operation on a single variable, which value stored in memory. Now in this topic we will cover
1. How to declare a variable
2. How to internalize a variable.
3. Types of a variable

1. How to declare a variable
 during the declaration of a variable, we use type of it. Through comma separation we declare multiple variable with single type. Lets take a simple syntax with example.

Syntax of single variable declaration 
<data_type> single_variable_declaration;
Example of single variable declaration
int Employee_age;

Syntax of list of variable declaration
<data_type> variable1, variable2, .....variableN;
Example of list of variable declaration
String Employee_Name, Employee_address, Employee_Country;

2. How to initialize a variable
The meaning of initialization is assigning some value into the variable. There are two types of initialization, first one is declaration time initialization, in which we can assign some value into the variable at declaration time. Lets take simple example
int employee_age=24;
In this example we are passing value(24) from the right side to left side. Also here we use assignment operator, In later session we will discuss about operators.

The last one is after declaration, in which we can pass the value into the variable after declaration of it. Like
int a,b,c; // Declaration over
a=10 ; // after initialization 
b=20 ; // after initialization 

c=a+b; // expression initialization

3. Types of a variable
In the c#, there are two types of variable, first one is local variable and second one is global variable. If we initialize a variable outside the block known as global variable you can consider public specifier as a example of it. If we initialize it inside the block known as local variable. lets take a simple example.

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

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {

            int a = 10;
            {
                int b;
                a = 20;
                Console.WriteLine(a);
            }
            b = 30;  // b is a local variable , can't initialize here. 
            Console.WriteLine(a);
            Console.ReadKey();
        }
    }
}
In this program we get error because a local variable can't initialize outside the block.

In this program we get error because a local variable can't initialize outside the block.
© Copyright 2013 Computer Programming | All Right Reserved