How to use ExecuteScalar in Ado.Net with C#

How to use ExecuteScalar  in Ado.Net  with  C#

create database test

use test

create table McaStudent(RollNo int identity(1,1),StudentId as 'Mca' + cast(RollNo asvarchar(10)),Name nvarchar(50),Gender nvarchar(50),Course nvarchar(50),Addressnvarchar(max))

insert into McaStudent values('Somesh', 'Male','B. Tech.','Kanpur')

*************************

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

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

{

    protected void Page_Load(object sender, EventArgs e)

    {

         con = new SqlConnection("Data Source=kush;Initial Catalog=test;Integrated Security=True");

    }      

    SqlConnection con;

    protected void BtnSave_Click(object sender, EventArgs e)

    {

        con.Open();

       // SqlCommand cmd = new SqlCommand("insert into McaStudent (Name,Gender,Course,Address) output inserted.StudentId values ('" + txtName.Text + "','" + RbGender.SelectedValue + "','" + ddlCourse.SelectedItem.Text + "','" + txtAddress.Text + "')", con);

        SqlCommand cmd = new SqlCommand("insert into McaStudent  output inserted.StudentId values ('" + txtName.Text + "','" + RbGender.SelectedValue + "','" + ddlCourse.SelectedItem.Text + "','" + txtAddress.Text + "')", con);

       LStudentId.Text= ""+cmd.ExecuteScalar();

       con.Close();

       LMessage.Text = "Add Success fully";

 }

    protected void BtnNoRow_Click(object sender, EventArgs e)

    {

       int count=0;

      String sqlQuery = "Select COUNT(*) from McaStudent";

        SqlCommand cmd = new SqlCommand(sqlQuery, con);

        try

        {

            con.Open();

            //Since count  is intger,System.Object, a typecast is must

            count = (Int32)cmd.ExecuteScalar();

            LMessage.Text = count.ToString();

        }

        catch (Exception ex)

        {

            LMessage.Text = ex.Message;

        }

        finally

        {

            con.Close();

        }

     

    }

}

Read More

How to execute stored procedure in another store procedure

How to execute stored procedure in another store procedure

--Create Database

create database dbtest

--Use Database

use dbtest

--Create table StudentDetails RollNo with primary key

create table StudentDetails(RollNo int identity(1,1) primary key,Name nvarchar(50),Age int)

--Create table FeeDetails RollNo relation with StudentDetails RollNo(foregin key)

create table FeeDetails(RollNo int foreign key references StudentDetails(RollNo),Fee int)

--Create proc ins_fee on FeeDetails Table

create proc  ins_fee @RollNo int,@Fee int

as

begin

insert into FeeDetails values(@RollNo,@Fee)

end

--exec ins_fee 1,500

--Create proc ins_student on StudentDetails Table using out keyword

create proc ins_student @RollNo int out,@Name nvarchar(50),@Age int,@Fee int

as

begin

insert into StudentDetails values(@Name,@Age)

set @RollNo=@@IDENTITY

exec ins_fee @RollNo ,@Fee

end

--declare @RollNo int

--exec ins_student @RollNo out,'Kush Tiwari',25,300

--select @RollNo

 select * from StudentDetails

  select * from FeeDetails

################################################################

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

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

{     

    protected void Page_Load(object sender, EventArgs e)

    {

    }

    protected void BtnSave_Click(object sender, EventArgs e)

    {

SqlConnection con = new SqlConnection("Data Source=kush;Initial Catalog=dbtest;Integrated Security=True");

        con.Open();

        SqlCommand com = new SqlCommand("ins_student", con);

        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.AddWithValue("@Name", txtname.Text);

        com.Parameters.AddWithValue("@Age", Convert.ToInt32(txtage.Text));

        com.Parameters.AddWithValue("@Fee", Convert.ToInt32(txtfee.Text));

        com.Parameters.Add("@RollNo", SqlDbType.Int);

        com.Parameters["@RollNo"].Direction = ParameterDirection.Output;

        com.ExecuteNonQuery();

        con.Close();

        LMessage.Text = "Your rollno is:" +"    "+ com.Parameters["@RollNo"].Value.ToString();

    }

}

Read More