How To Write Store procedure inside Web Service in Asp.Net

Question

Hello All,

 

I'm creating a project in Visual Studio 10 (Framework 4.0).

I'hv written a store procedure for (insert query) inside Business Access Layer (BAL) class file called as clsAdmin.cs

I want to call this store procedure in WebService.asmx page....... 

How to call this store procedure  in WebService.asmx ???? 

 

My Code as Follows

 

1. clsAdmin.cs 

public class clsAdmin

{

public clsAdmin()

{

//

// TODO: Add constructor logic here

//

}

public int AddCountry(string name, string code, string description)

{

try

{

SqlParameter[] p = new SqlParameter[3];

p[0] = new SqlParameter("@CountryName", name);

p[1] = new SqlParameter("@CountryCode", code);

p[2] = new SqlParameter("@CountryDesc", description);

return SqlHelper.ExecuteNonQuery(clsConnection.GetConnectionString(), CommandType.StoredProcedure, "sp_AddCountry", p);

}

catch (ArgumentException ex)

{

throw new ArgumentException(ex.Message);

}

}

}

  

 2. frmAddCity.aspx ( I'hv called the above store procedure directly here. But now i want call this store procedure through webservice page  to frmAddCity.aspx)

 

protected void btnsubmit_Click(object sender, EventArgs e)

{

try

{

int val = obj.AddCity(Convert.ToString(txtCity.Text), Convert.ToString(txtdescription.Text), Convert.ToInt32(ddlState.SelectedValue), Convert.ToInt32(ddlCountries.SelectedValue));

if (val > 0)

{

ScriptManager.RegisterStartupScript(Page, Page.GetType(), "success", "$('#success').modal();", true);

}

else

{

Label1.Text = "City Already Exists";

ScriptManager.RegisterStartupScript(Page, Page.GetType(), "warning", "$('#warning').modal();", true);

}

}

catch (ArgumentException ex)

{

ScriptManager.RegisterStartupScript(Page, Page.GetType(), "danger", "$('#danger').modal();", true);

}

}

 

3. WebService.asmx

 

 

[WebMethod]

public int insertcity(string knownCategoryValues, string category)

{

string name, description;

int stateid, countryid;

int record;

StringDictionary kv = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);

if (!kv.ContainsKey("City") || !Int32.TryParse(kv["City"], out record))

{

throw new ArgumentException("Couldn't find Category.");

};

clsAdmin obj1 = new clsAdmin();

record = obj1.AddCity(name, description, stateid, countryid);

// How to call the store procedure  over here ?? 

}