Correlated SubQuery...

What Is Correlated Subquery...??
If the Subquery depends on the outer query for its values than that Subquery  is called Correlated Subquery

Example:

First of create  2 Table Like

(1) Table Tbl_Product
create table Tbl_Product
(
    Id int identity ,
    Name varchar(500)
)
(2)Table Tbl_Product_Sales
 create table Tbl_Product_Sales
(
    Id int identity,
    ProductId int,
    Quntity int
)

Now Insert Record Into Table Like.

Insert Record As per Image Given Below Into Tbl_Product ..

Insert Record As per Image Given Below Into Tbl_Product_Sales ..

Now, How to Write SubQuery......(Show Example1, Example2)

Example1:-
select Id , Name  from
Tbl_Product where Id NOT IN (select distinct ProductId   from Tbl_Product_Sales )

Example2:-
select Name ,
(select SUM (Quntity)  from Tbl_Product_Sales where ProductId  = Tbl_Product .Id  )as TotalQuantity
 from Tbl_Product

Enjoyyyyyyyyyyyyyyyyyyy......
By:- Arvind Kataria.

Insert Update Delete in Repetar in asp.net...........

First create Table 

Now Design  Web-Form like..

Design file code(.aspx file)


<div>
    <table>
    <tr>
        <td>Name:</td>
        <td><asp:TextBox runat="server" ID="txtname"></asp:TextBox></td>
    </tr>
    <tr>
        <td>Email:</td>
        <td><asp:TextBox runat="server" ID="txtemail"></asp:TextBox></td>
    </tr>
    <tr>
        <td colspan="2" align="center"><asp:Button  runat="server" ID ="btnsubmit"
                Text="Submit" onclick="btnsubmit_Click"/></td>
    </tr>
    </table>
     <asp:Repeater ID="Repeater1" runat="server" onitemcommand="Repeater1_ItemCommand">
        <HeaderTemplate>
            <table>
                <tr>
                    <th align="left" width="250px" class="name">
                    </th>
                    <th align="right" width="250px" class="size">
                    </th>
                </tr>
            </table>
        </HeaderTemplate>
        <ItemTemplate>
            <table>
                <tr>
                    <td align="left" width="250px" class="name">
                    <asp:Label ID="lblname" runat="server" Text='<%# Eval("Name")%>'></asp:Label>
                      
                    </td>
                    <td align="left" width="250px" class="name">
                    <asp:Label ID="lblemail" runat="server" Text='<%# Eval("Email")%>'></asp:Label>
                      
                    </td>
                    <td align="left" width="250px" class="name">
                    <asp:Label ID="lblid" runat="server" Text='<%# Eval("ID")%>' Visible="false"></asp:Label>
                      
                    </td>
                    <td>
                  <asp:LinkButton ID="lnkEdit" runat="server" CommandArgument='<%#Eval("ID") %>' CommandName="edit">Edit</asp:LinkButton>
                  </td>
                   <td>
                  <asp:LinkButton ID="Lnkdelete" runat="server" CommandArgument='<%#Eval("ID") %>' CommandName="Delete" OnClientClick="return confirm('Are u sure u wnat to delete this record?');">Delete</asp:LinkButton>
                  </td>
                 
                </tr>
               
               
            </table>
        </ItemTemplate>
    </asp:Repeater>
    </div>

