The SELECT Query

The SELECT clause selects or returns the number of records from the table. But some times we want to return only some of the records rather than all the records in the table.
To do it , we have the clause SELECT TOP using which we can specify the number of records to return.
The SELECT TOP syntax varies with the Databases. i.e the syntax is different for MySQL, SQL Server and Oracle.

Syntax (For SQL Server)

SELECT TOP number column_name FROM
table_name

Example(For SQL Server)

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

Suppose we want to return the first two records from the above table.

SELECT TOP 2 * FROM
tbl_Record

Output

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4

If you want the selected number of columns , then you can specify the name of the columns you want to return.

SELECT TOP 2 ID,Name FROM
tbl_Record

Output

ID	Name
1	Anoop Sharma
2	Ankit Pandey

Syntax (For MYSQL Database)

SELECT column_name FROM
table_name LIMIT number

Example(For MYSQL Database)

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

Suppose we want to return the first two records from the above table.

SELECT * FROM
tbl_Record LIMIT 2

Output

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4

If you want the selected number of columns , then you can specify the name of the columns you want to return.

SELECT ID,Name FROM
tbl_Record LIMIT 2

Output

ID	Name
1	Anoop Sharma
2	Ankit Pandey

Syntax (For Oracle Database)

SELECT column_name FROM
table_name WHERE ROWNUM<=number

Example(For Oracle Database)

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

Suppose we want to return the first two records from the above table.

SELECT * FROM
tbl_Record WHERE ROWNUM<=2

Output

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4

If you want the selected number of columns , then you can specify the name of the columns you want to return.

SELECT ID,Name FROM
tbl_Record WHERE ROWNUM<=2

Output

ID	Name
1	Anoop Sharma
2	Ankit Pandey

Using WHERE

You can also use WHERE condition with the SELECT TOP clause to filter the records based on specific condition.


Syntax(For SQL Server)

SELECT TOP number column_name FROM
table_name WHERE condition

Example (For SQL Server)

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

SELECT TOP 2 * FROM tbl_record
WHERE Experience=3

Output

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3

Using Order BY Clause

We can also use Order By clause with the SELECT TOP clause to specify the order by which the records to be returned.

Syntax(For SQL Server)

SELECT TOP number column_name FROM
table_name ORDER BY asc/desc

Example (For SQL Server)

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

SELECT TOP 2 * FROM tbl_record
ORDER By ID desc

Output

ID	Name	State	JoinDate	Experience
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3

Operators Precedence in C#

Precedence stands for the priority. Operator precedence determines the output of the expression.

Below are the operators accordings to categories.

Primary Operators

Primary operators has the highest precedence in C#. The associativity of the primary operators is from left to right. The primary operators are

Symbol	Operator	Description	Associativity
a++,a--	Post Increment and Post decrement	Increment or Decrement the original value of operand.	Left To Right
X.a	Accessing member of class.	accesing variable a with object X	Left To Right
X(a)-	Method Invocation	Calling method (a) with object X	Left To Right
X[a]	Element or variable access.	Accessing index 'a' with Array 'X'	Left To Right
new	Object Creation	Using new to create object of class.	Left To Right
typeof	type of datatype	To find the Datatype of Object ot Variable	Left To Right
checked, unchecked			Left To Right

Unary Operators

Unary Operators are the operators who work with only one operand. The operators has the right to left Associatiity. Below are the Unary Operators.

Symbol	Operator	Description	Associativity
+a,-a	Unary Plus and unary minus operator	Unary Plus and unary minus operator	right to left
++a,--a	Preincrement , predecrement	Preincrement , predecrement	right to left
!a	Logical Not	Reverse the value of the variable	right to left
~a	Bitwise Not	Reverse the value of the bit	right to left

Multiplicative Operators

Multiplicative Operators perform multiplication or division. They possess left to right associativity.

Symbol	Operator	Description	Associativity
*	Multiplication	Multiply two operands	left to right
/	Division	Divide two operands	left to right
%	Modulus	To calculate reminder of division	left to right

