SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Data Source=HOME\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_GetCurrency1";
            cmd.Connection = conn;
            SqlDataAdapter adp = new SqlDataAdapter();
            adp.SelectCommand = cmd;
            DataTable DT = new DataTable();
            try
            {
                conn.Open();
                adp.Fill(DT);
                
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            SqlConnection conn = new SqlConnection(this.ConnStr);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_LoginUser";
            cmd.Connection = conn;
            SqlParameter paramUser = new SqlParameter();
            paramUser.ParameterName = "@name";
            paramUser.SqlDbType = SqlDbType.NVarChar;
            paramUser.Value = UserName;
            cmd.Parameters.Add(paramUser);
            SqlParameter paramPass = new SqlParameter();
            paramPass.ParameterName = "@password";
            paramPass.SqlDbType = SqlDbType.NVarChar;
            paramPass.Value = Password;
            cmd.Parameters.Add(paramPass);
            try
            {
                conn.Open();
                Users = (int)cmd.ExecuteScalar();
                
                Log.WriteMessage("User connected name= " + paramUser);
            }
            catch (Exception ex)
            {
                Users = -1;
                Log.WriteError(ex.Message);
            }
            finally
            {
                conn.Close();
            }

use AdventureWorks
go
create proc sp_proc
as
begin
select * from production.product
end
go
exec sp_proc