code Behind (.CS File code)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace repetar1
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        static int CuID;
        protected void Page_Load(object sender, EventArgs e)
        {
            fillrepetar();
        }

        private void fillrepetar()
        {
            SqlConnection con = new SqlConnection("Data Source=ARVINDKATARIA\\SQLEXPRESS;Initial Catalog=practice;Integrated Security=True");
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from namedetail", con);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            cmd.ExecuteReader();
            Repeater1.DataSource = dt;
            Repeater1.DataBind();
            con.Close();
        }

        protected void btnsubmit_Click(object sender, EventArgs e)
        {
            if (btnsubmit.Text == "Submit")
            {
                SqlConnection con = new SqlConnection("Data Source=ARVINDKATARIA\\SQLEXPRESS;Initial Catalog=practice;Integrated Security=True");
                con.Open();
                SqlCommand cmd = new SqlCommand("INSERT INTO  namedetail(Name,Email)VALUES('" + txtname.Text + "','" + txtemail.Text + "')", con);
                cmd.ExecuteNonQuery();
                con.Close();
            }
            else
            {
                SqlConnection con = new SqlConnection("Data Source=ARVINDKATARIA\\SQLEXPRESS;Initial Catalog=practice;Integrated Security=True");
                con.Open();
                SqlCommand cmd = new SqlCommand("update namedetail set Name='" + txtname.Text + "' , Email='" + txtemail.Text + "' where id = '" + CuID + "' ", con);
                cmd.ExecuteNonQuery();
                con.Close();
                fillrepetar();
            }
        }

        protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)
        {
            if (e.CommandName == "edit")
            {
                Label l3 = (Label)e.Item.FindControl("lblid");
                CuID = Convert.ToInt32(l3.Text);
                Label l1 = (Label)e.Item.FindControl("lblname");
                txtname.Text = l1.Text;
                Label l2 = (Label)e.Item.FindControl("lblemail");
                txtemail.Text = l2.Text;
                btnsubmit.Text = "Update";
            }
          
            if (e.CommandName == "Delete")
            {
                Label l3 = (Label)e.Item.FindControl("lblid");
                CuID = Convert.ToInt32(l3.Text);
                SqlConnection con = new SqlConnection("Data Source=ARVINDKATARIA\\SQLEXPRESS;Initial Catalog=practice;Integrated Security=True");
                con.Open();
                SqlCommand cmd = new SqlCommand("delete from namedetail where ID = '" + CuID + "' ", con);
                cmd.ExecuteNonQuery();
                fillrepetar();
            }
          
        }
    }
}

Show Edit like...

 

Show Delete Like....

 

Enjoyyyyyyyy......

Insert , Delete , View In Console Application........

Insert , Delete , View In Console Application........