Additive Operators

Additive operators perform addition and subtraction. They posses left to right associativity.

Symbol	Operator	Description	Associativity
+	Addition	Addition of two numbers	Left To Right
-	Subtraction	Subtraction of two operands	Left To Right

Shift Operators

Shift Operators are used to shift the bits either right or left. Shift operators used with bitwise operators.

Symbol	Operator	Description	Associativity
<<	Left Shift	Shift one bit to the left	Left To Right
>	Right Shift	Shift one bit to right	Left To Right

Relational and Type Testing Operators

Relational and Type Testing Operators are used to compare two operands/variables and testing the operands/variables for diffrerent conditions i.e equality, greater than, less than etc.

Symbol	Operator	Description	Associativity
<	Less than	To test if one operator/variable is less than other	Left To Right
<=	Less than or Equal To	To test if one operator/variable is less than or equal to other	Left To Right
>	Greater Than	To test if one operator/variable is greater than the other	Left To Right
>=	Greater Thanor Equal to	To check whether one perator/variable is Greater Than or Equal to other	Left To Right

Equality Operators

To check whether two operands/Variables are equal or not , Equality operators are used. Equality operators have Left To Right Associativity.

Symbol	Operator	Description	Associativity
==	Is equal To	To Check whether one operand/Variable is equal to other	Left To Right
!=	Is not equal To	To Check whether one operand/Variable is not equal to other	Left To Right

Logical Operators

Logical operators used to compare two operand and the result/output of expression using logical operators is boolean format. They possess Left to Right Associativity. For example IF(a&&b==0) then...

Symbol	Operator	Description	Associativity
&&	Logical AND	Returns True when both the operands returns true	Left to Right
||	Logical OR	Returns True when one of the operands returns true	Left to Right
^	Logical XOR	XOR operation between two operands	Left to Right

Conditional Operators

Conditional Operators executes expression and select the value between two operands/variables based on output of expression.

Symbol	Operator	Description	Associativity
?:(x?y:z)	Conditional	Executes operand y if x is true otherwise evalutes z	Right To left

Agreegate Functions- MIN() and MAX(), AVG(), SUM() and COUNT()

There are some functions in SQL having special use and functionality to perform some operations. The aggregate functions perform operation on a set of values and return a single value. The aggregate function ignore all null values. Aggregate functions usually combines with GROUP BY clause.

• MIN()
The MIN() function is used to return smallest value of the column selected.

Syntax


SELECT MIN(column_name)
FROM table_name WHERE condition


Example




ID	Name	State	JoinDate	Experience
1	Anoop	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Pune	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-07-11 10:03:30.577	2
4	Arjit singh	Punjab	2017-07-11 10:03:30.577	7



Suppose We want to find or select the candidate having less experience among all other candidates


SELECT MIN(Experience) AS Experience
FROM EmployeeDetails


Output


Experience
       2

• MAX()
The MAX() function is used to return largest value of the column selected.

Syntax


SELECT MAX(column_name)
FROM table_name WHERE condition


Example




ID	Name	State	JoinDate	Experience
1	Anoop	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Pune	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-07-11 10:03:30.577	2
4	Arjit singh	Punjab	2017-07-11 10:03:30.577	7



Suppose We want to find or select the candidate having most experience among all other candidates


SELECT MAX(Experience) AS Experience
FROM EmployeeDetails


Output


Experience
       7

• COUNT()
The COUNT() Function returns the number of Rows Affected by SQL Query that matches the given condition.

Syntax


SELECT COUNT(column_name)
FROM table_name
WHERE condition


Example




ID	Name	State	JoinDate	Experience
1	Anoop	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Pune	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-07-11 10:03:30.577	2
4	Arjit singh	Punjab	2017-07-11 10:03:30.577	7




SELECT COUNT(Experience) As Experience
FROM EMployeeDetails


Output


Experience
4


