ACID properties in SQL

ACID stand for

1. A is stand for Atomicity
2. C is stand for Consistency
3. I is stand for Isolation
4. D is stand for Durability

Atomicity – Atomicity is the features that means all the operations in the transaction must be completed successfully and committed, if any operations become fails then all the transactions must be rolled back.

Consistency – This is the features that means the transaction must be consistent is a state, in other words the transaction must be change the state of the system for a specific operation.

Isolation - Isolation is the property that contains the features that one operation in the transaction cannot see the result of the other operation in the transaction.

Durability – It means the transaction must be durable once it has been successfully completed, it prevent from the loss of information in case of resource damage and system failure.

Find the data of the column in SQL database on the basis of one column

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace WindowsFormsApplication16

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        SqlDataAdapter da;

        DataSet ds;

        private void Form1_Load(object sender, EventArgs e)

        {

            da = new SqlDataAdapter("select * from emp", "data source=MKS-PC\\SQLEXPRESS;Initial Catalog=master;integrated security=true");

            ds = new DataSet();

            da.Fill(ds);

        }

        private void button1_Click(object sender, EventArgs e)

        {

            bool b = false;

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

            {

                if (ds.Tables[0].Rows[i][0].ToString().Trim() == textBox1.Text.Trim())

                {

                    textBox2.Text = ds.Tables[0].Rows[i][1].ToString();

                    textBox3.Text = ds.Tables[0].Rows[i][2].ToString();

                    b = true;

                    break;

                }

            }

            if (!b)

            {

                MessageBox.Show("Id does not exist");

            }

        }

    }

}

Fetch Data from SQL database in Disconnected Environment

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

 

namespace WindowsFormsApplication16

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        SqlDataAdapter da;

        DataSet ds;

 

 

        private void Form1_Load(object sender, EventArgs e)

        {

            da = new SqlDataAdapter("select * from emp", "data source=MKS-PC\\SQLEXPRESS;Initial Catalog=master;integrated security=true");

            ds = new DataSet();

            da.Fill(ds);

 

        }

        private void button1_Click(object sender, EventArgs e)

        {

            textBox1.Text = ds.Tables[0].Rows[0][0].ToString();

            textBox2.Text = ds.Tables[0].Rows[0][1].ToString();

            textBox3.Text = ds.Tables[0].Rows[0][2].ToString();

 

        }

    }

}

Output :

Validation controls in ASP.Net

In ASP.Net validation controls are used to check the information that the user enter is valid or not. There are six types of validation control are given below:

1. RequiredFieldValidation Control
2. CompareValidator Control
3. RangeValidator Control
4. RegularExpressionValidator Control
5. CustomValidator Control
6. ValidationSummary

RequiredFieldValidation Control

The RequiredFieldvalidation control is used to see the data is entered for the input control, In other words we use RequiredFieldvalidation control for mandatory field.

<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" Style="top: 100px;

        left: 300px; position: absolute; height: 30px; width: 130px" ErrorMessage="password required"

        ControlToValidate="TextBox2"></asp:RequiredFieldValidator>

CompareValidator Control

CompareValidator Control is used to compare the data is input in one input control with another input control. It is used to compare the password from one textbox to another textbox at a time of registration.

<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" Style="top: 100px;

        left: 300px; position: absolute; height: 30px; width: 130px" ErrorMessage="password required"

        ControlToValidate="TextBox3"></asp:RequiredFieldValidator>

   

RangeValidator Control

RangeValidator Control is used to check the value of the input control that is in valid range, for example minimum and maximum value of the input control.

<asp:RangeValidator ID="RangeValidator1" runat="server" Style="top: 150px; left: 300px;

            position: absolute; height: 20px; width: 90px"

        ErrorMessage="RangeValidator" ControlToValidate="TextBox4" MaximumValue="50"

        MinimumValue="20" Type="Integer"></asp:RangeValidator>

 

 RegularExpressionValidator Control

RegularExpressionValidator Control is used to check user’s input based on a pattern that you define by using a regular expression, it is used to check the valid phone number, email address, zip code etc.

 

<asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" Style="top: 200px; left: 300px; position: absolute; height: 20px; width: 160px"        ErrorMessage="RegularExpressionValidator"ControlToValidate="TextBox5"        ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"></asp:RegularExpressionValidator>

 

CustomValidator Control

  

CustomValidator Control is used to allow write a method to handle the validation of the entered value.

Validation Summery

Validation summery is used to display report of all validations error occurred in the web page.

TextBox Allow only valid decimal value in ASP.NET

In this article I am going to explain , ASP. net text only accept valid decimal value, it allow you to only number and one dot point.

JavaScript Code that allow valid decimal entry

