Calling and Using Stored Procedures in Asp.Net Environment

 using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

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

{

protected void Page_Load(object sender, EventArgs e)

{

            txtusername.Focus();

}

SqlConnection con = new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=samplDB;Data Source=IRIDIUM102");

SqlCommand cmd = new SqlCommand();

protected void btnSubmit_Click(object sender, EventArgs e)

{

con.Open();

cmd.CommandText = "sp_insuserdet";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = con;

cmd.Parameters.Add("@uname", SqlDbType.NVarChar, 50);

cmd.Parameters["@uname"].Value = txtusername.Text;

cmd.Parameters.Add("@pwd", SqlDbType.NVarChar, 50);

cmd.Parameters["@pwd"].Value = txtpassword.Text;

int t=cmd.ExecuteNonQuery();

try

{

if (t > 0)

Response.Write("Record Inserted");

else

Response.Write("Insertion failed");

}

catch (Exception ex)

{

Response.Write(ex.Message);

}

con.Close();

clear();

}

private void clear()

{

cmd.Parameters.Clear();

txtusername.Text = "";

txtpassword.Text = "";

txtusername.Focus();

}

protected void btnUpdate_Click(object sender, EventArgs e)

{

con.Open();

cmd.CommandText = "sp_upduserdet";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = con;

cmd.Parameters.Add("@uname", SqlDbType.NVarChar, 50);

cmd.Parameters["@uname"].Value = txtusername.Text;

cmd.Parameters.Add("@pwd", SqlDbType.NVarChar, 50);

cmd.Parameters["@pwd"].Value = txtpassword.Text;

int t = cmd.ExecuteNonQuery();

try

{

if (t > 0)

Response.Write("Record updated");

else

Response.Write("updation failed");

}

catch (Exception ex)

{

Response.Write(ex.Message);

}

con.Close();

clear();

}

protected void btnDelete_Click(object sender, EventArgs e)

{

con.Open();

cmd.CommandText = "sp_deluserdet";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = con;

cmd.Parameters.Add("@uname", SqlDbType.NVarChar, 50);

cmd.Parameters["@uname"].Value = txtusername.Text;

cmd.Parameters.Add("@pwd", SqlDbType.NVarChar, 50);

cmd.Parameters["@pwd"].Value = txtpassword.Text;

int t = cmd.ExecuteNonQuery();

try

{

if (t > 0)

Response.Write("record deleted");

else

Response.Write("deletion failed");

}

catch (Exception ex)

{

Response.Write(ex.Message);

}

con.Close();

clear();

}

protected void btnFind_Click(object sender, EventArgs e)

{

con.Open();

cmd.CommandText = "sp_finduserdet";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = con;

cmd.Parameters.Add("@uname", SqlDbType.NVarChar, 50);

cmd.Parameters["@uname"].Value = txtusername.Text;

cmd.Parameters.Add("@pwd", SqlDbType.NVarChar, 50);

cmd.Parameters["@pwd"].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

try{

txtpassword.Text = cmd.Parameters["@pwd"].Value.ToString();

}

catch(Exception ex)

{

Response.Write(ex.Message);

}

con.Close();

}

}