You must Sign In to post a response.
  • Category: [Competition Entries]

    How to use the stored prcedure

    Thanq Madam
    But i will try to get one program in that program how to use the stored procedure that program is i had created one data base table of one username and password and also created a another database table in that table the total information of employee now i had written a 3 default.aspx pages mean one login page and another is register for login page and another is display employee detailes now how i am use the stored procedure in this program


    Regrads of
    Sathish
  • #709445
    Hi,
    Build the sql stored procedure with input parameters such as Empid in the sqlserver database

    Your storedprocedure code would be as follows

    CREATE PROCEDURE sp_Emp(@EmpID Int)
    AS
    SELECT *
    FROM Emptab
    WHERE EmpID= @EmpID
    GO



    In the Employee page add the following code in the page_Load() event

    Try
    {
    string strCon = "server=localhost;uid=sa;pwd=sa; database=EmpDB";
    con = new SqlConnection(strCon);
    con.Open();


    SqlCommand cmd =new SqlCommand ("sp_Emp", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@EmpID",empID);
    read = cmd.ExecuteReader();

    while(read.Read())
    {
    txtEmpID.Text=read["EmpID"].ToString();
    txtEmpName.Text=read["EmpName"].ToString();
    }

    }
    catch(Exception ex)
    {
    // Print error message
    MessageBox.Show(ex.Message);
    }



    Have a good Day

  • #709449
    Thanq Mam
    Mam you understand telugu just for in formation i will ask in telugu

  • #709464
    Hai Mam
    padma mam you known telugu i will ask you in telugu mama plz

  • #709534
    Stored Procedure

    A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a sproc or SP) are actually stored in the database data dictionary.

    Example




    create procedure Empp @eusername nvarchar(20),@epassword nvarchar(20),@EEmpid nvarchar(20)
    as
    begin
    insert into Emp (UserName,PassWord,EmpID) values(@eusername,@epassword,@EEmpid)
    end


    You can insert the values like this: empp 'Rengan','Nathan','1050'



    Thanks & Regards
    G.Renganathan
    Nothing is mine ,Everything is yours!!!

  • #709723
    string connect = System.Configuration.ConfigurationManager.AppSettings["conn"];

    SqlConnection scn = new SqlConnection(connect);

    SqlCommand spcmd = new SqlCommand("CustOrdersDetail", scn);

    spcmd.CommandType = System.Data.CommandType.StoredProcedure;

    spcmd.CommandText = "CustOrdersDetail";

  • #709771
    Hi,

    You can use the stored procedure for inserting username and password. Also you can write another stored procedure for getting information of employee.


    CREATE PROCEDURE [dbo].[spEmployeeInsert]
    @username VARCHAR(100),
    @password VARCHAR(100)
    AS
    BEGIN
    Insert into employee(username,password)values(@username,@password)
    END
    GO



    CREATE PROCEDURE [dbo].[spEmployeeSelect]
    @username VARCHAR(100),
    @password VARCHAR(100)
    AS
    BEGIN
    Select * from employee where username=@usrename and password=@password
    END
    GO



    Code behind method to insert employee details using stored procedure

    public int InsertEmployee(EmpClass emp)
    {
    SqlConnection con = new SqlConnection(Connectonstring);
    con.Open();
    SqlCommand com = new SqlCommand("spEmployeeInsert", con);

    com.Parameters.AddWithValue("@username", emp.username);
    com.Parameters.AddWithValue("@password", emp.password);
    com.Parameters.AddWithValue("@salary", emp.salary);
    com.CommandType = System.Data.CommandType.StoredProcedure;
    int result = com.ExecuteNonQuery();
    com.Dispose();
    con.Dispose();
    return result;
    }




    Code behind method to fetch employee details using stored procedure

    public EmpClass retrieveFromEmp(string usernamevalue, string passwordvalue)
    {
    SqlConnection con = new SqlConnection(MyClass.myconnectionstring);
    try
    {
    con.Open();
    SqlCommand com = new SqlCommand("spEmployeeSelect", con);
    com.Parameters.AddWithValue("@username",usernamevalue);
    com.Parameters.AddWithValue("@password",passwordvalue);
    com.CommandType = CommandType.StoredProcedure;
    SqlDataReader dr = com.ExecuteReader();
    if (dr.Read())
    {
    EmpClass emp = new EmpClass();
    emp.username = dr["username"].ToString();
    emp.password = dr["password"].ToString();
    emp.salary = dr["salary"].ToString();
    dr.Close();
    com.Dispose();
    con.Dispose();
    return emp ;
    }
    else
    {
    dr.Close();
    com.Dispose();
    con.Dispose();
    return null;
    }
    }
    finally
    {
    con.Close();
    }
    }



    Hope it will be useful.

    Regards,
    T.N. THEAAVARAAJ
    Senior Software Engineer,
    Microsoft Certified Technology Specialist.
    Email:devabe2005@gmail.com


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.