class Program
    {

        public  static void insert()
        {
            SqlConnection con = new SqlConnection("Data Source=ARVIND-KATARIA\\SQLEXPRESS;Initial Catalog=practice;Integrated Security=True");
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            Console.WriteLine("Enter student id");
            int id = Convert.ToInt16(Console.ReadLine());
            Console.WriteLine("Enter student Name");
            string name = Convert.ToString(Console.ReadLine());

            com.CommandText = "insert into stu1(ID,Name)values(@param1,@param2)";
            com.Parameters.AddWithValue("@param1", id);
            com.Parameters.AddWithValue("@param2", name);

            con.Open();
            SqlDataReader datareader = com.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(datareader);
            Console.WriteLine("\n Record insert ");

        }
        public static void display()
        {
            SqlConnection con = new SqlConnection("Data Source=ARVIND-KATARIA\\SQLEXPRESS;Initial Catalog=practice;Integrated Security=True");
            con.Open();
            DataSet ds = new DataSet();
            try
            {
                string sql = "select * from stu1";
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                   
                    printrow(Convert.ToString(dr["ID"]), Convert.ToString(dr["Name"]));

                }

                con.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

            }

           
        }
        static void printline()
        {
            Console.WriteLine(new string('-', 80));
        }
        static void printrow(string ID, string Name)
        {
            Console.WriteLine(string.Format("|{0}|{1}|", AlignCenter(ID, 17), AlignCenter(Name, 17)));
        }
        static string AlignCenter(string text, int width)
        {

            if (string.IsNullOrEmpty(text))
            {
                return new string(' ', width);

            }

            else
            {

               
                return text.PadRight(width - (width - text.Length) / 2).PadLeft(width);

            }

        }
       
         
        public static void delete()
        {
            SqlConnection con = new SqlConnection("Data Source=ARVIND-KATARIA\\SQLEXPRESS;Initial Catalog=practice;Integrated Security=True");
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            Console.WriteLine("Enter student  id");
            string id1 = Convert.ToString(Console.ReadLine());

            Console.WriteLine("Enter student Name");
            string name1 = Convert.ToString(Console.ReadLine());

            com.CommandText = string.Format("delete from stu1 where ID={0}", id1);
            com.Parameters.AddWithValue("@param1", "id");

            con.Open();
            SqlDataReader datareader = com.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(datareader);

            com.ExecuteNonQuery();
            Console.WriteLine("Data Deleated");

         
            com.CommandText = ("select * from stu1");

            Console.WriteLine("Detail Of the New Data Of student ");
            foreach (DataColumn colum in dt.Columns)
            {
                Console.WriteLine(string.Format("{0}", colum.ColumnName));
            }
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine(string.Format("{0}\t\t{1}", row["ID"], row["Name"]));

            }
            dt.Load(datareader);


                          
        }
        public static void update()
        {
            string connString = "Data Source=ARVIND-KATARIA\\SQLEXPRESS;Initial Catalog=practice;Integrated Security=True";
         string qry = @"select * from stu1 ";
         string upd = @"update stu1 set Name = @Name where ID = @ID";

         SqlConnection conn = new SqlConnection(connString);

         try {
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand(qry, conn);

            DataSet ds = new DataSet();  
            da.Fill(ds, "stu1");

            DataTable dt = ds.Tables["stu1"];

            dt.Rows[0]["Name"] = "W";

            foreach (DataRow row in dt.Rows)
            {
               Console.WriteLine(
                  "{0} ",
                  row["Name"].ToString().PadRight(15));
                 
            }

            // Update employees
            SqlCommand cmd = new SqlCommand(upd, conn);
            cmd.Parameters.Add("@Name",SqlDbType.NVarChar,50, "Name");
            SqlParameter parm = cmd.Parameters.Add("@ID",SqlDbType.Int,4,"ID");
            parm.SourceVersion = DataRowVersion.Original;
            da.UpdateCommand = cmd;
            da.Update(ds, "stu1");
         } catch(Exception e) {
            Console.WriteLine("Error: " + e);
         } finally {
            conn.Close();
         }
      } 



       

        static void Main(string[] args)
        {
            int ch;
            do
            {
                Console.WriteLine("\n ---------- MENU --------------");
                Console.WriteLine("\n 1. INSERT ");
                Console.WriteLine("\n 2. DISPLAY");
                Console.WriteLine("\n 3. DELETE ");
                Console.WriteLine("\n 4. UPDATE ");
                Console.WriteLine("\n 5. EXIT");
                Console.WriteLine("\n ENTER YOUR CHOICE");
                string s = Convert.ToString(Console.ReadLine());
                ch = int.Parse(s);

                if (ch == 1)
                {
                    insert(); 
                }
                else if (ch == 2)
                {
                    display();
                    printrow("ID","Name");
                    printline();
                    Console.ReadKey();
                }
                else if (ch == 3)
                {
                    delete();
                }

                else if (ch == 4)
                {
                    update();
                }
                else if (ch == 5)
                {
                    Console.WriteLine("\n Good Byeeeeeee");
                }
            } while (ch != 4);
        }
 }
BY:- Kataria Arvind...!!!!!!

C# get, set properties

Example.....


 class Arvind
    {

        private int rollno;
        private string name;

        public int setrollno()
        {
            return rollno;
        }
        public void getrollno(int values)
        {
            rollno = values;
        }
        public string getname()
        {
            return name;
        }
       public void setname(string abc)
       {
           name = abc;
       }
      
      
          static void Main(string[] args)
        {
            Arvind example = new Arvind();
            example.rollno = 5; // set { }
            example.name = "arvind";
            Console.WriteLine(example.rollno); // get { }
            Console.WriteLine(example.name);
            Console.ReadLine();
        }
    }

Enumeration Example......

Example......

 class Program
    {
        enum gender
        {
            male,
            female
           
        }
        static void Main(string[] args)
        {
           Console.WriteLine("select the 0 for male and 1 for female");
           gender name = (gender)Int32.Parse(Console.ReadLine());
           if (name == gender.male)
           {
               Console.WriteLine("select male");
               Console.ReadLine();
           }
           else if(name == gender.female)
           {
               Console.WriteLine("select female");
               Console.ReadLine();
           }
         
         
        }
    }