SQL Command type

Vijay

13y1.8kLearn iOS Programming

I create an stored procedure like dis....

CREATE PROCEDURE sp_Personal

@ID int,@Name varchar(50)@Gender varchar(6),@Address varchar(75),

@Phone bigint,@Status varchar(15)

BEGIN

IF @Status='Create'

BEGIN

INSERT INTO Student1(Name,Gender,Address,Phone) VALUES

(@Name,@Gender,@Address,@Phone)

END

IF @Status='Update'

BEGIN

UPDATE Student1 SET Name=@NameGender=@Gender,Address=@Address,Phone=@Phone

WHERE ID=@ID

END

END

Now my issue is want to connect to frontend usind ADO.net

public Create(Personal pers)

{

SqlConnection con = new SqlConnection("Data Source=SamplePC;Initial Catalog=Sample;Integrated Security=True");

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

com.CommandType = CommandType.StoredProcedure;

}

How to call a "Create" query only using Status parameter in frontend. am using MVC4 asp.net

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vijay13y ago
Thanks experts..... Now working Select, SelectRow fine.

But not working Create, Update, Delete using Storedprocedure ... its working with seperate queries oly..

using above StoredProcedure --- StudentApn

.cs file is.....

public string Create(Student st)
{
com = new SqlCommand("StudentApn", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@Status", SqlDbType.VarChar).Value = "Create";
com.Parameters.AddWithValue("@Name", st.Name);
com.Parameters.AddWithValue("@Dept", st.Dept);
com.Parameters.AddWithValue("@Section", st.Section);
com.Parameters.AddWithValue("@Gender", st.Gender);
com.Parameters.AddWithValue("@Address", st.Address);
com.Parameters.AddWithValue("@Phone", st.Phone);
com.Parameters.AddWithValue("@IsHostel", st.IsHostel);

con.Open();
int result = com.ExecuteNonQuery();
con.Close();
return "";
}

public string Update(Student st)
{
com = new SqlCommand("StudentApn", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@Status", SqlDbType.VarChar).Value = "Update";
com.Parameters.AddWithValue("@SID", st.SID);
com.Parameters.AddWithValue("@Name", st.Name);
com.Parameters.AddWithValue("@Dept", st.Dept);
com.Parameters.AddWithValue("@Sectoin", st.Section);
com.Parameters.AddWithValue("@Gender", st.Gender);
com.Parameters.AddWithValue("@Address", st.Address);
com.Parameters.AddWithValue("@Phone", st.Phone);
com.Parameters.AddWithValue("@IsHostel", st.IsHostel);

con.Open();
com.ExecuteNonQuery();
con.Close();
return "";
}

public string Delete(Student st)
{
com = new SqlCommand("StudentApn", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@Status", SqlDbType.VarChar).Value = "Delete";
com.Parameters.AddWithValue("@SID", st.SID);
con.Open();
com.ExecuteNonQuery();
con.Close();
return "";
}

0
Iftikar Hussain13y ago
Modify the sp like this

CREATE PROCEDURE StudentApn
(
@SID int,
@Name varchar(50)='',
@Dept varchar(50)='',
@Section int='',
@Gender varchar(6)='',
@Address varchar(75)='',
@Phone bigint='',
@IsHostel bit='',
@Status varchar(15)
)
AS
BEGIN
IF @Status='Create'
BEGIN
INSERT INTO Student1(Name,Dept,Section,Gender,Address,Phone,IsHostel) VALUES
(@Name,@Dept,@Section,@Gender,@Address,@Phone,@IsHostel)
END
IF @Status='Update'
BEGIN
UPDATE Student1 SET Name=@Name,Dept=@Dept,Section=@Section,Gender=@Gender,Address=@Address,Phone=@Phone,IsHostel=@IsHostel
WHERE SID=@SID
END
IF @Status='Delete'
BEGIN
DELETE Student1 WHERE SID=@SID
END
IF @Status='Select'
BEGIN
SELECT * FROM Student1
END
IF @Status='SelectRow'
BEGIN
SELECT * FROM Student1 WHERE SID=@SID
END
END

Regards,
Iftikar
0
Vijay13y ago
I set SID is Auto-Increment in table
0
Vijay13y ago
Stored Procedure is

CREATE PROCEDURE StudentApn
@SID int, @Name varchar(50),@Dept varchar(50), @Section int, @Gender varchar(6),@Address varchar(75),
@Phone bigint, @IsHostel bit, @Status varchar(15)
AS
BEGIN
IF @Status='Create'
BEGIN
INSERT INTO Student1(Name,Dept,Section,Gender,Address,Phone,IsHostel) VALUES
(@Name,@Dept,@Section,@Gender,@Address,@Phone,@IsHostel)
END
IF @Status='Update'
BEGIN
UPDATE Student1 SET Name=@Name,Dept=@Dept,Section=@Section,Gender=@Gender,Address=@Address,Phone=@Phone,IsHostel=@IsHostel
WHERE SID=@SID
END
IF @Status='Delete'
BEGIN
DELETE Student1 WHERE SID=@SID
END
IF @Status='Select'
BEGIN
SELECT * FROM Student1
END
IF @Status='SelectRow'
BEGIN
SELECT * FROM Student1 WHERE SID=@SID
END
END

.cs file is

public IEnumerable StudentRecords(Student st)
{
com = new SqlCommand("StudentApn", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@Status", SqlDbType.VarChar).Value = "Select";
com.Parameters.AddWithValue("@SID", st.SID);
com.Parameters.AddWithValue("@Name", st.Name);
com.Parameters.AddWithValue("@Dept", st.Dept);
com.Parameters.AddWithValue("@Section", st.Section);
com.Parameters.AddWithValue("@Address", st.Address);
com.Parameters.AddWithValue("@Phone", st.Phone);
com.Parameters.AddWithValue("@IsHostel", st.IsHostel);

con.Open();
SqlDataAdapter sda = new SqlDataAdapter(com);
DataSet ds = new DataSet();
sda.Fill(ds);

foreach (DataRow dr in ds.Tables[0].Rows)
{
yield return new Student
{
SID = Convert.ToInt32(dr["SID"]),
Name = Convert.ToString(dr["Name"]),
Dept = Convert.ToString(dr["Dept"]),
Section = Convert.ToInt32(dr["Section"]),
Address = Convert.ToString(dr["Address"]),
Phone = Convert.ToInt64(dr["Phone"]),
IsHostel = Convert.ToBoolean(dr["IsHostel"])
};
}
}

public string StudentRecord(Student st)
{
com = new SqlCommand("StudentApn", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@Status", SqlDbType.VarChar).Value = "SelectRow";
com.Parameters.AddWithValue("@SID", st.SID);

SqlDataAdapter sda = new SqlDataAdapter(com);
DataSet ds = new DataSet();
sda.Fill(ds);

if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
Student student = new Student()
{
SID = Convert.ToInt32(dr["SID"]),
Name = Convert.ToString(dr["Name"]),
Dept = Convert.ToString(dr["Dept"]),
Section = Convert.ToInt32(dr["Section"]),
Address = Convert.ToString(dr["Address"]),
Phone = Convert.ToInt64(dr["Phone"]),
IsHostel = Convert.ToBoolean(dr["IsHostel"])
};
}
return "";
}

in this i getting error like... Procedure or function 'StudentApn' expects parameter '@Name', which was not supplied. please check my code wat i did wrongly

0
Rosi Sreenivasa Reddy13y ago
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE proc_personal
(
@Id int=0,
@Name varchar(50)=null,
@Gender varchar(6)=null,
@PAddress varchar(75)=null,
@Phone numeric=null,
@status int=o,
@result int=null,
@errmsg varchar(500)=null
)
AS
BEGIN
if(@status=1)
begin
if not exists(select Id from personal where Name=@Name)
begin
select @Id=ISNULL(max(Id),0)+1 from personal
insert into personal(Id,Name,Gender,PAddress,Phone)Values(@Id,@Name,@Gender,@PAddress,@Phone)
set @result=1
end
end
else
begin
set @result=0
set @errmsg='Name Already Exists'
end
if(@status=2)
begin
if not exists(select Id from personal where Id<>@Id and Name=@Name)
begin
update personal set Name=@Name,Gender=@Gender,PAddress=@PAddress,Phone=@Phone where Id=@Id
set @result=1
end
end
else
begin
set @result=0
set @errmsg='Name Already Exists'
end

END
GO

aspx.cs file::

SqlConnection con = new SqlConnection("data source=WEBDEV1-PC; Initial Catalog=srinu; user Id=sa; password=pftec;");
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "Proc_Personal";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
if(string.IsnullorEmpty(hfId.Value)
{
cmd.parametera.Add("@status",sqlDbType.Int).Value=1;
}
else
{
cmd.Parameters.Add("@ststus",sqldbtype.Int).Value=2;
cmd.Parameters.Add("@Id",dqldbtype.Int).Value=HfId.Value;
}
}
0
Rosi Sreenivasa Reddy13y ago
if you i will send the stored procedure and coding for this kkkkkkkkkkkkkk
0
Iftikar Hussain13y ago
sorry, here it is

http://www.asp.net/mvc/tutorials/mvc-4/getting-started-with-aspnet-mvc4/intro-to-aspnet-mvc-4

Regards,
Iftikar
0
Vijay13y ago
which link Iftikar Hussain..!
0
Vijay13y ago
Sorry sir.. I understood what u said ... and i tried like code before itself.. I got it...

But now i wanna use only create command oly using parameter ...like

SqlCommand com = new SqlCommand("sp_Personal", con);
com.CommandType = CommandType.StoredProcedure;
com.CommandType=CommandType("@Status='Create');

com.Parameter.AddWithValue("@Name",Name);
com.Parameter.AddWithValue("@Gender ",GENDER);
com.Parameter.AddWithValue("@Address ",ADDRESS);
com.Parameter.AddWithValue("@Phone ",Phone);

In that time i want access oly create query
I getting error... plz understand my question
0
Iftikar Hussain13y ago
Hi,
Please refer the below link for all different type of functionality including Create

Regards,
Iftikar
0
13y ago
You need to pass parameter from your code.

SqlConnection con = new SqlConnection("Data Source=SamplePC;Initial Catalog=Sample;Integrated Security=True");
SqlCommand com = new SqlCommand("sp_Personal", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameter.AddWithValue("@ID",IDVALUE);
com.Parameter.AddWithValue("@Gender ",GENDER);
com.Parameter.AddWithValue("@Address ",ADDRESS);
com.Parameter.AddWithValue("@Phone ",Phone);
com.Parameter.AddWithValue("@Status",STATUS);
int result=com.ExecuteNonQuery();
0