function fun_AllowOnlyAmountAndDot(txt) {                if (event.keyCode > 47 && event.keyCode < 58 || event.keyCode == 46 || event.keyCode == 44) {                    var txtbx = document.getElementById(txt);                    var amount = document.getElementById(txt).value;                    var present = 0;                    var count = 0;                      if (amount.indexOf(".", present) || amount.indexOf(".", present + 1));                    {                        // alert('0');                    }                    do {                        present = amount.indexOf(".", present);                        if (present != -1) {                            count++;                            present++;                        }                    }                    while (present != -1);                    if (present == -1 && amount.length == 0 && event.keyCode == 46) {                        event.keyCode = 0;                        return false;                    }                    if (present == -1 && amount.length == 0 && event.keyCode == 44) {                        event.keyCode = 0;                        //alert("Wrong position of decimal point not  allowed !!");                        return false;                    }                    if (count >= 1 && event.keyCode == 46) {                          event.keyCode = 0;                        //alert("Only one decimal point is allowed !!");                        return false;                    }                    if (count >= 1 && event.keyCode == 44) {                          event.keyCode = 0;                        //alert("Only one decimal point is allowed !!");                        return false;                    }                    if (count == 1) {                        var lastdigits = amount.substring(amount.indexOf(".") + 1, amount.length);                        if (lastdigits.length >= 2) {                            //alert("Two decimal places only allowed");                            event.keyCode = 0;                            return false;                        }                    }                    return true;                }                else {                    event.keyCode = 0;                    //alert("Only Numbers with dot allowed !!");                    return false;                }            }

Function calling from textbox

<asp:TextBox ID="txtvalidenter" runat="server"  CssClass="inrinpt"  onkeypress="return fun_AllowOnlyAmountAndDot(this.id);" ></asp:TextBox>

SQL question and answer for fresher

What is SQL?
SQL is stand for structured query language designed for communicating with database

What are the different types of SQL statement?
The SQL statement is given below:
DDL – DDL is stand for Data definition language used to define the data For example Create, Alter, Drop, truncate table.
DML – DML is stand for Data manipulation language is used to manipulate data itself for example insert, update, delete, retrieving data from table.
DCL – DCL is stand for data control language is used to control the visibility of data for example granting database access and set privileges and set privileges to create table. Grant, Deny, revoke commands are used in DCL.

What is a field in the database?
A field is an area where record is kept for a specific piece of data for example client name, client address etc.

What is a record in the database?
A record is the collection of fields for example salary of an employee

What is the table in database?
A table is the collection of record for example emp table, salary table etc.

What is the difference between clustered and Non-clustered index?
Clustered index physically sort all rows while Non clustered index does not sort.
One table contain only one clustered index while in Non-clustered index there is no such restriction
In clustered index the leaf level pages are the actual data pages of the table while in Non-clustered index does not contain data pages while it contain pointer to the data pages.

 What is the difference between primary key and unique Key and Foreign Key?
Primary key and unique key both enforce uniqueness of the column, primary key create a clustered index on the column while unique key create Non clustered index on the column, the major difference between the primary key and unique key is that  primary key does not allow Nulls, while unique key allows one NULL values.
Foreign key in one table points to the primary key to the another table .

What is the difference among delete and truncate and drop commands?
Truncate
Truncate removes all rows from a table but table structure, its column, constraints, indexes do not remove
Truncate cannot be rolled back
Truncate is DDL commands
Truncate reset identity of table
Delete
Delete is used to delete rows in the table also used where clause to remove the user if we don’t use where clause then all rows will be removed
Delete commands is used with or without where clause
Delete can be rolled back
Delete is DML commands
It does not reset identity of the table
Drop
Drop commands are used to remove object from the table by using drop commands all the rows deleted and also structure of the table from database is deleted

What is the difference between the having clause and where clause?
Having clause can be used only with the SELECT statement it is used in GROUP BY clause if we don’t use GROUP BY clause HABING work just like a Where Clause. Having clause is used only with the group by function while where clause is applied to each row.

What is check constraints?
The check constraints are used to enforce domain integrity and it is used to limit the value which is placed in the column.

What is NOT NULL constraints?
Not Null constraints is just like check constraints it enforce the column will does not accept  null values.

What is the stored procedure?
Stored procedure are the group of SQL statement which run all the SQL statement in a single unit it is precompiled object if we call to stored procedure then there is no need to compile again.

What is SQL injection?
This is the problem by injecting the SQL command illegal operation can be performed the solution of the problem is that use parameterized query.

What is transaction?
Transaction is used to proceed multiple SQL statement into single logical unit.

WAP to print Fibonacci numbers below 100

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

namespace ConsoleApplication12
{
    class class1
    {
        public static int Fib(int n)
        {
            int a = 0;
            int b = 1;
      
            for (int i = 0; i < n; i++)
            {
                int temp = a;
                a = b;
                b = temp + b;
            }
            return a;
        }

        static void Main()
        {
            for (int i = 0; i < 12; i++)
            {
                Console.WriteLine(Fib(i));
                Console.ReadKey();
            }
        }
    }
}
Output :