Now look at another example. We Have added a new Row in the EmployeeDetail Table and Will use COUNT() Function on the State Column of the EmployeeDetail Table.


ID	Name	State	JoinDate	Experience
1	Anoop	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Pune	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-07-11 10:03:30.577	2
4	Arjit singh	Punjab	2017-07-11 10:03:30.577	7
5	Tarun Sharma	Maharashtra	2017-08-11 10:15:25.000	5




SELECT COUNT(State) As StateCount
FROM EMployeeDetails


Output


StateCount
5

• AVG()
The AVG() Function is used to find out the Average value of the perticular nummeric column in SQL Table.

Syntax


SELECT AVG(column_name)
FROM table_name
WHERE condition


Example




ID	Name	State	JoinDate	Experience
1	Anoop	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Pune	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-07-11 10:03:30.577	2
4	Arjit singh	Punjab	2017-07-11 10:03:30.577	7
5	Tarun Sharma	Maharashtra	2017-08-11 10:15:25.000	5




SELECT AVG(Experience) As AvrageExperience
FROM EmployeeDetails


Output


AvrageExperience
4.200000

• SUM()
The SUM() function is used to return the sum of the column of the table. The column should be the integer or numeric column.

Syntax


SELECT SUM(column_name) FROM table_name
WHERE condition


Example




ID	Name	State	JoinDate	Experience
1	Anoop	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Pune	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-07-11 10:03:30.577	2
4	Arjit singh	Punjab	2017-07-11 10:03:30.577	7
5	Tarun Sharma	Maharashtra	2017-08-11 10:15:25.000	5




SELECT SUM(Experience)
FROM EmployeeDetails



Output
SUMExperience
21

Java Program To Insert Value into Database/Table.

Below is the Java Program which inserts values into MySQL Database Table.


The import statement imports the SQL classes required to connect to database and perform insert operation.

import java.sql.*;
public class Insert_Values {

public static void main(String[] args)
{

try
      {
Class.forName("com.mysql.jdbc.Driver");

try
      {

String query="Insert into test_1 (Id,Name) values('2','Karan')";

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root@123");

Statement stmt=con.createStatement();

int res=stmt.executeUpdate(query);

System.out.println("Records Inserted");

}
       catch (SQLException e)
       {
e.printStackTrace();

}

   }
   catch (ClassNotFoundException e)
   {

e.printStackTrace();

}
  }
}

Output

Records Inserted.

Check program by manually firing select query on database.

Explaination

• import java.sql.*;- This statement imports sql package so that we can use it's classes and methods for interacting with Database. Java.sql package contains large number of classes and methods with the help of which we can perform various operations with Database.

---

• public class  Insert_Values- is the name of the class.

---

• public static void main(String[] args)  - is the main() method . i.e entry point for every JAVA Program.

---

• Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root@123");-  We have created a object of Connection class using static method getConnection(); passing the parameters the database server name jdbc:mysql hostname as localhost, database name as test , user id root and password root@123.

---

• String query- Variable which stores the SQL query to Execute.

---

• Next to execute sql query we have to create a Statement object using Statement Class. There is a createStatement() method in Connection class.

---

• The ResultSet class is the class having methods to store the output of the executed query. We created object of ResultSet class passing the query using Statement object and executeQuery() method.

---

• Finally the try..catch is used to find the catch any error like SQLException or ClassNotFoundException.

Java Program-Selecting values from Database

Below is the Program which connects to database MySQL and select values from the table and displays it on the screen. Note that you must have created database and tables in MySQL Database.

import java.sql.*;
public class ConnectDatabase {

public static void main(String[] args) {

try
  {

Class.forName("com.mysql.jdbc.Driver");

try
  {

Connection            con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root@123");

System.out.println("Connected");

Statement stmt=con.createStatement();

ResultSet res=stmt.executeQuery("select * from test_1");

while(res.next())

{

System.out.println(res.getInt(1)+"  "+res.getString(2));


}

}
   catch (SQLException e)
   {


e.printStackTrace();

}

}
   catch (ClassNotFoundException e)
   {

e.printStackTrace();

}

}


}

