I have a class1.cs file in appcode and and it has stored procedure in it .
cmd.CommandType = CommandType.StoredProcedure;
How do i write stored procedure for all this??
public void updatesubcategory(int cat,string subcategory,string ImageFile1,string description)
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_updatesubcategory", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.Parameters.AddWithValue("@category", cat);
cmd.Parameters.AddWithValue("@subcategory", subcategory);
cmd.Parameters.AddWithValue("@images", ImageFile1);
cmd.Parameters.AddWithValue("@description", description);
cmd.ExecuteNonQuery();
con.Close();
}
public DataSet getsubcategorybyid(int id)
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_getsubcategorybyid", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", id);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public DataSet checkforsubcategory(int id)
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_checkforsubcategory", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", id);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public DataSet getauto()
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_getcategory1", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public void delauto(int id)
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_delauto", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.Parameters.AddWithValue("@id", id);
cmd.ExecuteNonQuery();
con.Close();
}
public void insauto(string category, string subcategory , string images,string description)
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_inssubcategory", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.Parameters.AddWithValue("@category", category);
cmd.Parameters.AddWithValue("@subcategory", subcategory);
cmd.Parameters.AddWithValue("@images", images);
cmd.Parameters.AddWithValue("@description", description);
cmd.ExecuteNonQuery();
con.Close();
}
public DataSet getauto1()
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_getsubcategory", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public DataSet getcategory()
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_getcategoryfordropdown", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public void delauto1(int id)
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_delsubcategory", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.Parameters.AddWithValue("@id", id);
cmd.ExecuteNonQuery();
con.Close();
}
public void insauto1(string category)
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_addcategory", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.Parameters.AddWithValue("@category", category);
cmd.ExecuteNonQuery();
con.Close();
}
public DataSet getclient()
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_getclient", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public void delclient(int id)
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_delclient", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.Parameters.AddWithValue("@id", id);
cmd.ExecuteNonQuery();
con.Close();
}
public void insclient(string category)
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_insclient", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.Parameters.AddWithValue("@description", category);
cmd.ExecuteNonQuery();
con.Close();
}
public DataSet getclientforuser()
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_getclientforuser", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public DataSet selectlist(int id1)
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_getsubcategoryforuser", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id",id1);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public DataSet getselectedproduct(int cid, int sid)
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_getselectedproduct", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@cid", cid);
cmd.Parameters.AddWithValue("@sid",sid);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public DataSet getselectedproduct1(int cid)
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_getselectedproduct1", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@cid", cid);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public DataSet getsubproductbyid(int sid)
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_getsubproductbyid", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@sid", sid);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public DataSet menu()
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_fillmenu", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
}
Sunny SharmaPosted Jun 19, 2013, 1:12 AM
Stored Procedures lie in DB Servers so you just don't need to write them in your cs file. I'll take the second code block from your question for explanation:
--------------------------------------------------
public DataSet getsubcategorybyid(int id)
{
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("sp_getsubcategorybyid", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", id);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
---------------------------------------------------
In the above highlighted you just specify explicitly that the query that you're trying to execute will seek for a Stored Procedure on DB server instead of simply executing it as a query. The default CommandType is Text.
If you want to learn how to create Stored Procedures in SQL Server, there are plenty of resources available. I recommend you read these articles:
http://www.c-sharpcorner.com/UploadFile/4624e9/stored-procedures-in-C-Sharp/
http://www.c-sharpcorner.com/uploadfile/puranindia/stored-procedures/
http://www.mssqltips.com/sqlservertutorial/160/sql-server-stored-procedure/
http://goo.gl/iH7Ct - this will get you plenty of tutorials available.
Hope you understand the point now.
Mark it as answer if it helps.
Thanks.
Sunny SharmaPosted Jun 19, 2013, 3:11 AM
antony victorPosted Jun 19, 2013, 1:21 AM
Neeraj SinghPosted Jun 19, 2013, 1:18 AM