Output

Connected.

1 vishal

Explaination

• import java.sql.*;- This statement imports sql package so that we can use it's classes and methods for interacting with Database. Java.sql package contains large number of classes and methods with the help of which we can perform various operations with Database.

---

• public class ConnectDatabase- is the name of the class.

---

• public static void main(String[] args)  - is the main() method . i.e entry point for every JAVA Program.

---

• Class.forName("com.mysql.jdbc.Driver");-  The 'Class' is the class which is present in Java.lang package. forName is the method which returns the class for the parameter that was passed in class loader. And 'com.mysql.jdbc.Driver'  this statement is found in class path. It simply means that the driver is registered with JDBC DriverManager.

---

• Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root@123");-  We have created a object of Connection class using static method getConnection(); passing the parameters the database server name jdbc:mysql hostname as localhost, database name as test , user id root and password root@123.

---

• Next to execute sql query we have to create a Statement object using Statement Class. There is a createStatement() method in Connection class.

---

• The ResultSet class is the class having methods to store the output of the executed query. We created object of ResultSet class passing the query using Statement object and executeQuery() method.

---

• The query select * from test_1 will select all the records from the test_1 table and will be stored in ResultSet object. Then using the while loop we iterate through the records using next() method of ResultSet Class. In the loop , According to the datatype of the column of table in database, we print the value on the console. i.e res.getInt(1) prints the integer value of the first column. The (1) means the index of the column. Similarly we use the res.getString(2) to print the string value name in the second column of the table.

---

• Finally the try..catch is used to find the catch any error like SQLException or ClassNotFoundException.

Java Program to Connect To Database.

Below is the Java Program To connect to Database

Here the DataBase used is Mysql database Steps to follow

• Download the Mysql connector from the internet.

• import it in your project.

• Right click on project->Properties->Java build path.

• In libraries select Add external jars on the right side menu.

• select and add mysql connector that you downloaded.

• Go to tab order and export and select the mysql connector checkbox.

import java.sql.*;
public class ConnectDatabase {

public static void main(String[] args) {

Class.forName("com.mysql.jdbc.Driver");

try
   {

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root@123");

System.out.println("Connected");

}
    catch (SQLException e)
   {

System.out.println("Bad Connection Request");

}


}


}

Output.

Connected.


Explaination

• com.mysql.jdbc.Driver- driver class for Mysql;

• DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root@123");-

Here jdbc is API, mysql is database, localhost is the server name. It can also be an IP address, 3306 is the port number. And root is username , root@123 is a password.

C Sharp ADO.NET - Getting to know Dataset , Datatable objects.-And GridView

DataTable

DataTable is nothing but a representation of Database Table in Memory. Using the DataTable , we can retrieve the table from the Database into DataTable and can interact or perform operations on it without having to connect to Database every time. DataTable is an exact representation of the Table in Database. Just like Table in SQL DataTable also has Rows and Columns. It is an virtual representation of Table in SQL to make it easy to perform operations.

DataSet

DataSet can be said as collection of the DataTables. DataSet is an Connectionless entity. We can retrieve the Table from the SQL Server into the DataTable and store this DataTable into DataSet.DataSet is also an Virtual Representation of the Collection of the DataTables. In this post we will be studying C Sharp ADO.NET objects i.e Dataset , Datatable and storing value in datatable
In the green colors , the comments are written which describes what the syntax or line for.

using System;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

namespace DatatablePro

{

           class Program

           {

                   static void Main(string[] args)

                  {

                              DataSet ds = new DataSet();

                              DataTable dt = new DataTable();

                            //Giving name to datatable
                              dt.TableName = "mytable";

                            //adding datatable to dataset
                              ds.Tables.Add(dt);

                            //gets name of datatable
                              string name=dt.TableName;

                             //Creating DataColumn Objects
                               DataColumn dc = new DataColumn();

                               DataColumn dc1 = new DataColumn();

                             //Giving name to columns
                               dc.ColumnName = "ID";

                               dc1.ColumnName = "Name";

                             //adding datacolumns to datatable
                               dt.Columns.Add(dc);

                               dt.Columns.Add(dc1);

                             //creating DataRow object
                               DataRow dr = dt.NewRow();

                             //Storing value in columns
                               dr["ID"] = 1;

                               dr["Name"] = "Karan";

                             //adding row to datatable
                               dt.Rows.Add(dr);

                             //displaying values
                               Console.WriteLine("name of datatable is {0} ", name);

                               Console.WriteLine("Value in Datatable \n ID \t Name \n {0} \t {1}",dt.Rows[0]["ID"],dt.Rows[0]["Name"]);

                               Console.ReadKey();

                  }

          }

}

Output

In this post we will be studying how to bind the DataTable to GridView. GridView is an asp.net object which displays the data in Grid Format. For this , We will be creating ASP.NET form and C Sharp code to the background. You will learn from the basic how to Add ASP.NET form and the code in this post.

Follow the below steps .

Open the Visual Studio.

Click on the New Project.

Select the Visual C #->Web->ASP.NET Empty Web Application as shown in image.

This will open Empty ASP.NET web Application Project.

Open the Solution Explorer on the right side of the Project.

Right click on it. Select Add->Web form as shown in image.

Write the below code.

First in the Web Form.

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" Width="200px" Height="100px"></asp:GridView>
    </div>
    </form>
</body>
</html>

Then right click in the page and select View Code

Write the below code to the page

using System;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

namespace bindwithgrid

{

          public partial class WebForm1 : System.Web.UI.Page

          {

                    DataSet ds = new DataSet();

                    DataTable dt = new DataTable();

                    protected void Page_Load(object sender, EventArgs e)

                   {

                           PopulateDatatable();

                           GridView1.DataSource=ds.Tables[0];

                           GridView1.DataBind();

                   }

                   public void PopulateDatatable()

                  {

                         //Giving name to datatable

                          dt.TableName = "mytable";

                        //adding datatable to dataset

                          ds.Tables.Add(dt);

                       //gets name of datatable

                         string name = dt.TableName;

                       //Creating DataColumn Objects

                         DataColumn dc = new DataColumn();

                         DataColumn dc1 = new DataColumn();

                       //Giving name to columns

                         dc.ColumnName = "ID";

                         dc1.ColumnName = "Name";

                       //adding datacolumns to datatable

                         dt.Columns.Add(dc);

                         dt.Columns.Add(dc1);

                       //creating DataRow object

                         DataRow dr = dt.NewRow();

                       //Storing value in columns

                         dr["ID"] = 1;

                         dr["Name"] = "Karan";

                       //adding row to datatable

                         dt.Rows.Add(dr);

          }

   }

}

Go to Debug-> Start Debugging or press F5 on keyboard

Output

C Sharp Program to Connect to Database-Insert Value in Database Table

As you know, The Application softwares have front end and back end.Many of the applications , windows or web requires Database to store records. The front end is the User Interface using which Clients or Users use the Applications and the Back end is the one who provides the data or stores the data. In the Web or Windows Applications coded in C# also has the back end associated to provide the data and information or to store the data. It is very necessary for a program to interact with the database. C# ADO.net provides the classes and methods to connect to database. Interacting with the database is very simple in C#. Let's have a look at below program for better understanding.

Below is the C Sharp Program to Connect to Database.

The Database used here is the Microsoft SQL Server 2014.

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

namespace Connect_Databse

{

           class Program

          {

                   static void Main(string[] args)

                   {

                           string connectionstring = "data source=VISHAL-                                                                                     PC\\SQL2014;database=Test;uid=sa;password=ka123";

                           SqlConnection con = new SqlConnection(connectionstring);

                           con.Open();

                           Console.WriteLine("Connection opened");

                            Console.ReadKey();

                   }

            }

}

OutPut.

Connection opened.

Explanation

Using System- The System is the namespace containing all of the basic important classes and methods.

Using System.Data.Sqlclient- This namespace contains all the required classes to interact with the database.

Class Program- is the name of the class.

static void main(String [] args)- The main methods where the Console Program starts executing.

string connectionstring- is the variable to store the connection to Database.

Data Source- is the name of the Database Server.

Database- Name of the database to interact with.

uid- User id to connect to Database.

password- password required for authentication.

SqlConnection con=new SqlConnection(connectionstring)-Sqlconnection is the name of the class which is used to establish connection and interact with the Database. We passed the connectionstring to SqlConnection Constructor indication the Database path to connect with.

con.open()-Method of sqlconnection class to open the connection.

Using Console.WriteLine to make sure that we are connected to database.

Above we saw that how can we connect to Database using ADO.NET . Now we will try to interact with the database. There are also number of classes and objects and methods in ADO.NET which are used to perform operations on database like Creating Table , Inserting values in the Table , Deleting values, Updating values Etc. Let's write the program to insert values into database Table.
Below is the program to insert value into table using C Sharp ADO.Net properties.

using System;

using System.Data.SqlClient;
namespace insert_value

{

      class Program

     {

            static void Main(string[] args)

            {

                    string connection = "data source=VISHAL- PC\\SQL2014;database=Test;uid=sa;password=sa@123";

                    SqlConnection con=new SqlConnection(connection);

                    con.Open();

                    SqlCommand cmd=new SqlCommand("Insert into WinForm_1 (Name,Age)                      values('vkk','20')",con);

                   cmd.ExecuteNonQuery();

                   Console.WriteLine("Record inserted");

                   Console.ReadKey();

           }

      }

}

Output.

Before inserting.

After inserting.

Explanation

Using System- The System is the namespace containing all of the basic important classes and methods.

Using System.Data.Sqlclient- This namespace contains all the required classes to interact with the database.

Class Program- is the name of the class.

static void main(String [] args)- The main methods where the Console Program starts executing.

string connectionstring- is the variable to store the connection to Database.

Data Source- is the name of the Database Server.

Database- Name of the database to interact with.

uid- User id to connect to Database.

password- password required for authentication.

SqlConnection con=new SqlConnection(connectionstring)-Sqlconnection is the name of the class which is used to establish connection and interact with the Database. We passed the connectionstring to SqlConnection Constructor indication the Database path to connect with.

con.open()-Method of sqlconnection class to open the connection.

SqlCommand cmd=new SqlCommand(Querey)-Sqlcommand is the Class in ADO.NET with the help of which we can pass the queries to Database. The Default constructor of the SqlCommand Accepts the Query to be passed to SQL Server to perform operation on the Database Table.

cmd.ExecuteNonQuery- Method to execute the query passed to Database via SqlCommand Object.

INSERT INTO/UPDATE TABLE/DELETE TABLE

In this post We will study how to insert records into table,How to delete and how to update records into table using INSERT , UPDATE and DELETE statement. All the below queries are performed in SQL server 2014.

Syntax

INSERT INTO table_name(column_1,column_2)
VALUES(value_1,value_2)

Example

SELECT * from [dbo].[WinForm_1]

Output

INSERT INTO WinForm_1 (Name,Age)
VALUES ('Raj','21').

Output

Note:Here the column ID is the PRIMARY key with AUTO INCREMENT value . PRIMARY  key is the value used to identify records uniquely. When the new record is inserted in table , the value of the ID Attribute is automatically increased to next value. This is done with the help of AUTO INCREMENT Feature.We will learn more about Keys in next coming posts.
We can also omit the column names when we have to enter values in all columns.

Example

INSERT INTO Winorm_1 values('4','karan','32')

Now we will be studying about updating table/value in table

The update table command updates the one or more values in table.

Syntax

UPDATE table_ name
SET column_1=value_1 where condition

---

Example

See below table we will be updating name value from Raj to Karan.

UPDATEV[WinForm_1]
SET Name='karan' where ID=4.

Output

The value is changed from Raj to Karan.

UPDATE command should be used very carefully. If by any means the where condition is missed , this command will update all the records in the table.


You can also update multiple values by using below syntax.

UPDATE table_ name
SET column_1=value_1 column_2=value_2 where condition......

Example

UPDATE WinForm_1
SET name ='vk' , age='33' where ID=4

The DELETE statement in SQL is used to delete the records from the SQL table.Please note that there is difference between DELETE and TRUNCATE statement. The DELETE statement deletes the no of records from the Table according to given condition. But the TRUNCATE statement deletes all the records/ rows from the table. The TRUNCATE table cannot be used with  WHERE condition.

Syntax

DELETE FROM table_name
WHERE condition

---

IMP: While deleting records from table , look always for WHERE condition. The WHERE clause specifies which records should be deleted. If you Omit the WHERE condition , all the records from the table will be deleted.

ID	Name	State
1	Anoop	Delhi
2	Ankit Pandey	Pune
3 4	Gaurav Kapoor Arjit Singh	Delhi Punjab

Consider Above table.

Example

DELETE from Detail
WHERE ID=3

Executing above query will delete third row from the table.

Also you can use the string in WHERE condition to delete specific record. See the below example.

DELETE from Detail
WHERE Name='Anoop'

Executing above query will delete first row from the table.

Deleting all records from the table.

To delete all the rows from the table , we use the following SQL query.

DELETE * from table_name

Above query will delete all records from the table, but the table structure and the schema will remain the same. 
Another alternative command to delete entire records from table, is the TRUNCATE TABLE command. 

Syntax

TRUNCATE TABLE table_name

Example

TRUNCATE TABLE Details

Executing above commnd will delete all the records from table and table structure and schema will also reset.

ALTER TABLE/ORDER BY CLAUSE

In this post , we will be knowing use of ALTER command on table. The ALTER command is used to change the structure of the table. For ex Adding a column, Deleting a column, Or Changing the Data Type of the column etc.

• Changing data type of column.

In above figure , we see that the data type of age is int. Let's change it to varchar(10)

ALTER TABLE Winform_1
ALTER column Age varchar(10)

Data type of Age is changed.

The ALTER table syntax is different for MYSQL , DB2 etc.

---

• Adding column to table.

To add the column to the table , following ALTER command is used.

ALTER TABLE WinForm_1
ADD address varchar(20)

Column address is added.

---

• Dropping a column.

Using ALTER command , we can also drop or delete column from the table. See the below command to know how can we do it.

ALTER table WinForm_1
DROP column address

In this post, we will be studying order by clause. The order by is used to select data from database in particular order or particular column.

---

Syntax

SELECT column_name1,column_name2 from table_name
ORDER BY column_name ASC|DESC

Note- If you omit ASC keyword from order by clause , it will sort in ascending order by default.

---

ID	Name	City	State
1	Raj	Mumbai	Maharashtra
2	Rahul	Nashik	Maharashtra
3	Saurabh	Nagar	Maharashtra
4	Ryan	panaji	Goa

Example

SELECT * from Detail ORDER BY ID desc

Output.

ID	Name	City	State
4	Ryan	panaji	Goa
3	Saurabh	Nagar	Maharashtra
2	Rahul	Nashik	Maharashtra
1	Raj	Mumbai	Maharashtra

We can also use multiple columns in order by clause.
For example, Consider the following table.

ID	Name	State
1	Anoop	Delhi
2	Ankit Pandey	Pune
3 4	Gaurav Kapoor Arjit Singh	Delhi Punjab

Example

SELECT * FROM [dbo].[tbl_Record] order by Name,State

Output

ID	Name	State
1	Anoop	Delhi
2	Ankit Pandey	Pune
3 4	Gaurav Kapoor Arjit Singh	Delhi Punjab